Setting up DBLookup filters for inline edits of detail/premium fields

You can configure the application to use filters on DBLookups (database lookups). Filtering lookups is useful to present only a relevant subset of selections to the user, based on a prior selection. For example, for users making inline edits of work details, you can filter the values in the Department lookup based on what is selected in the Team lookup.

Note: This section covers setting up DBLookup filters in the inline edit of the Daily Timesheet.

See "Filtering DBLookups" in the Infor Workforce Management Time and Attendance Implementation and Administration Guide for general information on setting up DBLookup filters in the application.

To set up a DBLookup filter for inline edits:

  1. Identify the field that is used to filter the DBLookup. This is the master input field.
  2. Identify the DBLookup being filtered. This is the slave input field.
  3. Create an XPath expression to select the value of the master input field (relative to the slave input field). The XPath is included in the SQL expression of the slave input field (Step 4).
  4. Modify the SQL expression of the slave input field. The SQL expression links the master input field with a slave input field to extract the data you want to display in the filtered DBLookup.
  5. Configure the DBLookup of the slave input field to use the SQL expression.

Example: Filtering the Department lookup based on the selected team

In this example, the Team lookup is the master input field and the Department lookup is the slave input field.

For the Details row, the SQL expression for the Department lookup is as follows:

sourceType='SQL'
width='7'
source='select DEPT_NAME, DEPT_LOC_NAME, WBT_NAME
from VL_DEPARTMENT, WORKBRAIN_TEAM
WHERE VL_DEPARTMENT.WBT_ID = WORKBRAIN_TEAM.WBT_ID AND TO_DATE(cast(? as char(17)), \'yyyymmdd hh24miss\') <= DEPT_END_DATE and DEPT_START_DATE <= TO_DATE(cast(? as char(17)), \'yyyymmdd hh24miss\')'
sourceParams='STRING==#request.WORK_DATE#~|~STRING==#request.WORK_DATE#'
sourceDisplayFields='DEPT_LOC_NAME'
locale='en'
labelFieldStatus=edit
masterSlaveUseXPath='true'
masterInputFields='[ancestor::tr/td[@data-field=\'WBT_NAME\']/input]'
masterDataFields=[WBT_NAME]

Only the active departments (DEPT_LOC_NAME) that are associated with the currently selected team (WBT_TEAM) are displayed in the Department lookup. The actual team name is retrieved using the XPath expression specified in the masterInputFields element.

Note: The queries in this section assume you are using an Oracle database. For other databases, the functions must be changed accordingly. In this case, the TO_DATE function must be changed to the appropriate function on your database.