Setting up DBLookup filters with multiple primary input fields

  1. Locate the secondary DBLookup that you want to filter.
  2. Click the Turn ON config mode icon.
  3. Click the orange diamond icon beside the field you want to filter to open the Field UI Configuration window.
    Sometimes there are field parameters already set in the Field UI Configuration window. In that case, evaluate the existing parameters. You may want to replace them with the DBLookup filter, or you may want to append the filter to the existing parameters.
  4. Specify the appropriate SQL statement in the Field Parameters field. For example:
    sourceType=SQL
    source='SELECT DISTINCT JOB.JOB_ID, JOB_NAME, JOB_DESC, 
    MYPROJ_NAME AS PROJECT, MYDEPT_NAME as DEPARTMENT FROM JOB, 
    PROJECT, DEPARTMENT, DEPT_PROJ_JOB WHERE 
    DEPT_PROJ_JOB.JOB_ID=JOB.JOB_ID AND 
    DEPT_PROJ_JOB.PROJ_ID=PROJECT.PROJ_ID AND
    DEPT_PROJ_JOB.DEPT_ID=DEPARTMENT.DEPT_ID'
    primaryInputFields=[FIELDS4X,FIELDS3X]
    primaryDataFields=[DEPARTMENT,PROJECT]
    multipleRowPrimarySecondary=true

    See SQL expression.

    When you have multiple primary DBLookups, the order of the primaryInputFields and the primaryDataFields are important. The order for both is the opposite of the order of the DBLookups on the page.

    For example, if the DBLookups on the page are in the order Project, Department, and Docket, then the order of the primaryInputFields and the primaryDataFields in the SQL expression must be reversed: FIELDS5X (Docket), FIELDS4X (Department), FIELDS3X (Project).

    The SQL is:

    primaryInputFields=[FIELDS5X,FIELDS4X,FIELDS3X]
    primaryDataFields=[DOCKET,DEPARTMENT,PROJECT]
  5. Click Save.
    The filter configuration is now set. If the settings have not taken effect, you can clear the system cache. For more information on how to do that, see Unloading the cache.