Best answer

Airtable splitting multi select field responses on the comma


I’m using Zapier to send survey responses from Alchemer to Airtable. To help with filtering in Airtable, I match each question type to the corresponding field/column type in Airtable; so a likert scale (or other radio button question) is mapped to a single-select field, a checkbox question is mapped to a multi-select field/column, and open-text questions are mapped to single/long text field/column types. 

My survey makes heavy use of ‘Other - write in’ fields, especially in the context of multi-select (checkbox) questions. Commas are often included in these write-in fields, either by survey respondents or by Alchemer in how it formats data. However, Zapier appears to be seeing the commas and using them to break apart individual responses into smaller pieces that then get entered into Airtable incorrectly. 

For example, if someone writes in “Planes, trains, and automobiles” into the “Other - write in” option of a checkbox question, instead of the response “Other - write in, Planes, trains, and automobiles” getting piped into Airtable as a complete, single response, it instead gets piped in as “Other - write in” “Planes” “trains” “and automobiles” all as different individual entries within the same cell.

As you can imagine, breaking apart the responses like this is causing a lot of problems in being able to read and filter responses in Airtable, and is creating a bloated list of options within a given multi-select field.

Desperate to see if anyone has any ideas on how to fix this, so thank you all in advance for any insights into the problem!

icon

Best answer by Troy Tessalone 7 June 2021, 20:10

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

6 replies

Userlevel 7
Badge +13

Hi @kjones 

To clarify, it’s Airtable that is interpreting the commas in the data and separating the values for select/multi-select fields.

Try using a Formatter > Text > Replace step to convert the commas to semi-colons ; or a different delimiter character.

Hi Troy-- Thanks for the quick response! I will try that, but my concern is that method would also interfere with the actual multi-select options as those may be also separated by commas in Alchemer’s API. Will have to test and see.

Another idea I thought of is to remove the write-in field (but keep the response option) from the multiple choice question, and instead use an open-text conditional question that would allow people to type in their “other” response. By creating a separate question, I can map it to an open text field/column in Airtable that does comma separate. This might be more work and a less optimal survey experience, but could be a sturdier solution in the long run.

Userlevel 7
Badge +13

@kjones 

With the Formatter action step in the Zap you can choose which data points from the trigger step to convert.

Userlevel 1
Badge +3

Hi @kjones, were you able to sort out the issue with the Airtable comma separated data? Could you share how you resolved the issue with the Community? 😀

Thanks for the reminder @Paolo

I tried Troy’s suggestion, but honestly could not get the Formatter to do what Troy suggested. I had never used it before and didn’t have time to figure it out, so I went with what I ultimately thought would be a more robust/safer approach by changing my survey directly. I ended up doing what I described in my previous comment -- breaking out the “other” responses into separate open-text questions rather than using the “write-in” field within a question. Doing this actually made it cleaner to view and easier to filter by in Airtable as well. 

Userlevel 7
Badge +8

Thanks for updating the thread with the solution that worked best for you, @kjones! I’m glad you were able to find a work-around that suited your needs. 🤗