fetch_cell and fetch_row

In the following example, suppose this sheet, named 'snacks', is loaded into the survey area.

We can reference the text in any cell and display it onscreen. The code below would display the contents of the 'type' column for row 5: 'salty'.

 [* fetch_cell( name => 'foodsurvey.snacks', row => '5', column => 'type' ) *]

You can also reference text dynamically based on a respondent's previous answer. In the following code, the row is determined by the respondent's answer at Q1. Imagine the respondent selected option 2, 'Apples'. The show text widget below would display "Apples are a sweet snack."

1. Select a snack:
type: radio
optsfrom: survey.snacks

showtext: SNACK_LABEL
text: $Q1_text are a [* fetch_cell( name => 'survey.snacks', row => $Q1, column => 'type' ) *] snack.

In the example below, the programmer utilizes a block to program several similar variables efficiently by looping through a series list from 1 to 8. For the first loop, the row value is set 1, and the column value is retrieved from column QTEXTQ49B. These variables can then be referenced as needed to drive other survey logic.

start group: 49Bgrp

block: <<END

GG_49B_PRICE_%%ID%%. value for $P1
type: text
invisible: y
cvalue: fetch_cell( name=> '12is8426.q49b_gg_pricing', row => %%ID%%, column => 'QTEXTQ49B' )
systemvar: y

END
freeform: y
list: series[1..8]

Researchers often use ZIP code lists to determine survey qualification. If a respondent qualifies, additional data, such as geographic details or project-specific information, may be linked to their record. The fetch_row function simplifies converting this information into dynamic survey data.

For example, consider a sheet named 'ziplist' loaded into the survey area (excerpt below; full sheet available for download here).

In the 'ziplist' sheet, each ZIP code is used as a row ID. Each row contains columns for 'dma_name', 'tier', 'group', 'tradearea', and 'dma_stores'.  This information is essential for the survey to function properly. The values from these columns correspond to survey options as follows:

• 'dma_name' is used for QDMA_NAME to bring in the appropriate text
• 'tier' maps to QTIER
• 'group' maps to QGROUP
• 'tradearea' maps to QTRADEAREA
• 'dma_stores' maps to QDMA_STORES.

ZIP. Please enter your ZIP code.
type: uszip5

TIER. Market Tier
type: coded single select
selectby: calculate
cvalue: our $zipdata = fetch_row( name => 'surveyIDhere.ziplist', row => $QZIP); return $zipdata -> {'tier'}
1. Tier 1
2. Tier 2
3. Tier 3
4. Tier 4

TIERQTA. QUOTA | Market Tier
type: quotas
optsfrom: QTIER

GROUP. Group
type: coded single select
selectby: calculate
cvalue: $zipdata -> {'group'}
1. Group 1
2. Group 2
3. Group 3
4. Group 4

TRADEAREA. Trade Area
type: coded single select
selectby: calculate
cvalue: $zipdata -> {'tradearea'}
1. Yes
2. No

setlist: DMALIST
1. Dallas, TX
2. Atlanta, GA
3. Augusta, GA
4. Richmond, VA

DMA_NAME. DMA Name
type: coded single select
optsfrom: DMALIST { if $zipdata -> {'dma_name'} eq '[text]' }

DMA_STORES. Number of stores in DMA
type: coded single select
selectby: calculate
cvalue: $zipdata -> {'dma_stores'}
optsfrom: series[0..10]
11. 11
14. 14
25. 25
37. 37

The example starts by identifying the respondent's tier after they input their ZIP code at QZIP. The fetch_row function is used within QTIER to create the $zipdata hash, which stores all relevant information for the entered ZIP code. The tier value retrieved from $zipdata is then used to determine the value of QTIER.

Since $zipdata is already defined, there's no need to call fetch_row again for other variables. Instead, we can repeatedly reference $zipdata to access any other column values for the current ZIP code row.

If a respondent enters a ZIP code that isn't on the list, QTIER will return a null value, leading to the respondent's termination at QTIERQTA.

fetch_row retrieves all data points from a specified row in a sheet, unlike fetch_cell, which gets a single data point. This allows you to reference the entire row's data later in the survey without repeating the fetch_cell syntax, making fetch_row efficient when accessing multiple columns from the same row.

The code shown below comes in two parts. QVAR1 represents the first variable in the survey that references the data from the row; it has the full fetch_row syntax. Then, QVAR2 represents a subsequent variable that uses abbreviated syntax to reference another column in that row.

VAR1. 1st Variable to collect 1st Restaurant Tier
type: text
invisible: y
cvalue: our $rowdata = fetch_row( name => 'restaurantstudy.zipmapping', row => $QREGION); return $rowdata -> {'column1'}


VAR2. 2nd Variable to collect 2nd Restaurant Tier
type: text
cvalue: $rowdata -> {'column2'}

The cvalue at QVAR1 contains two statements, separated by a semicolon:

• our $rowdata = fetch_row( name => 'restaurantstudy.zipmapping', row => $QREGION); — This creates a Perl hash variable rowdata containing all data from that row.
• return $rowdata -> {'column1'} — This assigns the value from column1 of the fetched row to QVAR1.

For QVAR2 and subsequent references to the row, we only need part of second statement to look up a particular column from that row:

• $rowdata -> {'column2'}.

This example includes the "secondary" key, which is needed to pull in a second-layer spreadsheet's value. See Relational spreadsheets for more information.

1. A variable interacting with relational spreadsheets
type: text
cvalue: our $var = fetch_row( name => 'survey_id.spreadsheet_name', row => 'row_id', secondary => 1); return $var -> {'column'}
invisible: y