Related topics

Using Secure Query

View Data and Run Queries

Set Record Filter

The Set Record Filter tab enables you to further define the records you want included in a query. In some cases, you might want to specify a particular value to further limit your results. For example, you might want to find only active employees.

Other times, you might want to create a query that enables you to specify different value each time. The record filter acts as placeholder for a value that is supplied when the query runs. For example, you might want to find employees hired during a certain date range. You could run the same query for each request, except that the original hire date range would be different each time.

The Set Record Filter tab lists previously saved conditions for the selected query in the order in which you added them. You can specify up to a maximum of 10 conditions. If the record filter contains conditions with set values, the conditions display with their assigned values. If the record filter contains conditions that can have different values at different times, the condition appears with {To be entered later}. Set Record Filter fields do not have to be placed in a query in order to be used as a condition in a record filter.

Select the Hide Duplicate Output Records check box if you do not want the query to display records that are duplicated.

Add a New Condition to a Record Filter

  1. From the Set Record Filter tab, select Hide Duplicate Output Records to prevent the query from returning duplicate records from the database.
  2. Click Set Record Filter.
  3. The first time the Set Record Filter is used in a query, the Set Record Filter dialog box opens. Proceed to step 4.
  4. Note: After you add your first condition to a record filter, the system opens the Conditions List page.

  5. Click Add. The Select a Connector dialog box opens.
  6. To require both this condition and the prior condition to be true, click And. Using AND enables you to specify that the values in a record filter must meet two (or more) conditions for the data to be included in the query. This option narrows the scope of the search and usually returns less data.
  7. To require either this condition or the prior condition to be true, click Or. Using OR enables you to specify several alternative values in a record filter. This option expands the scope of the search and can return more data.
  8. In the Set Record Filter dialog box, on the Pick a Field tab, select the field that contains the data you want to filter or specify.
  9. Click Next to move to the Pick an Operator tab or the Pick True or False tab. The choices on the Pick an Operator tab or the Pick True or False tab depend on the type of field you select from the Pick a Field tab. Select the type of operator that describes the operation or task you want. If you select a True or False condition, the Enter a Value tab is disabled. Proceed to step 9.
  10. Click Next to move to the Enter a Value tab if the system does not automatically move you to that tab.
  11. The choices on the Enter a Value tab depend on the type of field you select from the Pick a Field tab and the type of operator you select from the Pick an Operator tab. From the Enter a Value tab:
  12. Enter or select the appropriate value.
  13. Click Ask later to create a record filter that prompts you for the values when the query is run.
  14. Click Compare to Field to compare data from the field you selected on the Pick a Field tab with the content of another data field.
  15. Click OK. The Conditions List page opens.
  16. Click Cancel to discard your changes. A message asks if you want to exit without saving changes. Click Yes if you are certain you do not want to save the changes.
  17. When you are finished setting up the record filter, click Done. If the Enter a Value tab opens, click Cancel.

Edit a Condition

  1. From the Set Record Filter tab, click the Set Record Filter button. The Conditions List page opens and lists previously saved conditions.
  2. From the list, select the condition you want to edit.
  3. Click Edit. The Set Record Filter dialog box opens.
  4. From the Pick an Operator tab, select the type of operator that describes the operation or task you want to take place.
  5. Click Next to move to the Enter a Value tab, if the system does not automatically move you to that tab.
  6. The choices on the Enter a Value tab, depend upon the type of field you select from the Pick a Field tab and the type of operator you select from the Pick an Operator tab. From the Enter a Value tab:
  7. Enter or select the appropriate value.
  8. Click the Ask later button to enable you to create a record filter that prompts you for the values when the query is run.
  9. Click the Compare to Field button to compare data from the field you selected on the Pick a Field tab with the content of another data field.
  10. Click OK. The Conditions List page opens.
  11. Click Cancel to discard your changes. A message asks if you want to exit without saving changes. Click Yes if you are certain you do not want to save the changes.
  12. When you are finished setting up the record filter, click Done. If the Enter a Value tab opens, click Cancel.

Switch a Connector

If a Record Filter includes more than one condition, the conditions are linked with either an AND (to narrow the search) or an OR (to broaden it). If you want the values in a record filter to meet several conditions, specify an AND connector. If you want to specify several alternative values, specify an OR connector. Use the Switch button to change the connector that links two conditions.

  1. From the Set Record Filter tab, click the Set Record Filter button. The Conditions List page opens and lists previously saved conditions.
  2. From the list of conditions, select the one you want to switch and click once to highlight the condition.
  3. Click Switch.
  4. Click Cancel to discard your changes. A message asks if you want to exit without saving changes. Click Yes to not save the changes.
  5. When you are finished setting up the record filter, click Done. If the Enter a Value tab opens, click Cancel.

Move a Condition

  1. From the Set Record Filter tab, click the Set Record Filter button. The Conditions List page opens and lists previously saved conditions in the order in which you added them. This is the order in which Sage HRMS evaluates the conditions in the record filter.
  2. From the list of conditions, select the one you want to move and click once to highlight the condition.
  3. Click Move. A check mark indicates the condition you selected.
  4. Go to the location you want for the marked condition and double click.
  5. The condition moves to the new position and the other conditions automatically reorder to accommodate the change.
  6. Click Cancel to discard your changes. A message asks if you want to exit without saving changes. Click Yes if you do not want to save the changes.
  7. When you are finished setting up the record filter, click Done. If the Enter a Value tab opens, click Cancel.

Bracket Two or More Conditions

Brackets enable you to add parentheses around two or more conditions to force those conditions to be evaluated together. For example, you might want to find employees hired during a certain date range with an employee status of either A (active) or L (leave of absence).

  1. From the Set Record Filter tab, click the Set Record Filter button. The Conditions List page opens and lists previously saved conditions.
  2. From the list of conditions, select the first one you want to include or if the condition already contains brackets, remove from the brackets. Click once to highlight the condition.
  3. Click Bracket. The Insert or Remove Parentheses dialog box opens.
  4. Click Insert to add parentheses. A check mark indicates the condition you selected.
  5. Select the condition where you want to place the matching ending parentheses and double click.
  6. OR

  7. Click Remove to remove existing parentheses. A check mark indicates the condition you select.
  8. Select the condition that contains the matching ending parentheses and double click.
  9. Click Cancel to discard your changes. A message asks if you want to exit without saving changes. Click Yes if you do not want to save the changes.
  10. When you are finished setting up the record filter, click Done. If the Enter a Value tab opens, click Cancel.

Delete a Condition

  1. From the Set Record Filter tab, click the Set Record Filter button. The Conditions List page opens and lists previously saved conditions.
  2. From the list of conditions, select the one you want to delete and click Delete.
  3. Click Cancel to discard your changes. A message asks if you want to exit without saving changes. Click Yes if you do not want to save the changes.
  4. When you are finished setting up the record filter, click Done. If the Enter a Value tab opens, click Cancel.