Lookup tables allow you to search on and select an answer from an external data table. Additionally, if a lookup table row contains answers for other questions in your form, you can use the table to automatically answer these questions as well. For example, if your form contains questions for entering “Customer Number”, “Customer Name” and “Customer Address”, and if you have a lookup table that also contains these columns, then you can set up “Customer Number” as the Lookup Value, and when the mobile user searches on and selects a Customer Number, the corresponding Customer Name and Customer Address will be automatically filled out in the form.
The illustration below shows a simple lookup table CSV file.
The Option Menu section above provides instruction on uploading lookup table files into the Build Forms tab. Once a lookup file is uploaded, it may be used in one or more forms as described below. External files must be CSV or Excel 2003 format, and the first row of the file must contain field names for each column (no spaces or special characters).
To see which lookup tables are available in your account, click the Options menu and select Manage Lookup Tables. In the illustration below, the lookup table name “Customer List” contains the data above.
After you have confirmed that the desired lookup table has been uploaded, you are ready to set up a “lookup” question in your form.
To set up the lookup table in your form, simply drag and drop the “Lookup” question widget into the desired location in the question canvas as illustrated below.
The Lookup question widgets include the following special properties that you can use to control the look and behavior of lookups:
Caption text - Refers to the question caption as presented to the user. Think of this as the title for the question; it should be self descriptive. We recommend no more than three words for optimal display on mobile devices.
Hint - Additional help for the question will be displayed; short clear sentences work best. Use 80 characters or less for optimal display on mobile devices. The maximum number of characters is 500, including spaces. The following HTML formatting tags are currently recognized:
Android: <br>, <i>, <b>, <u>, <font color="xxx">, <H1>, <H2>, <H3>, <H4>, <H5>, <P>, <a>, <div>;
PDF reports: <br>, <font color="xxx">.
Data name - Represents the name of the data that will be displayed in column headings. This property is automatically filled in based on the Caption Text, but may be modified in the properties area. We recommend one or two word data names to keep column headings short. The maximum number of characters is 30. Spaces are not permitted in the Data Name field.
Default value - The value of the field first presented to the user when they see the question. Unless the question is marked as “Read Only”, the mobile user will be able to change the value. This is very useful where the answer to a question is likely to repeat itself over and over. Setting a default value minimizes the work for mobile users. Note that repeating values can also be set on the mobile app using the “Remember Answer” option.
Read only - Determines whether or not this field can be edited. This is used in combination with a Default Value.
Required - Determines whether or not this field must be filled-in before continuing to the next question. On the mobile device, a form with an empty required field may be saved as “incomplete” but not as “complete” and hence cannot be sent to your website.
Limit to list – Limits the entry of data to a lookup table selection only. If the lookup has any destination questions, the answers to these questions will also be limited to the corresponding values in the lookup table.
Sort lookup list alpha-numerically – Check this if you want to sort your lookup field alpha-numerically. Numbers are sorted first, followed by letters. If you wish to avoid sorting results like 1, 10, 11, 12, 2, 3 …..8, 9, then pad number values with leading zeros 01, 02, 03 … 08, 09, 10, 11, 12. If this option is not selected, then the order of the list will be exactly as arranged in the Source Data.
Display the capture barcode button – If checked, the mobile app will include a Barcode scanning control in the question widget. This is handy if your “Lookup Field” is a barcode.
Display scan NFC button (Android only) - Check this if your lookup field is a NFC that will be scanned.
Choose-one destination fields – Choose-one questions have two ways of describing each answer option ... “Answer as displayed to the user” and "Underlying_value”. If a destination field for a lookup is a choose-one question type, then you will need to specify which of these to use to determine which answer option to select.
For example, suppose your form has a destination field that is a choose-one question with the following answer options:
Answer as displayed to the user Underlying_value
There are two answer options ... YES/1 and NO/0 ... that correspond to the radio buttons in the choose-one question.
If your lookup table contained YES/NO answers, you would UNCHECK this option so the YES or NO values are used to determine which answer option to select. So if the lookup value is "YES", then the YES/1 answer option is selected in the choose-one destination question. Conversely, if the lookup value is "NO", then the NO/0 answer option is selected in the choose-one destination question.
But suppose your lookup table had 1/0 answers. In this case you would CHECK this option so the 1 or 0 values are used to determine which answer option to select. So if the lookup value is "1", then the YES/1 answer option is selected in the choose-one destination question. Conversely, if the lookup value is "0", then the NO/0 answer option is selected in the choose-one destination question.
Use lookup in website Dispatch tab only – Use the display of the lookup in the website Dispatch tab only. This property is used when you wish to use a lookup to pre-populate a dispatched record on the website prior to sending, but do not wish to send the lookup table to the mobile devices.
Use as input for conditional lookups only – Check this option to improve speed if this widget is only being used to support conditional lookups later in your form. Uncheck if you want full lookup functionality.
Display keyboard in portrait mode - Controls if the keyboard will be displayed when the device is in portrait orientation. In most cases, this setting should be checked since displaying the keyboard provides a fast way for searching through long lookup lists.
Display keyboard in landscape mode - Controls if the keyboard will be displayed when the device is in landscape orientation. In most cases, this setting should not be checked since displaying the keyboard will reduce the screen area for displaying the lookup list.
Wrap text - Controls if long text strings in the lookup list will be displayed one to a line (not checked) or wrapped on more than one line (checked). Check this option if you expect your forms to be used primarily on smaller-screen devices.
Length – Validates the character length of the user input in terms of a minimum and a maximum number of permitted characters (spaces are counted).
Kind – Specifies the type of value used in the lookup field. For barcode widget, the kind of value can be string or number.
Text size - This property allows you to choose the size of the text that will be displayed from xsmall, small, medium, large and xlarge.
Data source – Selects the lookup table to be used. In the example below, we are using the “Customer List” lookup table. Use the “Refresh” option if you have recently added a new lookup table as a Resource and do not see it in the list of available data sources.
Lookup field – Selects the field in the lookup table that will be used in the lookup search/list. In the example below we are using the “CustomerNumber” field in the lookup table.
Lookup conditions – Specifies if any prior lookup selection from the same lookup table will be used to filter the lookup field. For example, if you have a lookup table with state and city columns, you could set up a lookup question to select the state and a second lookup question to select the city. In the second lookup question you could make the lookup condition “State” so that only the cities in the selected state are displayed. Lookup conditions can also be specified as the answer values to a prior text, numeric or choose one question in the form. Use the “Add field” option to add a new condition.
Evaluate blank (null) condition values – This option can be used to control how lookup conditions work in the special case when the lookup is being used as a data source for a checklist in a table container. CHECK this option if you DO NOT want to include blank (null) values in the checklist. UNCHECK this option if you DO want to include blank (null) values in the checklist.
Destination fields – Provides a “mapping” of any additional lookup table fields that will be used to populate other question fields in your form. In the example below, we are going to search on the lookup field “CustomerNumber”, then use the “NameLast” field in the lookup table to populate the “Last_Name” field in the form, the “NameFirst” field in the lookup table to populate the “First_Name” field in the form, and “Address” field in the lookup table to populate the “Address” field in the form. Use the “Add field” option to add a new destination field.
Advanced - Clicking on the Advanced arrow will reveal “Relevance conditions” and “Skip conditions” properties that can be used of further control the behavior of your form using sophisticated logical statements. Please refer to http://support.doforms.com/hc/