Another configuration for the Sheetgo Forms is subquestions. This triggers an additional set of questions once a previous question has been answered in a specific way.
How to configure subquestions
As an example, below, you can see that when I select my order item as a “drink”, it triggers a new set of questions to specify the type of drink “coke, water, ginger ale…”, which then triggers another set of questions about the size of the drink “bottle, can…”.
To set this configuration up, you will need a spreadsheet with 2 tabs:
Contains the form questions as explained in the How do I create a new form in this guide.
Containing the parameters our form will use to populate the subquestions
Let’s use the restaurant example to show how to make use of this feature.
Add primary question to main forms sheet (tab 1)
In the main forms tab, create a new column for your primary question, adding the field title to the header.
You need to format your primary question with data validation, adding the list of values.
In the screenshot above, our primary question is the “Order Item Type”, with data validation that includes all potential values such as “drinks”, “sandwich”, “burger” and “dessert”.
This will create the usual list of questions in your form. Now it’s time to configure the subquestions in the second parameters tab.
Set up the subquestions in the parameters sheet (tab 2)
The parameters sheet should list all of the potential combinations of values of your primary question and subquestion(s).
Create a column for your primary question and subquestion, and list all of the potential combinations as new rows. For example, if a user selects “drinks” from the primary question, list all of the different “drinks” values connected to this, e.g., “coke”, “water”, “ginger ale” etc. Repeat for each value from your primary question.
Your sheet should look something like this:
It’s possible to continue adding columns for additional subquestions, which makes it easier to filter this table by item and sub-item
Please note: it’s extremely important that you list down every possible combination of values as new rows in your second tab. If not done correctly, you may experience issues when users fill out your form.
You can also opt to create a new table for each set of parameters rather than one long table as shown above (e.g. a table for “drinks” values, a table for “dessert” values, etc.). However, this will affect the way you configure your form questions in the next step.
Adjust the main forms sheet (tab 1)
Now that your parameters are listed in your second tab, it’s time to return to the main forms tab to apply the configurations for the subquestion behavior. Below is an example of what your main forms tab should end up looking like:
The “Order Item Type” above is the column you created in the first step. This should contain the field title and should be formatted with data validation. You can also add configurations to adjust the way it is presented in the form.
Now it’s time to create the next set of columns for your subquestions. For this, you’ll need to use the following order of configurations:
Use conditional configuration to set up the relationship with the primary question column (in this example, the “Order Item Type” column).
It should look like this in our current example: [conditional: “Order Item Type”]. Please remember this depends on the name you’ve given to your previous question.Next, you must link to the parameters sheet and the range of cells that directly link to your primary question. In this example, I need to link the range of cells in the “Type” and “Item” columns in my parameter tab, to the “Order Item Type” - “Parameters!A2:B100”. This is similar to how you’d build a vlookup.
At this step, the configuration will look like this so far: [conditional: “Order Item Type”, range: “Parameters!A2:B100”]You can also set up the field to accept any text response by adding the command text to the end of the header. It will then look like this: [conditional: "Order Item Type", range: "Parameters!A2:B100", text]
This allows you to input any text item that you wish but in case it matches one of the previously listed items, it also accepts that input and shows the new option as expected.
Repeat the steps for however many sub-questions you want or need to use on your form.
Once you have carried out the steps above, your form spreadsheet is now ready to use. Using the example above, whenever a user selects an item in the “Order Item Type” field, their specific answers will trigger a new sub-question field where they can continue to select the following answer.
How to use Auto-fill
Another powerful feature of the subquestions is the auto-fill function, which allows a new answer to be automatically completed based on the previous one.
To configure this on your form, we need to use the configuration vlookup and two others based on a conditional field, key, and range.
This is used to indicate another field that will provide the value to be searched for. The value of the key configuration will be the exact same name as this other field. Range, on the other hand, is used to indicate the data. The first column must have the searched value and the following columns must have values related to this one. Here is an example of how the configuration looks on your spreadsheet.
Using the above example, a pre-filled field can be configured to [default:vlookup, key: "User id", range:"Auto fill parameters!A2:B"], where an existing field called User id will have options ranging from 1 to 6. When this field changes, our pre-filled field will change its default value based on the previous value. Here is a look at this system in action:
Additionally, an index configuration can be added to indicate which column of the range will have the values which will be displayed in the field. This follows a notation of 1 to many.
In the above example, if the range was "Auto fill parameters!A2:C", including the column Color, and values of column Name should be displayed, adding index:2 would achieve this goal. If the index is not indicated, the last column of the given range will be used.
Need more help?
Contact us via chat, on our email ([email protected]), or send us a ticket!
Visit our Community for help, tips, tricks, and feedback for Sheetgo.