Grid Indexing is the process of referring to a specific cell within a grid in order to use that value in your Advanced Expression Piping formula. This article will walk through how SurveyMonkey Apply will index the cell within the grid or column. This indexing will be used through the rest of the How To articles for the AEP functions.
- How to Set Up your Question Variable
- Indexing a Single Column Question
- Indexing a Multi-Column Question
- Indexing the Score of a Question
When setting up your advanced expression piping variable you will need to set up the Reference to the question that you are looking to use in the formula.
A question variable consists of two parts:
| + | The Index to the specific cell(s)/row/column of that question |
Where the format of the question variable could be either:
| identifier[index] | identifier.index |
Question Identifier
A question identifier is used to target a specific question within a form and is required for AEP. Question identifiers within a single form must be unique in order for the expression to reference the intended cell successfully.
Index
The index will always be a numerical value to determine the placement of the cell/row/column within the question. Indexing will always begin at 0 for the first cell, in the first row and column.
| Question 1 (q1) | Column 1 |
| Row 1 | [0] (Start here!) |
| Row 2 | [1] |
| Row 3 | [2] |
If you have set up a Single Column Question the questions numerical index will increment from top to bottom. There are three index options available to you with single-column grid questions:
In some cases, you may wish to reference a single cell in your table. For example, you only want to pull the response given in row 3.
Below is an illustration of the Index:
| Question 1 (q1) | Column 1 |
| Row 1 | [0] |
| Row 2 | [1] |
| Row 3 | [2] |
Again, the index always begins with [0] where the index of Row 1/ Column 1 = [0], Row 2/ Column 1 = [1], etc. Therefore, to reference the single cell in Row 3, our question variable will be q1[2].
Since there is only one column in this question. If you wish to total the entire question, you can just use the identifier, and this will automatically include all cells within the question.
In some cases, you may want to index more than just one cell, but not an entire column. Therefore, we allow the ability to index a range of cells at once.
In order to index a range our question variable will be:
| identifier[Lower:Upper] |
NOTE: The lower value (first value) is inclusive, therefore this is where the range will start. However, the upper value (second value) is exclusive, therefore the range will end with the cell prior.
| Question 1 (q1) |
Column 1 |
| Row 1 |
[0] |
| Row 2 |
[1] |
| Row 3 |
[2] |
Using the example above:
To index Row 1 and Row 2: q1[0:2]
To index Row 2 and Row 3: q1[1:3]
NOTE: Since the lower value is exclusive, we reference the last cell [2] + 1 = [3] to ensure all cells including the final cell is included in the range. If the index stated q1[1:2], the final cell placed at row 3 column 2 would not have been included in the range.
Indexing in a Multi-Column Question, is slightly different than a Single Column question, in that the index values increment from Left to Right, Top to Bottom. This section will walk through how to set up your indexing if using a Multi-Column question.
In some cases, you may wish to reference a single cell in your table for example you only want to pull the response given in Row 1/ Column 2. As mentioned previously, the indexing will always be a numerical value beginning at 0.
Below is an illustration of the Index:
| Question 2 (q2) | Column 1 | Column 2 |
Column 3 |
| Row 1 | [0] | [1] |
[2] |
| Row 2 | [3] | [4] |
[5] |
Where the index of Row 1/ Column 1 = [0], Row 1/ Column 2 = [1], Row 2/Column 3 = [3] etc.
Therefore, if we want to reference the single cell in Row 1/ Column 2, our Question Variable will be q2[1].
If we want to reference a single cell in Row 2/ Column 3, our Question Variable would be q2[5].
If we want to reference all values in a column, for example to find the SUM of the column, we can do so by simply indexing the column. The format for this is slightly different than a single cell and is formatted as:
| identifier[:columnindex] |
The colon (":") before the index in this case states that we are referencing the whole column. Again, the index will begin at [0].
This is very similar to Single-Column grids, however now we have multiple columns to reference from.
| Question 2 (q2) | Column 1 [:0] |
Column 2 [:1] |
Column 3 [:2] |
| Row 1 |
|
||
| Row 2 |
|
Therefore, if we want to reference the entire Column 2, our Question Variable will be q2[:1].
If we want to reference the values in a row, for example find the Average of the row, we can do so by indexing the row. The format for this is slightly different than a single cell and the column reference:
| identifier[rowindex:] |
The colon (":") after the index in this case states that we are referencing the whole row. Again, the index will begin at [0].
| Question 2 (q2) | Column 1 | Column 2 |
Column 3 |
| Row 1 [0:] |
|
||
| Row 2 [1:] |
|
Therefore, if we want to reference the entire Row 2, our Question Variable will be q2[1:]
In some cases, you may want to index more than just one cell, but not the entire table. Therefore, we allow the ability to index a range of cells at once.
In order to index a range our question variable will be:
| identifier[Lower:Upper] |
NOTE: The Lower value is inclusive therefore this is where the range will start, however the Upper value is exclusive, therefore the range will end with the cell prior.
| Question 2 (q2) | Column 1 | Column 2 |
Column 3 |
| Row 1 | [0] | [1] |
[2] |
| Row 2 | [3] | [4] |
[5] |
For this example, if we want to index Row 1/ Column 1 to Row 2/ Column 2, our question variable would be q2[0:5]
If we wish to index Row 1/Column 2 to Row 2/ Column 3, our question variable will be q2[1:6].
NOTE: As the indexing for a multi-column grid goes from left to right, top to bottom, it is only possible to index a range by cells. You will not be able to only index a range within a single column. For example, if there are 3 rows, you cannot index only Row 1/Column 2 and Row 2/Column 2 using range indexing.
Within ranking forms, you are able to add point values to form choices that are allocated to the total score of the application when the reviewer makes the selection. In the case that we need to later reference the score given to a particular question we can do so by using the following format:
| identifier.score |
The identifier will vary depending on the question that is selected and ".score" signals that we are trying to extract the score from that specific question.
Consider an example where 2 multiple choice questions have custom scores allocated to each choice within the question. These questions have been labelled with the identifiers q3 and q4 respectively. We can extract the scores given to the final review score of the application using the question variables:
q3.score
q4.score
This can come in extremely handy in the case where you are looking to pull the SUM or Average (AVG) of reviewer questions within a single form, or if you would like to display and/or report on scores from specific sections of a review form.