Day 22: Lookups
Previous Topic  Next Topic 

In the last lesson we showed you how to use the Internet on your Handheld/Simulator. If you have not done that, go back and do it now before continuing with this lesson.

This lesson will show you how to use the LOOKUP question allowing you to define your own lookup tables from scratch. This allows you to store an infinite amount of data in the Data Doctor that can be summoned using Lookup type questions in your survey.


Now do the following.

    1. Create a new survey and SAVE it as Day 22. Amend question zero so that it has a question prompt of Name. Add a MENU question named Do you like red wine? with two choices, Yes and No. Set up the question to JUMP TO the END OF SECTION if No is selected. This should be done in the JUMP TO column next to the choice menu.

    1. Add a lookup question by selecting the LOOKUP tab in the question definition dialog. Give it a question prompt of Lookup Wine Countries.

    1. The EDIT TABLE button allows you to define (or edit) a lookup table from scratch. Enter Wine in the box named USING TABLE, then click EDIT TABLE to launch the Data Doctor.

    1. Lookup tables are defined in the Data Doctor, as it is capable of storing an infinite amount of data. As this table you have created is new, you will be prompted with the FIELD NAME EDITOR in the Data Doctor. Enter the field name Wine Country then click MODIFY. Now enter a field name of Wine Type and click INSERT. Add a third field name of Serving Temperature and then select DONE. You should now have three empty fields in your Wine lookup table.

    1. In this table you will be entering some key red wine countries, a few popular wines that originate from these countries as well as the recommended serving temperatures of these wines. You now need to add some data to your lookup table. Double click in the cell within the column WINE COUNTRY situated to the right of the diamond. A box should pop up allowing you to enter your data. Type Australia and click OK. Select the row so that it is highlighted and then copy and paste the row (CTRL + C | CTRL + V). Repeat this until you have four different rows with Australia entered in the WINE COUNTRY field.

    1. There are four different red wine types each with different serving temperatures (in *C) that you are going to associate with Australia. These are Cabernet Sauvignon at 17, Pinot Noir at 16, Shiraz at 18, and Zinfandel at 15. Enter this data into your lookup table.

    1. Now at the diamond in the WINE COUNTRY field enter France. Copy and paste this row until you have created five different rows for France. Enter the following wines and their serving temperatures for wines found in France:

Cabernet Sauvignon at 17

Merlot at 18

 “Pinot Meunier at 16

Pinot Noir at 16” 

 “Zinfandel at 15.

    1. Similarly, enter 5 rows for the WINE COUNTRY South America. Add Cabernet Sauvignon at 17, Malbec at 18, Merlot at 18, Pinot Noir at 16 and Syrah at 15. Click the field name heading WINE COUNTRY in the Data Doctor so that the lookup table is sorted by country. Click the SAVE button and return to your question Lookup Wine Countries in PocketSurvey. Set the FIND menu to all and Wine Country. Make sure the DATA DESTINATION FILE is set to NONE and then INSERT the question.

    1. Create a MENU question and give it a question prompt of Wine Country. Select VARIABLE from the LIST TYPE menu then select Lookup Wine Countries from the variable drop down menu. Make sure the DATA DESTINATION FILE is set to SINGLE ITEM then INSERT the question. The question Lookup Wine Countries will find all the different wine countries in the lookup table (make sure your lookup table is sorted on Wine Country). The question Wine Country is required to create a menu list of these variables.

    1. Create a LOOKUP question named Lookup Wine Type. Using the lookup table Wine FIND all the wine types by selecting all and Wine Type from the drop down boxes. MATCH Wine Country WITH Wine Country so that wine types are only displayed for the country you select in the question Wine Country. Click INSERT.

    1. Now add a MENU question named Wine Type and select VARIABLE from the LIST TYPE. Select the variable Lookup Wine Type from the drop down list and INSERT the question making sure the DATA DESTINATION FILE is set to SINGLE ITEM.

    1. Now we want to create a lookup question that finds and retrieves the serving temperature for each wine depending on country and wine type. Create a LOOKUP question named Serving Temperature. Using table Wine set the FIND menu to find the FIRST Serving Temperature matching Wine Country with Wine Country, and Wine Type with Wine Type. Check the Data destination file is set to single item and INSERT the question.

    1. Create a message question at the end of your survey entitled Preferred Red. In the TEXT TO DISPLAY box enter the following:

You prefer a Red {{Wine Type}} from {{Wine Country}}. This wine should be served at {{Serving Temperature}}*C.

SAVE and RUN your survey. When completed a message should pop up including your preferred wine type, country and what temperature this wine should be served at.

The next lesson will expand on the uses of lookup tables in PocketSurvey including how to create menu lookup questions.

HandHeld Systems Ltd ©