A Data Lookup is a drop-down field within a form that references an external data source (called a Lookup table), allowing the mobile user to select and load data into the form quickly. Learn more about Lookup tables.
For instance, in the animated image below, the user selects three items from data lookups. After each selection, the item description and cost automatically populate the form by referencing the external data source and filling in the form automatically.
By using lookup conditions, you can instruct the form to filter data so that drilling down to the correct record is easy for the user. In the animated image below, the user first selects the Cold Storage equipment type, which filters down the potential refrigerants to four types. The user then selects the Unitary AC equipment type, which filters down the potential refrigerants to only two types.
Default value: This is what the Data Lookup field will initially display when a user opens the form. The user will be able to erase this value. Leave blank to have no value initially appear.
Lookup conditions: Allows the user to filter the data source and show a secondary data set, based on what the user chooses from a primary data set.
For example, you want the mobile form user to choose the city that they live closest to. Your data source is an Excel spreadsheet that contains the 300 largest cities in the United States, split into two columns: STATE and CITY. The following image shows the beginning of the spreadsheet:
You want the mobile form to present two drop-down lists, as follows:
- The user selects a state from the first drop-down list.
- The user selects a city from the second drop-down list. However, this second list has been automatically filtered so it only displays cities located in the state that the user selected from the first drop-down list.
The following animated image shows the user selecting Alabama (primary data selected from first drop-down list), and then selecting Mobile (secondary data selected based on primary data selection).
This is accomplished by including in your form two data lookup fields (State and City) that refer to the STATE and CITY columns located within an existing Excel spreadsheet. However, the City data lookup field contains a Lookup condition that forces it to only show data based on what the user chose in the State data lookup field.
The following two images show the settings for the two data lookup tools that have been added to the mobile form. The first image shows the settings for the State data lookup tool (highlighted in green, below). Note that the Data source is an existing Excel spreadsheet called Largest 300 cities in US. The Lookup field is STATE, which refers to the STATE column in that spreadsheet.
The next image shows the settings for the City data lookup tool (highlighted in green, below). Note that it uses the same Data source as the first data lookup (Largest 300 cities in US). The Lookup field is CITY, which refers to the CITY column in the spreadsheet. This data lookup tool contains a Lookup condition (highlighted in red, below). The Lookup condition field is STATE, which refers to where (in the spreadsheet) doForms will look for secondary data (i.e., the City names). The Condition answer is State_chosen_by_user, which refers to the field within the mobile form that satisfies the primary data.
Display first matching record if only one found: Select the matching record if there is only one record that matches the lookup conditions. If more than one record matches the lookup conditions, no auto select will happen.
- Evaluate blank (null) condition values:
- If not checked, the condition will be ignored when the comparison question is blank.
- If checked, a blank comparison question will match all blank field values in the lookup.
- 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: Destination Fields allow you to easily populate fields in your form from fields (called table fields) in your data source. The image below shows a source spreadsheet with ADDRESS, CITY, ST, and ZIP table fields:
Suppose your mobile form included a data lookup field with the ADDRESS column as the Lookup field. Once the form user chose an address, you want the form to populate with the City, State, and Zip for that chosen address. You would use Destination Fields to do this. Simply add a text field to your form and name it City. Then add two more text fields, named State and Zip Code. Then go back and create three Destination Fields and map them to the newly created text fields. Your City text field would map to the CITY table field. The State text field would map to the ST table field. And the Zip Code text field would map to the ZIP table field.
You can fill in multiple rows of a destination table by using the Table source in a destination field:
- Add a Form based data lookup that includes data from a table.
- Set up a new Lookup data source by selecting Build > Forms > Lookup data > New > Use form.
- Select the Project and Form.
- Uncheck Ignore fields in tables and repeatables.
- Click save.
- Add a field to a Table to be used as the destination field.
- Add a Data lookup and select the lookup from step 1 as the Data source.
- Add a destination field and select the Field target as the field from step 2.
- Select a source Table field from the lookup that includes "(table)". These will display at the end of the list.
Advanced Options: The following special properties control the look and behavior of data lookups:
- Limit to list: Determines if the user is restricted to only selecting an answer from the list provided, or if the user can enter their own answer by typing it in the field.
- Sort lookup list alpha-numerically: Sorts your drop-down list numerically (by digit) and then alphabetically. Because numbers are sorted by digit, note that numbers 1 through 10 would sort in the following manner: 1,10,2,3,4,5,6,7,8,9. To have them sort truly numerically, you would need to number them as follows: 01, 02, 03, 04, 05, 06, 07, 08, 09, 10.
Type: Specifies if the lookup value is String (e.g., alpha, numeric, and special characters accepted) or Number (e.g., only numeric characters accepted).
Length: Sets the Minimum and Maximum values that the mobile user may enter into the field.
Dropdown text: How the lookup data appears for selection.
- Wrap text: Determines if each answer choice in the drop-down will wrap onto multiple lines, or they will each be constrained to a single line (showing only as much text as will fit).
- Text size: Determine the size of the answer choice text in the drop-down.
Keyboard icon: Displays a keyboard icon within the lookup field in the form, which, when clicked, opens a on-screen keyboard.
Open keyboard on click: Opens an on-screen keyboard when the user clicks the field in the form (if the Type of the lookup value is set the String, then an alphanumeric keyboard will appear; if it is set to Number, then a numeric keyboard will appear). If you have a large data source and you would like your user to start typing to filter the results, then displaying a keyboard makes sense. If you are giving them a simple drop-down list to scroll through, then there is no reason to show the keyboard. The reason we differentiate between portrait and landscape is because on small devices in landscape there might not be enough room to show both the keyboard and the dropdown list.
- Decrease load times (for web portal users) of forms with data lookups containing large datasets. By restricting access to web app users, you stop the web app from trying to populate drop-downs, which could become time-consuming (and can dramatically decrease the initial load time of the form) depending on the size of the file.
- Limit access to datasets within mobile forms. By restricting access to mobile users, you stop the system from sending the data set to mobile devices. This feature was designed to allow a dispatcher to select a customer (from a drop-down) for a work order without the system automatically distributing the company's customer master file out to mobile devices.
Select one or more of the following Restrict Data On settings:
- View Data Edit: Restricts the loading of the data lookup data set when a web portal user edits a submitted form through the View data tab.
- View Data New: Restricts the loading of the data lookup data set when a web portal user creates a new form through the View data tab.
- Dispatch Edit: Restricts the loading of the data lookup data set when a web portal user edits a submitted form through the Dispatch tab.
- Dispatch New: Restricts the loading of the data lookup data set when a web portal user creates a new dispatch through the Dispatch tab.
- Mobile: Restricts the loading of the data lookup data set when a mobile user accesses the form.
- Web app: Restricts the loading of the data lookup data set when a web client user accesses the form.
Barcode button: This allows you to set the lookup field to a barcode that the user scans with their smartphone or tablet. Therefore, instead of having the user select from a drop-down list, they scan a barcode (which compares itself to the data source, and then sets the destination fields to the matched record in the data source).
NFC button: This allows you to set the lookup field to an NFC tag that the user reads with their smartphone or tablet (Android only). Therefore, instead of having the user select from a drop-down list, they read an NFC tag (which compares itself to the data source, and then sets the destination fields to the matched record in the data source).
Auto start scan: Start scanning when the user enters the field.
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:
There are two answer options, YES/1 and NO/0, which correspond to the radio buttons in the choose-one question.
If your lookup table contained YES/NO answers, you would un-check 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 contained 1/0 answers. In this case, you would check this option so that the 1/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 is the choose-one destination question.
Use as input for conditional lookups only: Check this option to improve speed if this tool is only being used to support conditional lookups later in your form. Uncheck if you want full lookup functionality.