I’m using Zapier to send survey responses from Alchemer to Airtable. To help with filtering in Airtable, I match each survey 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, either Airtable or Zapier (I’m not sure how to tell which) 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!
I also posted this issue in the Zapier community forums, and someone suggested the problem lies with Airtable and to try using Zapier’s Formatter tool to replace commas in the survey responses before they get piped into my Airtable base. My concern with this 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 checkbox questions in my survey, and instead use an open-text follow-up/conditional question that would allow people to type in their “other” response. By creating a separate question, I can then map it to a single line text field/column in Airtable that does comma separate. This might be more work for me and a less optimal survey experience, but could be a sturdier solution in the long run.