This article accompanies this sample form. Please read the following and test the sample form. Either save a copy of the sample form in your Google Drive, or download as an Excel workbook. You can then upload it to your server, and follow along.
When exporting data directly from the SurveyCTO server console, or when using SurveyCTO Desktop while the Export select_multiple responses as series of 1/0 columns export option (see the SurveyCTO Desktop section below for detail), select_multiple fields get split into binary values for each choice option. In other words, there is a column for each choice in a select_multiple field, and its value in the submission row is a 1 when the choice was selected, or a 0 if the choice was not selected. There will also be a column for the default format of select_multiple fields, which is a space-separated value list of each choice that was selected (e.g. "1 2 4" if choices 1, 2, and 4 were chosen). However, when publishing data to downstream systems like Google Sheets, via Zapier or webhooks, binary values in standalone columns are not included by default.
| The exception here is data served by the API in JSON format, which does generate binary values. Visit the API documentation to learn more. |
Why do you want split binaries?
Data from select_multiple fields is easier to view and use when you split it into binaries, which can help both readability and usability when reviewing data.
The "crops" column in this screenshot contains each selected option in the same column, separated by a space.
The "crops" column here has been split into the binary fields "crops_apl", "crops_ban", "crops_car", and "crops_dat". There is a 1 if that crop is listed in the crops column, and 0 if it is not. This is also how data will look by default when exported from the server console, or when the setting is turned on in SurveyCTO Desktop.
Programming binaries into your form design for publishing
Data published from SurveyCTO does not benefit from export processing. So when you publish to Google Sheets or another external source, those 1/0 columns are not automatically created. To compensate, add to your form design a calculate field that generates a binary value for each choice list option. This is illustrated in this sample form (also linked above). The steps are:
- Create a calculate field.
- Name this field after one of the select_multiple choices.
- Have its value be an "if" statement, being '1' if the choice it is named after is selected, and '0' otherwise.
- For example:
if(selected(${crops}, 'apl'), 1, 0) - Save that field.
- Repeat these steps for each choice in the select_multiple field.
Splitting into binaries in an existing spreadsheet
If you have already published data to Google Sheets, or if you are working in Excel, you can create split binary fields using spreadsheet formulas. You can't retroactively program calculate fields into the design to generate binary values if you have already collected data (this would only benefit newly collected data with the updated form version). You can either program in those fields and re-export the data, or employ the method below.
See this working example spreadsheet with the formulas. The following steps can be used to add split binaries into a spreadsheet:
1. Create a column per choice list option
In the spreadsheet file (the example pictured here is from Google Spreadsheets, but this will also work in Excel), directly to the right of the column with the select_multiple data, add a column for each available choice. (In our example, there were four options in the select_multiple field, so there are four columns added.)
2. Give the new columns names
Add in a header for each of the new columns. These can be anything you want, but we recommend using the name of the select_multiple field, underscore, and the value of the choice.
3. Add the formula into the first row
Prepare the formulas to be used in each column. Each column will use a slightly different version of the same formula.
- Formula:
=ArrayFormula(if($A2:$A = "", "", if(iferror(search("value",$A2:$A,1),0) >= 1,1,0))) - Replace value with what is to be represented in that column (in our example: apl, ban, car, or dat)
-
=ArrayFormula(if($A2:$A = "", "", if(iferror(search("apl",$A2:$A,1),0) >= 1,1,0)))=ArrayFormula(if($A2:$A = "", "", if(iferror(search("3",$A2:$A,1),0) >= 1,1,0)))
Note: Excel formula syntax may be different from Google Sheets, depending on the separator value your Excel uses in formulas. You might need to use semicolons instead of commas.
Then, enter the formulas into row 2 of each column. Remember to press "Enter" or "Return" to finalize each formula once it is entered.
This formula will automatically fill in all of the cells below it as well, and you should be all set! Even if you send new data to the Google Sheet, the binary cells will update along with it.
If you would like to try this yourself, you can download the form here, setup to publish to Google Sheets, submit a few sample submissions, and then try to apply these steps. However, it is better just to add calculate fields to create split binaries in your form design in the first place, as described above.
SurveyCTO Desktop settings
To get split binary values in exported data from a select_multiple fields when using SurveyCTO Desktop, do the following:
- Click on Workspace settings.
- Click on EXPORT OPTIONS.
- Check the box for the Export select_multiple responses as series of 1/0 columns? option.
Do you have thoughts on this support article? We'd love to hear them! Feel free to fill out this feedback form.
0 Comments