Using the Expression Builder

When you create an expression, it must be formatted correctly so Sage HRMS can understand it. You can type the expression in manually (if you know exactly what the field, relationship, and value should be)—or you can use the Visual FoxPro 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 window so you can create the 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.

Tip: 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

To create an expression telling Sage HRMS which employees to select, click the Expression Builder button.
In the Expression Builder window, build your expression:
1. In the From table list, select the database table to use.
2. In the Fields list (which lists the fields from the database table you selected in step 2a), double-click the field to use.
  
  The database table alias and field appear in the Expression area near the top of the window.
3. In the Logical list, select the operator that you want to use in the expression.
  
  The operator is added to the Expression area.
4. Click to place your cursor at the end of the expression, and then type a space and the comparison value.
  Note: Different types of values must be entered in different formats:
  - Character values must be enclosed with quotation marks: 'value' or "value"
  - Date values must be entered as numbers and enclosed with {braces}: {date}
  - Logical values (such as T or F for True or False) must be enclosed in periods: .T.
  - Numeric values are entered directly: 235.12
Example: To limit the selection to active employees, select the Pe table (the alias for the Employee Personnel database file), p_active for the field, = in the Logical list, and type 'A'. Your expression would look like this:
Pe.p_active = 'A'
If you need to add another criterion to the expression:
1. Type a space at the end of the expression in the Expression area.
2. In the Logical list, select the operator, such as AND.
  
  The operator is added to the Expression area.
3. Repeat step 2 to add the second criteria.
Example: To also limit the selection to employees in the Sales department (organizational level 1), select the Pe table, p_level1 for the field, = in the Logical list, and type 'SALES'. Your expression would now look like this:
Pe.p_active = 'A' AND Pe.p_level1 = 'SALES'
When you finish entering the expression, click OK.

Tip: You can click Verify to make sure you used the correct structure for your expression. For example, there is an equal sign, it will confirm that there is a value after the equal sign. When you save your expression and return to the report or process to use the expression, Sage HRMS checks your expression again. If it is an illogical expression, a message displays and indicates what is wrong; return to the Expression Builder and fix the error.