In this article we will be giving an overview of how to populate data from a resource file by be selecting an option from a select question.

Your select question can either be options that you added or it can be options from a resource file. In this example we will be adding the options manually, but make sure the options match what you have in the spreadsheet.

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: YYYY-MM-DD 

After we added the options to the select question, each question after that you want to have auto populate, will be a calculated question. In this case we need to add 3

Now you need to set the expression for all of them.

The expression in all three is similar, the only field that varies each time is the Value column. The reason for this is that it is the column which data you want to have displayed after the lookup is performed.

Click on the question and click "Set Expression" and then click "Add Function" and select "LOOKUP"

Now upload your resource and attach it to your select question as per usual and select your table.

Your Key column is the column in the spreadsheet that needs to be looked up. This will be your company column as this is the same as the select question.

Your value column is the column of data you want to have displayed as the answer to your "Contact Person" calculated question.

Your Key Value column is the field in the form that needs to match your key columns information

Click Add Function and Save

Now you can repeat this for all the questions you want to auto populate, based on a select questions answer.

At the bottom of the expression builder the LOOKUP expression will be translated to reflect your specific Lookup details, if you need to refer back to the setup in the future.
​
​ LOOKUP( "Resource Name", "Table", "Key Column", "Value Column", "Key Value" )
​

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.
​
For example: TO_NUMBER(LOOKUP("0719c1110-38f9-013a-03fa-0e5123020bd7","46f92e10-38fd-013a-5b59-411111b2c32a","46f938e11111113a-5b59-4e4810b2c32a","cf2adb01111111a-28d0-2a5268794096",your_question))

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.

If you have any questions or comments feel free to send us a message at support@devicemagic.com.