Skip to main content

I have a form for Licensure to get submitted, but I am wanting the person submitting to see the Description of the Code that is chosen.

I can't seem to get this to show how I want because I need on my database to have a column with ONLY the code to be passed over on the file I generate.  

Ultimately what I want is what you see below.  I want the "License Code - PS to be what is showing.  If we have someone in certain states I need it to change to a certain code.  Here is what it should look like (coming from step 3 below, but read why I don't seem to like this). 

 

I tried a few items listed below:

1. First, I wanted to use a formula in the Automations, but it won't allow this from what I could see.  I can choose

Static or Dynamic to update the record, but can't use a Formula here.

2. Second I then made the option on the form to be Code-Description and then tried to use the following formula which works great except I have some "Codes" that have a dash and I don't want it to remove from the code and only the Description portion.

TRIM(REGEX_EXTRACT({TRACK TITLE}, "[^-]*"))

3. I then tried to use a Lookup Table by linking to another table that would have Descriptions and the Code associated with.  This again seems to work except I don't want it to link back to each line on the Lookup Table.  I will have thousands of these being entered so I don't want to capture this on here to link.  

 

Any suggestions on what else I could try?

Hi @Aaron_Reynolds_,

It's a little bit difficult for me to follow what you're looking to achieve but here are a couple of ideas that might help:

You can use a SWITCH statement in your formula to modify the result based on another field like state.
It would look like this:
SWITCH({State},
'MA', 'some code',
'NY', 'some code',
'RI', 'some code',
'ME', 'some code'
)

You could also have States be stored in a separate table with the question on the form as a linked record field to the States table, then you can pull in values as lookups to dynamically construct your license code based on state.

As far as having dynamic options appear on a form based on a previous field filled out in the same form, that is not possible with Airtable's form builder (in case that's something you're asking about).

Another option is to have the description included in the value selected on the form and then use a formula field like a SWITCH statement to convert this answer into a smaller code (or instead of a formula, use a table + lookup field).

What are your current tables?

Hi @Aaron_Reynolds_,

It's a little bit difficult for me to follow what you're looking to achieve but here are a couple of ideas that might help:

You can use a SWITCH statement in your formula to modify the result based on another field like state.
It would look like this:
SWITCH({State},
'MA', 'some code',
'NY', 'some code',
'RI', 'some code',
'ME', 'some code'
)

You could also have States be stored in a separate table with the question on the form as a linked record field to the States table, then you can pull in values as lookups to dynamically construct your license code based on state.

As far as having dynamic options appear on a form based on a previous field filled out in the same form, that is not possible with Airtable's form builder (in case that's something you're asking about).

Another option is to have the description included in the value selected on the form and then use a formula field like a SWITCH statement to convert this answer into a smaller code (or instead of a formula, use a table + lookup field).

What are your current tables?


Thanks for the suggestion!  I was given something to try that bumped me through my rabbit hole!  I'll post what I came up with in a moment.

I was able to get what I needed in column "License Code - PS".

I used a combination of items (Automation & Formula)

- User enters from the choices in the "License Form" and state.  
- I first have an Automation setup when a condition of "License Form" and State are sent to look at three criteria (MI, IN, or all other states).  The Automation changes to RN-M (MI), RN-Other, or passes through the "License Form" name if IN. (This fills "Code AT" column).
- Finally I used the function below to trim off the description leaving me just the codes from "Code AT"

TRIM(REGEX_EXTRACT({Code AT}, "[^:]*"))
 
Seems to be working from my initial 3 samples from above.

 

Airtable doesn’t give the ability to show lookup fields on its forms, but there are a few workarounds for this:

  1. Instead of showing a lookup field on your form, you can make the primary field (in the linked record’s table) a formula field that contains whichever fields you want to see on the form. Then, when you choose the linked record field on the form, you will see that formula field.
     
  2. With Airtable’s new form builder (which you can get to by clicking on the “Forms” button at the very top of the screen), Airtable now lets you choose additional fields that you would like to display when selecting your linked record field.

    Note that Airtable only lets you display a few fields, and it often cuts off the length of those fields so you can’t read the data in its entirety. Also, Airtable disables this feature entirely if you’re using dynamic linked record filtering.
     
  3. For the best of all worlds, you can use Fillout’s advanced forms for Airtable because:

    a) Unlike Airtable’s forms, Fillout actually lets you add lookup fields to your form! To add these fields to your form, just and any text element onto your form and type the @ symbol. Then, you will get a whole bunch of additional options of what you can display on your form, including lookup fields
    b) Unlike Airtable’s forms, Fillout lets you display as many fields as you would like to display when selecting your linked record field. It never cuts off these fields, so you can always see the full text displayed.
    c) Unlike Airtable’s forms, Fillout still lets you display additional fields even if you’re using its ability to filter linked record fields.

    Even better, Fillout is 100% free, and it offers hundreds of features that Airtable’s native forms don’t offer, including the ability to update Airtable records using a form, create custom PDF files from a form submission, accept payments on forms, pre-fetch dynamic data from an Airtable record, create new linked records on a form, add a login page to your form, perform math or other live calculations on your forms, collect signatures on a form, create multi-page forms with conditional paths, connect a single form to dozens of external apps simultaneously, add CAPTCHAs to your form, and much more.

    I show how to use a few of the advanced features of Fillout on these 2 Airtable podcast episodes:
    Using Fillout to create an eSignature approval process with PDF file creation.
    Using Fillout to create an order entry form with line items.

Hope this helps!

If you’d like to hire the best Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

Terms & ConditionsAccessibility statement