Convert multiple text field columns into one multiple-select column

[Tommasina_Mille]

I’m converting a table that has multiple text field columns into one multiple-select column. The old form had text input fields so the most of the data is like this: and now that form has condensed those fields into one multiple-select field, like this: . I don’t want to keep the old text columns around, and want to collate those fields into the single existing multiple-select column.

I think the way to do this is in a step-wise fashion:

1. create a lookup table that converts the text into the new multiple-select labels
2. use a rollup field on the main table to concatenate or arrayjoin the data into one comma-delineated string,
3. copy that data over into the existing multiple-select column
4. delete the old columns and the reference table

But I’m not sure what aggregate formula I can use to create the string. And I’m not sure how to reference the data within the rollup formula field: (I know this isn’t right…)

Any help would be much appreciated! We’re about to add a lot more organizations to this use case so the sooner I can get this squared away, the better. Thanks!

[Justin_Barrett · Accepted solution]

Welcome to the community, @Tommasina_Miller! :grinning_face_with_big_eyes: The process is actually easier than your 4-step outline. There’s no need to use a temporary lookup table. All you need is a Formula field to create the comma-delineated string, which you can then copy to your existing Multiple Select field.

Here’s an example of how that formula would be built. I’ve split it across multiple lines to make editing easier:

IF(Gloves = "yes", "Gloves,") &
IF(Sanitizer = "yes", "Sanitizer,") &
IF(Thermometer = "yes", "Thermometer,") &
IF(Thermometer_Cover = "yes", "Thermometer Cover,") &
...

Obviously you’ll match the output text exactly to your existing Multiple Select options.

One thing to be aware of is a situation like your Masks entry, where there is a comma in the item text. For something like that, the appropriate part of the formula would look like this:

...
IF(Masks = "yes", '"Masks (if medical-grade required, specify so),"')
...

You need to wrap any comma-containing text inside quotes. Because Airtable supports both single and double quotes, use single quotes around a double-quoted string to create such items.

Once you’re done, your Formula field should have the comma-separated string you need to copy into your Multiple Select field.

[Tommasina_Mille]

Thanks for the welcome @Justin_Barrett and for the swell solution! That works great. I didn’t realize the ampersand concats the string. That’s perfect.

Justin_Barrett:

‘“Masks (if medical-grade required, specify so),”’)

^ That worked perfectly - I was worried about the double-quotes messing up with the existing one, but Airtable knew they were the same.