Help

Formula to auto populate based on selection in another field (multiple options equal same answer)

Topic Labels: Formulas
591 2
cancel
Showing results for 
Search instead for 
Did you mean: 
hnr
4 - Data Explorer
4 - Data Explorer

I'm using airtable to manage all my seasonal employee hiring. I have one column where from a multiple selection I choose their job title (eg. Camp Ops, Kitchen, or Waterfront). I want another column that show what certification are required based on the job title I selected. I believe this should be a IF or SWITCH formula.

Currently I have a working formula that is 

IF(Job='Camp Ops',"First Aid & CPR" )
 
But I can't figure out how to get the formula to also be IF(Job='Kitchen',"First Aid & CPR) and IF(Job='Waterfront',"Lifegaurding") and so on.
hnr_0-1706820230218.png

 

2 Replies 2
Marcelo
6 - Interface Innovator
6 - Interface Innovator

you have a lot of options in the select field, so you should use the SWITch formula.

here is the support link: https://support.airtable.com/docs/an-alternative-to-if-statements-using-switch

in your case you should code like this:

SWITCH(
{JOB},-------THIS IS YOUR SINGLE SELECT FILED NAME
'WATERFRONT','THIS REQUIERES TO BE SUPERMAN',
'COOKING','BEST CHEFF DEGREE',
[....],
'CHECK THE JOB LIST'-------(THIS LAST OPTION IS IN CASE THE FORMULA CAN'T FIND THE OPION IN THE LIST)
)---END

 

As @Marcelo said, a SWITCH is probably your best bet.  For future reference, you can also chain up your IF's with an '&' like so:

IF(Job='Kitchen',"First Aid & CPR) &
IF(Job='Waterfront',"Lifegaurding")

If you find that you have a lot of jobs and certifications and don't want to handle them all via formula field, perhaps you could also consider creating a "Jobs" table and putting every job and certification in there, then using a linked field with a lookup field like so:

Screenshot 2024-02-03 at 3.30.06 PM.png

Screenshot 2024-02-03 at 3.30.04 PM.png