Skip to main content

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

H
Forum|alt.badge.img+1
  • hnr
  • New Participant

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.

 

2 replies

Marcelo12
Forum|alt.badge.img+6
  • Marcelo12
  • Inspiring
  • 23 replies
  • February 2, 2024

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

 

TheTimeSavingCo
Forum|alt.badge.img+28

As @Marcelo12 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:

Adam, Consultant @ thetimesaving.company

Reply


Most helpful members this week

Terms & Conditions