SQL expression

After the required table exists, you must write an SQL expression to link the primary input fields 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 complete the SQL expression and get the desired results.

Example: Single primary input field and multi-row DBLookup filtering

In this example, the Job DBLookup is filtered by the Project DBLookup on the Weekly Timesheet page.

The DEPT_PROJ_JOB table has been defined to contain the relationship data between projects and jobs.

sourceType=SQL
source='SELECT DISTINCT JOB.JOB_ID, JOB_NAME, JOB_DESC, 
MYPROJ_NAME AS PROJECT FROM JOB, DEPT_PROJ_JOB WHERE 
DEPT_PROJ_JOB.JOB_ID=JOB.JOB_ID'
primaryInputFields=[FIELDS3X]
primaryDataFields=[PROJECT]
multipleRowPrimarySecondary=true

Example: Multiple primary input fields and multi-row DBLookup filtering

In this example, the Job DBLookup is filtered by both the Project and the Department DBLookups:

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