Subform Setting

SQL Query

The SQL Query will appear once the 'Lookup' or 'Read Only' options within the Basic tab are selected. You can use the SQLScript editor to validate your script.

Use an SQL Script in this field to look up the values of multiple fields from one or more tables, at the same time. The lookup is based on the value of a trigger field in the subform. The lookup is performed whenever the value of the trigger field changes. The trigger field must have the Refresh on change option enabled.

The ALIASES returned in the SQL Script (after the AS clause) should match the name of the field setting that will be populated. Refer to the example that follows and note that the field names after the AS (which we call ALIASES) are real names for field settings.

Example - Lookup

The following script is used to lookup Customer Information from the Lookup table. This script indicates that the Customer Number will be the trigger key, i.e. If you want to look up customer information, you select the customer’s number (note that last statement in the script), and then the remaining information will be selected automatically.

Each field in the Subform will read from one column in the lookup table.

SELECT

CUSTOMER_NO AS CUSTOMER_LOOKUP,

CUSTOMER_NAME AS CUSTOMERINFO_NAME,

CUSTOMER_PHONE AS CUSTOMERINFO_PHONE_NO,

CUSTOMER_CONTACT AS CUSTOMERINFO_CONTACT_NAME,

CUSTOMER_FAX AS CUSTOMERINFO_FAX_NO,

CUSTOMER_EMAIL AS CUSTOMERINFO_EMAIL,

CUSTOMER_ORDER_NO AS CUSTOMERINFO_ORIGINAL_ORDER_NO,

CUSTOMER_CALL_NO AS CUSTOMERINFO_CALL_NO,

CUSTOMER_ADDRESS AS CUSTOMERINFO_ADDRESS

FROM LOOKUPS.CUSTOMER_INFO WHERE CUSTOMER_NO = ? 

Formula (Refresh Options)

The Formula (EtQScript) appears once the 'Conditional' checkbox for the Refresh Options field within the Advanced Tab is selected. Using an EtQScript, you can specify a certain condition to refresh the subform automatically when this condition is met. Remember to use the EtQScript Editor features.

Example (1)

The following example forces a subform to refresh only if the key field has a value. Otherwise, the subform will retain the current values.

if (thisDocument.getField("DOCUMENTINFO_LOOKUP").getDisplayText() != None):

 print true

else:

 print false