Modifying the SQL expression

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. The SQL expression resides in the secondary input field.

Note: You must know the actual field names in the required table to modify the SQL expression and get the desired results.

Example: Filtering the Department lookup based on the selected team

Currently, the Department lookup is configured to show all the active departments on the work date. 

sourceType='SQL'
width='7'
source='select DEPT_NAME, DEPT_LOC_NAME
from VL_DEPARTMENT
WHERE 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#'
locale='en'
labelFieldStatus=edit

In this example, you want to configure the Department lookup to show only the active departments that are associated with the currently selected team. To accomplish this, you must set up a primary-secondary relationship between team and department.

For the detail row, modify the SQL expression for the Department lookup as follows (changes in bold): 

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]
For the premium row, modify the SQL expression for the Department lookup as follows (changes in bold): 
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 ANDYou must know the actual field names in the required table to modify 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=\'PREM_WBT_NAME\']/input]'
primaryDataFields=[WBT_NAME]

SQL expression elements

Your database administrator will have detailed information about the SQL expression elements that are useful in your unique system. The DBLookup filtering SQL expressions have these elements:

  • sourceType

    Specifies the DataSource type:

    • SQL
    • REGISTERED
  • source

    Specifies the DataSource specification.

    • If the sourceType is SQL, then an SQL SELECT statement must be provided.
    • If the sourceType is REGISTERED, then the name of the builder class registered under the /system/rowsource/builders registry parameter must be provided.

    The source expression element is code that tells the system from which tables to extract the primary and secondary input data and where to locate the data to display in the filtered secondary DBLookup.

  • sourceDisplayFields

    Specifies a pipe separated list of display fields. By default, all fields except the first field in the SELECT statement are displayed in the DBLookup.

  • primarySecondaryUseXPath

    Indicates whether the primary input field is configured using an XPath expression instead of name.

    For setting up DBLookup filters in the inline edit of the Daily Timesheet, this must be set to true.

  • primaryInputFields

    Enables a primary-secondary relationship between fields. Specifies names of input fields narrowing down the current field's set of values.

    For setting up DBLookup filters in the inline edit of the Daily Timesheet, this must be an XPath expression. The XPath expression is used to get the actual value of the primary input field.

  • primaryDataFields

    Enables a primary-secondary relationship between fields. Specifies names of data fields narrowing down the current field's set of values.