In this article we'll be covering our Lookup function for Calculated Questions. This feature allows you to set an answer on a Calculated Question by looking up a value in an Excel spreadsheet.
Using the example below of the Equipment Quality Check, let's say that each piece of equipment has a barcode associated with it.
Note: If you would like to use a Date Field to look up a value, your spreadsheet cells will need to be changed to a 'text' format and the dates formatted to:
To set this feature up in your form, first add a Barcode Question and a Calculated Question.
Next, click the "Set Expression" button in the Calculated Question's Field Properties box.
Once the Expression Menu has appeared, click the "Add Function" button.
You will then be brought to a separate section, with a drop-down box at the top. Click on the arrows of the drop-down box, and select "LOOKUP". You will now see that some boxes have appeared that ask for specific information.
Click where it says "Select Resource" and select or upload the Excel spreadsheet you want to use. After you've done that, select the correct table (sheet) from the "Table" drop-down.
Note: To learn more about adding resources, click here.
Beneath the "Table" drop-down, you will see that it says "Key Column" and "Value Column". The "Key Column" is the column against which you want to match or in which you want to lookup, while the "Value Column" is the answer from the row you want to get. In our example you will want to match the barcode of the equipment, so set "Key Column" to "Barcode". You will also want to get the Name of the Equipment so set the "Value Column" to "Equipment".
At the bottom you will see that it says "Key Value". This is where you indicate which question in your form matches the "Key Column". Using this example, click the "Choose field" button.
And then select the Barcode Question.
Once you have done that, click "Add this function".
You will now see the Calculation that has been created according to your specifications.
Once you're done, you can Save & Exit your form.
Other Useful Articles:
- Select Options from a Resource
- Auto Populate Data in Multiple Fields by Answering a Select Question
- Calculated Question Expressions
If you have any questions or comments feel free to send us a message at firstname.lastname@example.org.