I would like to generate a new Multiple Select field that contains values found in a series of Checkbox fields. Currently the Subject of each row is marked as a Checkbox. There can be one or more Subject values for each row. Can you help me write a formula that tests a series of Checkbox fields and generates values in a Multiple Select field that match the Name of the Checkbox fields? I have several groups of Checkbox values I would like to apply this formula to, in order to create a more compact Gallery display. Thanks for any help you can offer an Airtable Newbie
Generate Multiple Select values from a series of Checkbox fields
+21Welcome to the community, @Paul_Kahn! :grinning_face_with_big_eyes: A formula field can only affect its own output. It can’t control what appears in any other field. However, a formula could still get you part of the way, and then a copy-paste could do the rest.
The formula would look something like this:
IF({Field Name 1}, "Field Name 1,") &
IF({Field Name 2}, "Field Name 2,") &
IF({Field Name 3}, "Field Name 3,") &
...
You can use the formula field’s autocomplete feature to find the field names, but the output string for each will have to be typed manually. However, you could use autocomplete to add the field reference twice, then manually wrap quotes around the second instance of each pair, remove the curly braces, and add the comma after the name, like this (demonstrated with only two fields):
This formula will generate a comma-separated list of field names for each record based on the status of the associated checkbox fields; e.g. if you had the first and third fields checked for a given record, the formula would output:
Field Name 1,Field Name 3
Create a new multiple-select field and don’t give it any options. Select the field header of the formula field, press Ctrl/Cmd-C to copy its contents, then select the first record field in the multiple-select field and press Ctrl/Cmd-V to paste. Airtable will create new multiple-select options based on each item from the text. It won’t matter if there’s a trailing comma after the last field name in the formula; Airtable is smart enough to just ignore it and not create a null item in the list.
Welcome to the community, @Paul_Kahn! :grinning_face_with_big_eyes: A formula field can only affect its own output. It can’t control what appears in any other field. However, a formula could still get you part of the way, and then a copy-paste could do the rest.
The formula would look something like this:
IF({Field Name 1}, "Field Name 1,") &
IF({Field Name 2}, "Field Name 2,") &
IF({Field Name 3}, "Field Name 3,") &
...
You can use the formula field’s autocomplete feature to find the field names, but the output string for each will have to be typed manually. However, you could use autocomplete to add the field reference twice, then manually wrap quotes around the second instance of each pair, remove the curly braces, and add the comma after the name, like this (demonstrated with only two fields):
This formula will generate a comma-separated list of field names for each record based on the status of the associated checkbox fields; e.g. if you had the first and third fields checked for a given record, the formula would output:
Field Name 1,Field Name 3
Create a new multiple-select field and don’t give it any options. Select the field header of the formula field, press Ctrl/Cmd-C to copy its contents, then select the first record field in the multiple-select field and press Ctrl/Cmd-V to paste. Airtable will create new multiple-select options based on each item from the text. It won’t matter if there’s a trailing comma after the last field name in the formula; Airtable is smart enough to just ignore it and not create a null item in the list.
Justin,
Thank you for this great advice. It is a two step process, but consolidating information for a few thousand rows is worth both steps.
The only detail of your advice that did not work is “press Ctrl/Cmd-C to copy its contents”. I am working on a Mac and Ctrl-C and Ctrl-V does the copy/paste while adding the Ctrl/Cmd combination does nothing. Or perhaps I a misunderstanding your instruction, that you mean use either Ctrl or Cmd.
In any case, here is the successful formula that converts the checkbox fields into multi-select fields:
IF({Medical Magnitude},“Medical Magnitude,”)&
IF({Medical Supplies},“Medical Supplies,”)&
IF({Non-Medical Economic},“Non-Medical Economic,”)&
IF({Non-Medical Social},“Non-Medical Social,”)&
IF({Non-Medical Environmental},“Non-Medical Environmental,”)&
IF({Risk Historical},“Risk Historical,”)&
IF({Risk Future Model},“Risk Future Model,”)&
IF({Risk Flatten the Curve},“Risk Flatten the Curve,”)&
IF({Transmission and Infection},“Transmission and Infection,”)&
IF({Biomedical Research},“Biomedical Research,”)&
IF({Data Viz Advice},“Data Viz Advice,”)
Thanks again for your help
+21Justin,
Thank you for this great advice. It is a two step process, but consolidating information for a few thousand rows is worth both steps.
The only detail of your advice that did not work is “press Ctrl/Cmd-C to copy its contents”. I am working on a Mac and Ctrl-C and Ctrl-V does the copy/paste while adding the Ctrl/Cmd combination does nothing. Or perhaps I a misunderstanding your instruction, that you mean use either Ctrl or Cmd.
In any case, here is the successful formula that converts the checkbox fields into multi-select fields:
IF({Medical Magnitude},“Medical Magnitude,”)&
IF({Medical Supplies},“Medical Supplies,”)&
IF({Non-Medical Economic},“Non-Medical Economic,”)&
IF({Non-Medical Social},“Non-Medical Social,”)&
IF({Non-Medical Environmental},“Non-Medical Environmental,”)&
IF({Risk Historical},“Risk Historical,”)&
IF({Risk Future Model},“Risk Future Model,”)&
IF({Risk Flatten the Curve},“Risk Flatten the Curve,”)&
IF({Transmission and Infection},“Transmission and Infection,”)&
IF({Biomedical Research},“Biomedical Research,”)&
IF({Data Viz Advice},“Data Viz Advice,”)
Thanks again for your help
I meant to use either Ctrl or Cmd depending on your OS. Interesting that Ctrl-C and Ctrl-V worked on your Mac. I’m also on Mac, and the desktop Airtable app doesn’t recognize the Ctrl modifiers for copy and paste, nor does Chrome.
Login to the community
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.