SQL expression

After the required table exists, you must write an SQL expression to link the master input fields with a slave input field to extract the data you want to display in the filtered DBLookup. The SQL expression resides in the slave 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 master and multi-row DBLookup filtering

In this single master, multi-row 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'
masterInputFields=[FIELDS3X]
masterDataFields=[PROJECT]
multipleRowMasterSlave=true

Example: Multiple master and multi-row DBLookup filtering

In this multiple master, multi-row 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'
masterInputFields=[FIELDS4X,FIELDS3X]
masterDataFields=[DEPARTMENT,PROJECT]
multipleRowMasterSlave=true