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.

  1. Identify the field that is used to filter the DBLookup. This is the primary input field.
  2. Identify the DBLookup being filtered. This is the secondary input field.
  3. Create an XPath expression to select the value of the primary input field (relative to the secondary input field). The XPath is included in the SQL expression of the secondary input field (Step 4).
    See Creating the XPath expression.
  4. Modify the SQL expression of the secondary input field. The SQL expression links the primary input field with a secondary input field to extract the data you want to display in the filtered DBLookup.
    See Modifying the SQL expression.
  5. Configure the DBLookup of the secondary input field to use the SQL expression.
    See Configuring the DBLookup of the secondary 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 primary input field and the Department lookup is the secondary 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
primarySecondaryUseXPath='true'
primaryInputFields='[ancestor::tr/td[@data-field=\'WBT_NAME\']/input]'
primaryDataFields=[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 primaryInputFields 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.