LOOKUP a value from a Resource
Using the Excel resource 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.
Using Lookups in Numerical Calculations
If you would like to use a Lookup value in a numerical calculation, the LOOKUP expression will need to be surrounded by the TO_NUMBER() function, in order to change the pulled value to a numerical value.
Image Lookup Limitation
It's unfortunately not currently possible to LOOKUP images from a resource, but there are a couple of work-arounds:
- Dispatch a single dispatch that contains the image.
- If the image is required in the final report only, you can add all images to the Word template and use if statements in the template to make them conditionally active based on an answer value.
Other Useful Articles:
If you have any questions or comments feel free to send us a message at email@example.com.