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.
Setting up your 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
Setting up your Calculated Questions
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.
Note: 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))
Other Useful Articles:
If you have any questions or comments feel free to send us a message at support@devicemagic.com.