Creating a Source Lookup Query

Source Lookup Queries can be used with all Server Types.

To create a new Source Lookup Query, navigate to the Lookup Queries Tab on your Form Mapping then select the Type (Source) and the Search Form from the drop-down list.  The Identifier field is auto-populated but you can change the value if necessary.  The identifier is used to uniquely identify the Lookup Query that you have defined.

Now click on the Query Builder button to define your Lookup Query.  Note that we are not defining which field value to return here, just the query itself. You can use the same query to select different fields for different field mappings. These are selected as part of the Field Mapping Definition.

The Source Lookup Query window is shown above.

Select the field from the Search Form Field menu, then enter a condition using any of the operators below (AND, OR, NOT, (, ). =. != etc.), in combination with a keyword value (NULL, DATE, TIMESTAMP, TIME, TRUE, FALSE, MONTHSAGO(), etc), and/or the Current Entry Source Value from the Source Form or a Temporary Variable.

In the example above, the SysID is used to identify a record on the Request Table based on the Request field value on the current Form Mapping Source Form.  So for example, if the current Source Table is Requested Item, then we can supplement the values used in this mapping with values from the Request table, using this Lookup Query.

The keywords available are the same as for the Source Filtering Query Builder and depend on the Source Server Type.

Note that the Temporary Variable must have been defined and assigned a value before it can be selected and used in a Lookup Query expression. 

Unlike Source Filter Queries, project variables cannot be used in Lookup Queries. If you want to make use of a project variable, first create a temporary variable and use a simple mapping to assign the project variable to the temporary variable, then use the temporary variable in your lookup query.

When you have completed the Lookup Query definition, click on OK to validate and save the query.  Once saved the query can then be used to support any Lookup-type field mapping on the current Form Mapping.

For more information on defining and using temporary variables, see article on Creating Temporary Variables.

Null Parameter Action

If your query contains parameters (for instance current source/target fields or temporary variables) you can optionally choose how to handle queries that may get executed at execution time where one or more of the parameters is NULL. This can be useful for instance if it is often the case where a parameter is NULL and you always want to set the target value to NULL if this is the case. Note that this option will apply to ALL field mappings where the lookup query is used.

The options are as follows:

Null Parameter Action	Description
Run Query	The query will be run anyway, with the NULL matching any NULL values in the lookup form's data. This is the default option.
Return NULL	The query will not be run, and the value returned from the lookup field mapping will always be NULL. This can prevent unexpected results where a null match occurs due to missing data on the lookup table. It also improves performance where the parameter is an optional field that does not always have a value, since it prevents queries being run o the server unnecessarily.
Return No Match	The query will not be run and the No Match Action for the field mapping will be followed (Set Null, Log Error, Set Default etc)
Ignore	The query will not be run. If a record is getting updated, any existing value for the field will not be changed. If a new record is being created, the value will be set to NULL, or a default value if the target server sets one automatically.