Help

Re: Automation: Allow users to add new options to a predefined list via a form

8246 3
cancel
Showing results for 
Search instead for 
Did you mean: 

A question arose recently that’s come up a few times in the past: is it possible to give form users the ability to either pick an item from a predefined list or add their own “other” option, and have manually-added options actually appear in the list for later form users? For most of Airtable’s existence so far, this wasn’t possible (at least not without delving into the API, and even then I’m not completely sure that was doable). However, it can be done using Airtable’s automation feature. Here are two methods you can use:

Method 1: Single Select Field

Most of the time this question comes up, the user is probably thinking of using a single select field. While this will work, it comes with a couple caveats that actually make the second method preferable IMO. That said, some of the details that I share here will also be used in the second method, so I still recommend reading through this one.

First, define your single select options as you wish, with an option named something like “Other” as the last entry.

Screen Shot 2021-03-20 at 8.51.02 PM

Add a single line text field named something like “Other option”.

In your form setup, put this “Other option” field after your single select field, and make it conditional, only displaying when the user picks “Other” from the preceding list.

Screen Shot 2021-03-20 at 8.38.51 PM

Create a new automation using the “When record matches conditions” trigger, with the condition being that {Other option} is not empty. Add an “Update record” action, choosing the same record that triggered the automation, and setting the value of your single select field to the value added by the user in the {Other option} field.

Screen Shot 2021-03-20 at 8.43.29 PM

Turn on the automation, and it’s done. When a user picks “Other” from the single select field, the {Other Option} field will become visible, allowing them to enter their own choice.

other option 1

When the record saves, the automation will add that new item to the single select.

Screen Shot 2021-03-20 at 8.50.43 PM

So what about those caveats?

  1. All new entries will appear after the “Other” entry in the list. The only way to change this order is to manually move “Other” back to the bottom of the list on a regular basis. Kinda cumbersome.
  2. Every manual entry will automatically add to the original list. If a form user decides to be malicious, their new entry will appear for others to see until it’s manually removed.

That’s why I prefer…

Method 2: Linked Records

The setup is similar to what’s above, but with a few changes.

Instead of creating a single select field, make a new table to contain your list options (I’ll call mine [Options] for consistency), including an “Other” option. To ensure that “Other” remains at the bottom of the list, add an {Order} formula field using this formula:

IF(Name = "Other", 9999, 0)

Then sort the view by this {Order} field.

Next add an “Approved” checkbox field and check this box for all of your initial options. Change the view name to “Approved”, and add a filter to only show approved items.

Now the view should look something like this:

Screen Shot 2021-03-20 at 9.07.19 PM

Duplicate this view and name the new view “Unapproved.” Modify the visible fields to only show the {Approved} checkbox field. Change the filter to only show records where {Approved} is not checked. For now, it’ll be an empty view, which is fine.

Back in your main table, add an “Options” field (or whatever name you wish to use) and make it a link to your new [Options] table, only allowing individual records to be selected.

In the form setup, set {Options} to only allow selection of records from the “Approved” view.

Screen Shot 2021-03-20 at 9.15.48 PM

As with the first method, set the “Other option” field to be conditional, only appearing when {Options} contains “Other”.

Screen Shot 2021-03-20 at 9.17.24 PM

For the automation, begin with the same trigger as in the sample above. The first action in this setup, though, will be to create a new record to the [Options] table, with the record’s primary field being the value entered by the user into “Other option”:

Screen Shot 2021-03-20 at 9.21.00 PM

Now add an “Update record” action. In this case, add a link to the newly-created record into the {Options} link field.

Screen Shot 2021-03-20 at 9.24.16 PM

Save and activate the automation, and you’re all set.

There are several benefits to this method over the first one…

  1. Any manually-created “Other” entries must be approved before they will appear for other form users. You can easily review and approve new entries in the “Unapproved” view on the [Options] table. (You could, of course, omit the approval features and just let all new entries auto-add like the above scenario, but I personally prefer to use the approval options.)
  2. The “Other” entry will always remain at the bottom of the list thanks to the formula field that’s driving the view’s sort.
  3. Because you’re using linked records instead of a single select field, you can add a “Count” field to the [Options] table to count the linked records tied to each option, allowing you can see how many times each option has been chosen.

I hope that these tips are helpful!

12 Replies 12

This is awesome, @Justin_Barrett!! Thank you for sharing this with the community! :grinning_face_with_big_eyes: :raised_hands:

Great post @Justin_Barrett !

Burner
7 - App Architect
7 - App Architect

What a brilliant solution. Thank you @Justin_Barrett for this amazing tip. Can’t wait to put this to use everywhere. :grinning_face_with_smiling_eyes:

Chad_Kukahiko
5 - Automation Enthusiast
5 - Automation Enthusiast

I’m trying to translate this for a Multiple Select instead of Single Select and can’t figure it out. Is that even possible?

It should be doable. Are you able to share specific details about how you’ve tried to implement it and the problems that you’re encountering? Screenshots (even doctored to hide private data) would be immensely helpful.

Chad_Kukahiko
5 - Automation Enthusiast
5 - Automation Enthusiast

Stumbled onto this bit of code from a previous poster (made some customizations) …

const 
    targetField = base.getTable('Businesses')
        .getField('Business Type'),
    tfChoices = targetField.options.choices,
    name = 'Option';

if(!tfChoices.map(choice => choice.name).includes(name))
    await targetField.updateOptionsAsync(
        {choices:[...tfChoices,{name}]}
        );

… but all it’s doing is creating a new option named “Option” if it doesn’t already exist. I know I might be a bit over my head, but I don’t know how to use variables so that the ‘name’ field works properly.

I’ve also tried your first option a few different times, but I keep getting stuck here.
Screen Shot 2022-01-18 at 11.47.34 AM
It won’t let me run the test and it doesn’t work. I can’t figure out what I’m doing wrong.

It also seems like in a Multiple Select situation that automation would need to be added to de-select “Other”. I’m also trying to figure out how to do that and can’t figure that out either.

I don’t have time to dig any deeper right now, but I’ll bookmark this for tomorrow. However, I can quickly clarify one point:

You can only run a test on a given action if you have run a successful test on the previous action. That’s required because each action passes data to the action(s) after it. Without running tests on prior actions, later actions don’t have access to that passed data and often can’t be configured because it’s missing.

Jason_C
4 - Data Explorer
4 - Data Explorer

Hey, I would love to apply this to my base, but I’m not totally sure how to apply it to what I have set up. Basically I have a table for “Companies” and a table for “Contacts,” And I have it so that the Companies table has a column that links to contacts table, and vice versa. I have a form I’m creating to make it easier to input new companies, but as I’m sure you are aware, it doesn’t let me create a new linked contact.

These table are already populated with information, and I’m pretty new to the airtable world, so I’m not totally sure how to apply your solution in my scenario. I also want to leave it so multiple selections can still be made.

Any wisdom or guidance would be greatly appreciated!

Welcome to the community, @Jason_C! :grinning_face_with_big_eyes: Method 2 listed above should be what you need. You’re either using one of the existing linked records, or using the “Other” field to create a new record that will become a linked record once the automation runs after form submission. If you want to allow both existing records plus one new option in the “Other” field, that might still work, but I haven’t tested that option (and unfortunately don’t have time to test right now). Here’s the theory I’m thinking about:

  • In the target table—I’m guessing this is [Contacts] in your case, the table that you want to link to with your “Other” field entry—add a formula field that outputs the record ID using the RECORD_ID() function.
  • In the primary ([Companies]) table, add a rollup field that rolls up the record IDs of all linked records, using the following aggregation formula: ARRAYJOIN(values, ",") . I’ll call this field {Linked Record IDs}
  • In the “Update record” action of the automation setup, you’ll insert two items from previous actions into the link field (named “Options” in the example above; change as appropriate for your use case).
    1. First you’ll add the {Linked Record IDs} field
    2. Type a comma immediately after that insertion
    3. Finish by adding the record ID of the newly-created record in the previous step.

That last step will create a comma-separated list of record IDs to feed into that link field: the IDs of the link(s) that you chose in the form, followed by the ID of the new record created by the automation from your “Other” field entry. If you don’t choose any linked records and only add something via the “Other” field, it will still work (that part I have tested previously).

ActivEight_acti
4 - Data Explorer
4 - Data Explorer

@Justin_Barrett. Thank you so much. This is the most accurate answer i’ve gotten. Though it looks like airtable has been updated since this response and I keep getting stuck with both methods. Is there a way someone can share a video of the steps to the non techie folks? Thanks :grinning:

NoahSmit
4 - Data Explorer
4 - Data Explorer

Now its 2022 it doesn't work more like that can you do in the new version