Using the Expression Builder

The expression must be formatted correctly so Sage HRMS can understand it. You can always type the expression in manually if you know exactly what the field, relationship and value should be. Or you can use the Expression Builder to help you build the expression. As you work with Sage HRMS, whenever you see the Expression Builder button, click it to open the Expression Builder dialog box. Then create your expression.

Note: If you are using a SQL Server database, some functions that are available in the Expression Builder may not work because they are specific to Visual FoxPro and there is not an equivalent function for SQL Server. See the list of valid functions that are supported for both databases.

All date fields contain the time in addition to the date. If you use dates in calculations you should remove the time part of the date using the Foxpro TtoD() function. For example:

INT((Date() - TtoD(hrpersnl.p_birth))/365)
TtoD(hrpersnl.p_lasthire) + 90

You may also want to remove the time from the date when displaying or printing dates.

To Build an Expression

This section will take you through building an expression. Here is the example:

The sample company, Z Systems, Inc., is implementing a new policy. All active employees in the Sales/Marketing division will no longer have their performance review on their anniversaries. They will now all be reviewed on October 1.

You’ll use the Mass Update process to change the next performance review date for these employees. First, select the database file and field to update.

Select a menu group then Processes > Mass Update for the group that you want to work with, for example Time Off > Mass Update.
Select Employee Personnel from the Update File list.
Select Next Performance Review Due Date from the Update Field list.
You now need to tell Sage HRMS which employee records you want to update: click the Expression Builder button below the Records to Update field. The Expression Builder dialog box opens so you can build your expression.

Refer to the Data Dictionary, for database file aliases.

In the From table list, notice that Pe is selected. Pe is the alias for the Employee Personnel database file. The file is already selected because you chose it as your Update File.
The Fields list contains all the fields contained in the Employee Personnel database file. Double-click P_ACTIVE, the second field in the list.
The field, preceded by the "Pe" alias, appears in the Expression field.
In the Logical list, locate the equal sign (=) and click it.
Click your mouse button to place your cursor at the end of the expression and enter a space, single quote, A, single quote, space. Your expression should now look like this: Pe.p_active = 'A'

You have just built the first comparison to tell Sage HRMS to look for all employees with an active employee status.

In the Logical list, locate the word AND and click it. This starts your second comparison.
In the Fields list, locate the field P_LEVEL1 and double-click it.
In the Logical list, locate the equal sign (=) and click it.
Click your mouse button to place your cursor at the end of the expression and enter a space, single quote, SALES/MKTG, single quote. Your expression should now look like this: Pe.p_active = 'A' AND Pe.p_level1 = 'SALES/MKTG'
- You have finished building the expression. Sage HRMS will find all active employees who work in the Sales/Mktg division (in DEU (or DEC for Canadian sample data), P_LEVEL1 has already been defined as the division).
Click OK to save your expression and return to the Mass Update dialog box.

Tip: You can use the Verify button to make sure you’ve used the correct structure for your expression. For example, if you have an equal sign, it will confirm that you have a value after the equal sign. When you save your expression and return to the report or process for which you’re building the expression, Sage HRMS will check your expression again. If you have built an illogical expression, a message will indicate what is wrong. Return to the Expression Builder and fix your error.

Finish up your Mass Update process by typing October 1, 2016 in the New Value field. (Remember, this is the new date on which all employees will be reviewed.)
Click OK to complete the process and update the records.