Modifying the SQL expression

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

Continuing our example, we will modify the SQL expression of the Department lookup (secondary input field) to show only the active departments with the team selected in the Team lookup (primary input field).

In the desktop application, select Maintenance > System Administration > Maintenance Forms.
Click Edit beside the MOB_TIMESHEET maintenance form.

Note: Setting up a primary-secondary relationship is only supported on the mobility timesheet.
In the Form Fields section, locate the DBLookup being filtered. In our example, FIELD_TIMESHEET_DETAILS_WORKDETAILS_DEPARTMENT.

Note: The field control is MobLookupUI. This UI should not be modified.
Locate the Field Parameters column where the DBLookup query resides.
Modify the SQL expression to set up the primary-secondary relationship.
To configure the Department lookup to show only the active departments with the currently selected team, we will set up a primary-secondary relationship between team and department.

Modify the SQL expression for the Department lookup as follows (changes in bold):
```
sourceType='SQL'

source='SELECT DEPT_ID, DEPT_NAME, DEPT_LOC_NAME,DEPT_LOC_DESC 
from VL_DEPARTMENT, WORKBRAIN_TEAM 
WHERE VL_DEPARTMENT.WBT_ID = WORKBRAIN_TEAM.WBT_ID AND 
WORKBRAIN_TEAM.WBT_NAME = ? AND
CONVERT( DATETIME, LEFT( ?, 8 ), 112 ) <= DEPT_END_DATE and 
DEPT_START_DATE <= CONVERT( DATETIME, LEFT( ?, 8 ), 112 ) 
ORDER BY DEPT_LOC_NAME'

sourceParams='STRING==#request.CONTEXT_team_NAME#~|~
STRING==#request.WORK_DATE#~|~STRING==#request.WORK_DATE#'

searchColumns='DEPT_LOC_NAME' 
```
The context variable, CONTEXT_team_NAME, will be substituted for the team name selected by the user.

See Context variables for a list of context variables that can be used for substitution.

Note: This query assumes you are using a Microsoft SQL Server database. For other databases, the functions must be changed accordingly. In this case, the CONVERT function must be changed to the appropriate function for your database.
Click Save.