Understanding IF and SWITCH Formulas in Airtable | GAP Consulting

[@GarethPronovost]

Join us for our *FREE LIVE TRAINING* that teaches the building blocks of automation: www.garethpronovost.com/webinar-registration

[@0ooooooooa]

Best Airtable channel so far! Thank you Gareth

[@GarethPronovost]

So glad you like it!

[@JonathanShyman]

Yes! More formula videos PLEASE! Thanks!

[@GarethPronovost]

Awesome - will do! Thanks for the feedback!

[@angelprado293]

Excellent, I was looking for a Case Function or something like that and yo help me a lot, thanks

[@chrishicks2965]

I've been struggling with this. I'm running a time logging application specific for events. I have a Start/End/Break Start/Break End date fields. I'd like the Status to switch to different things (active/break/done for the day) based on info entering those fields (Start =active). I can write simple if statements, but nested they don't work. I think because the statements reference different fields? Anything you can tell me that I might be doing wrong?

[@GarethPronovost]

I'm sorry to hear you're struggling with this... Nested IF statements can be difficult! It should matter how many different fields you reference. My guess is you're suffering from a syntax error.

[@shaunparfett]

Thanks again Gareth for an informative video.

[@GarethPronovost]

My pleasure!

[@wilmermoreno9861]

Hello, I got a question, in this case, for my status I have different inputs, but I want the switch function to output and specific value for a set of different inputs: EX: I want for the outcome to be cancelled If the deal status is either cancelled, disqualified, expired and so on. I would appreciate the help here

[@sybdeclark8055]

Thank you so much for your videos. I am new to Airtable and they are very, very valuable to me. This one inspired me a question. In your example, could you use an if statement (or some other formula) to get the checkbox ticked automatically when the status is set to "done"? Generally speaking, can the output of an if statement be something else than text, in particular some kind of action (like click checkbox in this example)? Thanks in advance!

[@GarethPronovost]

Hi, great question! Yes, the output can be something other than text, so you can use a ✅ emoji. However, you can't change the type of the data - so it must remain as a formula field (not an actual checkbox field). That said, you CAN build an automation that checks a box when the status is updated. Check out tomorrow's video for an example on that!

[@leetal1]

HI Garath, thank you for the video, it is super informative, however there is something I'm missing. When you explained the IF() formula you were able to use a nested statement to include both the status and checkbox fields - meaning all possible options are covered and the output of the formula is as accurate as it can be. on the other hand, with the SWITCH() formula, you only used the status field in the statement you wrote (which might be a cleaner way of writing a formula but doesn't give the same "coverage" and accuracy.) Is there a way to use a SWITCH() formula that takes into account both the status and checkbox fields? Thanks!

[@GarethPronovost]

Hi! You can definitely use the SWITCH() formula to include all possible options, provided that the first parameter in your SWITCH() includes all of your possible outcomes. The first parameter will always be the expression that you are evaluating, so all of your logic must be built in that expression if you want to use the SWITCH formula alone. Otherwise, you may need to include nested IF() formulas 🤘

[@itseveryonelese]

Very cool! Loving the switch formula!!!!

[@GarethPronovost]

So much easier than a nested if, right?

[@wisdomofaclown]

Very new to AirTable. Loving it so far and really enjoying your videos. I’ve already learned a lot. One thing I’ve been trying to figure out. My wife is starting online reselling business. Some items are things we buy and some are things we sell for others and pay them a % of the sales. I have a column for the % going to the supplier and one for our profit after all other amounts are deducted if the item is supplied by someone else. Is there a way to do an if statement or some other formula that says if the single select is a specific name (my wife) to add the amount in the “paid to supplier” and the “profit” column for that row into a “final profit” column? Thanks so much. These videos and the way you explain this has really been helpful.

[@GarethPronovost]

Hey Josh - absolutely! Just use an IF formula that says: IF ( {***} = "Wife's Name" , do this math , do other math ) Just change *** to your single select field, add your Wife's Name, and then put in your mathematical outputs!

[@wisdomofaclown]

Thanks so much. It works now. I had the logic straight but messed up the way it was written. Wasn’t sure about the ( ) and { }

[@GarethPronovost]

@@wisdomofaclown Awesome!

[@williamho161]

Thank you Boss! Always looking forward to your new video!!

[@GarethPronovost]

Thanks William!

[@clairebarnett2023]

Hi Gareth - Just started using Airtable and it's changed my workflow dramatically - Your videos are such a massive help. I'm struggling with a formula for past date due "OVERDUE" but if marked as paid "PAID". I have tried sooo many connotations (over 4 hours today 😭) of the formula but it's just not working - can you point me in the right direction or can I employ your help?

[@GarethPronovost]

Hi Claire - we do offer hourly consultations on our website, but this is a simple thing that shouldn't require that much time. In plain English, if your past due date is less than today it is in the past. If this is true AND the item is not marked "Paid" then the status is "OVERDUE" - if it IS marked "Paid" then it's simply "Paid". Assuming that PAID is a checkbox field, you can do the following: IF( {Paid} , 'Paid" , IF ( {Past Due Date}

[@clairebarnett4587]

@@GarethPronovost Thank you for this Gareth 😊 - however, it isn't working (I've tried so many connotations of this!) - I've no idea why and it's driving me mad 😭 I may be in touch soon via your website!

[@clairebarnett4587]

Took some time but ended up with hugely nested formula based on totals for returned values in extra columns! IF( {CalcTotal} = 4, 'PAID', IF( {CalcTotal} = 3, 'PAID', IF( {CalcTotal} = 2, 'OVERDUE', IF( {CalcTotal} = 1, 'DUE', ' ' ) ) )) 😳

[@bill.scully]

Hi Garett, Another great video! I see you selecting the field types and formula starting text super fast. I’m guessing there are short cuts. IS there a video on that? If not you should do one. - Bill

[@GarethPronovost]

Hey Bill - you're a mind reader! Doing a video on shortcuts is coming up very soon!

[@arielsigler948]

Very Nice ! - I love how you explain things. Gareth , in airtable community I was introduced to "integromat" . Looks like a really cool tool that puts Zapier in the Shade (sorry to say) Since it automates also when a record is changed and not only when being created . I wanted to point out your attention to this tool. I think you will love it. ( I don't have any business connection with the company and I am not familiar with the developers)

[@GarethPronovost]

Hi Ariel, yes Integromat is also an incredible automation tool. I preferred Zapier for the past few years because it integrated with more software than Integromat. I want to use as few tools as possible, and since Zapier had more "connectivity" it was the optimal choice for me. However, Integromat is really starting to catch up!

[@HunterReynolds]

Thanks for teaching me about Switch()!

[@GarethPronovost]

Happy to help!

[@Smartstartup1]

I would put 2 likes😉

[@GarethPronovost]

Thanks for watching! 🙌