Wanting to show Lookup on Airtable Form or Not to attach every record on Link Table

[Aaron_Reynolds_]

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?

[Stephen_Orr1]

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?

[Aaron_Reynolds_]

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.

[Aaron_Reynolds_ · Accepted solution]

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.