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

String, required - DataSource type:

• SQL: Requires SQL SELECT statement.
• REGISTERED: Requires the name for the builder class that is registered in the application registry path /system/rowsource/builders.

sourceKeyField

String, optional - key field name. Default: first field. This value is the underlying data value to store in the relevant database field.

sourceLabelField

String, optional - label field name. Default: second field. This field should populate the label of the DBLookup in the DBLookup’s closed or default state.

sourceDisplayFields

String, optional - a pipe separated list of popup display fields. Default: all except first field. The fields to display to the user when the DBLookup is in the expanded state.

Source

String, required - DataSource specification:

• SQL: Requires a SQL statement.
• REGISTERED: Requires the name for the builder class that is registered in the application registry path /system/rowsource/builders.

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. In this example, the Job DBLookup is filtered by the Project DBLookup:

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

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

This name is the field name for the DBLookup that you want to use as the source for filtering. In the preceding examples, this name is the Project DBLookup or Project and Department (separated by a comma without spaces) DBLookups in the case of multiple primary input fields. The primary input field must be surrounded by square brackets.

To display the field name, use the mouse to hover over the key icon near the field name in config mode.

If the filter is a multiple row filter, use the mouse to hover over the key for the first row of the field. The field name for multiple row filters includes an underscore and a number, such as FIELDS3X_0. Remove the underscore and following number so that the system understands the filter is for that field on any row, such as FIELDS3X.

If you have multiple primary DBLookups, the order of the values must:

• Reverse the order of the DBLookups on the page.
• The order of the corresponding primaryDataFields must match the order of the primaryInputFields.

primaryDataFields

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

If you have multiple primary DBLookups, the order of the values must:

• Reverse the order of the DBLookups on the page.
• The order of the corresponding primaryInputFields must match the order of the primaryDataFields.

multipleRowPrimarySecondary

Set to true or false. Default is false.

• Set to true if the DBLookup has multiple rows.
• Set to false if the DBLookup has a single row.