Exception Queries

Exception queries are used to return records that don’t exist, for example a list of current employees who were not sick in the previous year, a list of employees in the Swansea site who were not paid last month.

Two queries are compared and the resulting query shows whether or not the records in the first query also exist in the second query.  Note that the Employee Reference field must exist in both queries.

Example – Current employees who were not sick last year

The first query would define the current employees and would be based on the HR Record entity and have a filter of HR Record Status <> Left.

The second query would be based on the Absences entity and have a filter of Absence Type = Sickness and Start Date between Previous Calendar Year Start and Previous Calendar Year End.

The exception query would list the records that exist in the current employees query, but don’t exist in the last years sickness query, in other words, current employees that were not sick last year.

Method

Exceptions will envoke the Exceptions wizard.

Exceptions qualifying query

Queries based on the HR Record, Employment History, Balances – This Year or Pay Summary entities will be listed here.  This is the query that contains the employees to check, for example all current employees.

Click Next.

Select the query to check against

All other queries are listed here.  Select the query that contains the records that do exist, for example last years sickness.

Set as Exception Query

If Set as Exception Query is not checked, the excption query will list all records in the qualifying query with a Qualify field showing Yes if the record has a corresponding record in the second query and No if there is no corresponding record in the second query.  The exception query results are not saved.

If Set as Exception Query is checked, the excption query will list only records in the qualifying query that do not have a corresponding record in the second query.  The exception query results are saved.