Introduction
A database integration can be used with both Lookups and Autolookups. These are integrations designed to retrieve specific data in real time as a form is being completed.
An autolookup runs automatically when the form is loaded and is not visible to the form filler.
A lookup will run after text has been entered in a previous field or after a button has been selected, thus its running is initiated by the form filler.
Table of Contents
Autolookups
Autolookups are normally used when wanting to bring one result from a database integration into a form. When adding an autolookup to your form, you can select the desired integration from the dropdown list and choose the lookup condition. The options for the lookup condition are:
- Run on Display and Input Change
- Run on Input Change
- Run on Display
- Run on Button
- Run Once
This allows you to control whether the autolookup will run as soon as the form is displayed, if specific input needs to be entered or if you want to limit the amount of times the autolookup will run in the form (Run Once).
You can also place a display condition on autolookups meaning that the above behaviour will only occur once the autolookup 'displays' by meeting the set condition.
Regarding the results of autolookups:
- No results message - If no results are found, you can enter a No Results Message that will display on error.
- Note: The No Results message only works with lookups on mandatory select fields.
- If only one result is found, the autolookup will display these results by matching the tokens or data names that have been entered in the database integration.
- If more than one result is found, the form filler will be given the option to select which result they wish displayed in the form.
Lookups
Lookups can be added to a Select or Button field in Forms. Most commonly, select lookups are used when wanting to bring back multiple results from a database integration which can be selected from a dropdown, checkbox or radio field. Button lookups can also be used to bring back multiple results also and this will display a pop out with all the results, giving the form filler the option to select one. Alternatively you can place an email or printable integration on the button that will send (email) or display in the form (printable).
In a Select field, you will need to select Lookup in the List Source and then select the database integration from the dropdown list under the Lookup header. You can change the lookup condition to one of the options mentioned above.
Please note that, when using a lookup with a select field, you need to return at least one column as name and one as display. The value ('name') returned by your integration should be a unique value. If the name column has the same value in two rows, the last row processed is the one that will be shown in the select field.
Similarly to autolookups, you can apply display conditions to fields which have a lookup attached to them. Furthermore, you can also expect the results of the lookup to follow the same behaviour as autolookups.
If you have a lookup which is returning unexpected results, the best way to resolve this is to add a display condition to the select field and this will prevent the lookup from running unexpectedly.
You cannot populate a second select field with the value of another select field which has a lookup attached to it. If you are looking to pull through a value from the first select field into the second, it will only work for the first value that is selected. If the selection is changed, the second select field will not update with the new value. Please use a text field instead of a select field as this will pull through the correct value every time.
Field Details - Autolookup
- Label - The name of your field that appears to the form filler.
- Data name - The data name of the field in your form. This should match the field/token placed in the database integration (in order to bring back the desired results)
- Lookup - From the dropdown list, select the desired lookup for the form.
- Lookup condition - From the dropdown list, select the run type of the autolookup as listed above. This is automatically set at Run on Display and Input Change.
- No results message - If no results are found, you can enter a No Results Message that will display on error.
- Note: The No Results message only works with lookups on mandatory select fields.
- No Retry on Integration Failure - If the autolookup fails, ticking this box will stop the integration running repeatedly which it will do as an attempt to find results.
Field Details - Select Lookup
- Choose field type - You can choose to have the field as a dropdown list, radio buttons or checkbox fields.
- Label - The name of your field that appears to the form filler.
- Data name - The data name of the field in your form. This should match the field/token placed in the database integration (in order to bring back the desired results)
- Default Type - When using lookups, this should be left as Specific Value.
- Default Value - This can be left blank as the results will be coming directly from the lookup.
- Help Text - You can enter help text to be visible to the form filler if necessary.
- List source - From the dropdown list, select Lookup which will in turn display the Lookup field, where you can select the lookup you want for the select field.
- Lookup condition - From the dropdown list, select the run type of the lookup as listed above. This is automatically set at Run on Display and Input Change.
- Lookup Button - If you have a button on the form, you can select this from the dropdown which means the lookup will be triggered as soon as the button has been pressed.
- No results message - If no results are found, you can enter a No Results Message that will display on error.
- Note: The No Results message only works with lookups on mandatory select fields.
Once you have selected the lookup for your select field, pull in certain fields from your integration by inserting a text field above the select field then assigning the default value for the text field to the value you would like to pull in from the integration.
Field Details - Button Lookup
- Label - The name of your field that appears to the form filler.
- Data name - The data name of the field in your form. This should match the field/token placed in the integration (in order to bring back the desired results) if needed to populate another field.
- Help Text - You can enter help text to be visible to the form filler if necessary. Lookup - From the dropdown list, select the desired lookup for the form.
- Lookup - Press inside the text area and a dropdown of the integrations will appear. Select the one needed for the field.
- No results message - If no results are found, you can enter a No Results Message that will display on error.
- Note: The No Results message only works with lookups on mandatory select fields.
Please note: There is no way to specify in which order integrations run when they are triggered by a button.
Button Display Conditions
To display a field only once a button has been clicked, such as an address select field that should only display when the Find address button is selected, you can create a database integration that populates a field {Button_Clicked} in the form.
A display condition can then be added to the select field to only appear when {Button_Clicked} is 'Yes', or similar.
Can I trigger a lookup on leaving a field?
There isn't a function to trigger the lookup on leaving a field but we can prevent it running too soon with a display condition. Regex might be best for the specific field, i.e. {yourfield} matches '^yourRegexHere$'. ?This way, the lookup will only display and therefore run if there is a valid input present.
Further Details to Consider
Subforms
If populating fields in a subform with a lookup:
- The lookup should exist in the subform rather than outside of it.
- Using the 'run on display and input change' option will not work, as it will only run on input change. You need to make sure your input is new or refreshed for the subform lookup to run again.
Security
It is possible for the information returned in a lookup to be viewed in its entirety, beyond what is specifically presented n the form, by a member of the public utilizing developer tools. Please take this into consideration when you set up the data that is returned.
Troubleshooting
Use Trim Function
Leading and trailing spaces can severely impact the behavior of lookups and autolookups. If you are experiencing odd behavior with lookups, please try using the 'trim' function to ensure that the values being passed are sanitized.
More information can be found here.
Duplicate Logs
We have found that some lookups will register in the logs as running twice. Through testing we discovered that, in some circumstances, the 'Prevent Running on Initialization' setting is not preventing the lookup from running when you first load up the form into the process. We have observed and tested that this happens when tokens are utilized in the form. The first time the lookup runs, it is registering the token. The second time, it is registering what the token should be.
Some people are utilizing this function in ways that are useful to them, and it has not proved problematic to desired function.
Please see further details here regarding various settings configurations and expected functions.
No Results When Lookup Runs on Input Change
If a lookup/autolookup is set to 'Run on Input Change' and runs once, thus populating form fields with data, if when the input is changed and the new input returns no results, the data that had originally populated the fields will remain. This is because the integration may be designed to only provide data to update the form when a result is successfully found. If you want the form to update when no data is found, then the integration should return NULL or empty string values for each column in the returned row. This can be achieved most easily by implementing a stored procedure within your database, so that you can make use of transaction logic.
Further useful reading:
top of page
