Understanding IF and SWITCH Formulas in Airtable | GAP Consulting

if you're struggling with formulas in air table you don't want to miss this video i'm going to be going into detail about the differences between if statements nested if statements and a switch formula so if that's of interest stick around and let's get into it hey i'm gareth pronovost i am the owner at gap consulting where we help you to organize and automate your business and life if that's of interest and you want to learn more about how we do that check out our website i'll include links below and don't miss our free airtable crash course it will help get you up to speed quickly with airtable software but that being said let's just jump into the heart of today's video and it is all about formulas so i'm going to be writing formulas here on the fly and sharing some of my pro tips specifically we are going to be looking at if statements and the switch statement these are two different types of formulas but they're often very similar in the way that we use them and the results that they produce so let's take a look i've got a really simple task list here nothing complicated i just want some you know fake data here so that i can run some you know formulas off of this so really i've just got the name of the different tasks and i've got a status here and let's say i also have like a complete check box because that's a pretty uh standard thing to include with any kind of task list right so inside of this then basically we're just saying hey this status can be one of three things this by the way is the default listing that comes anytime you start a new table from scratch so i just took the default that airtable provides for us and we're just going to run with that and so i want to write a formula that's looking at this status and is producing some kind of an output so maybe for example if the actually let's add some more complexity to here and i'm going to say the uh there's a date for these things and maybe there's a date due right so today that at the actual day and time of recording here it is uh october 8th so i'm going to go ahead and pull some of these that are before the 8th and then some of them that are after the 8th just so that we can run some scenarios around that date so an if statement would be uh as follows i'll just use if here and then we go into our formula we tell it that we want the field type of formula and first of course you know we should decide what we're going to write so maybe something like if the box is checked that the task is complete then we want to output completed and if it's not complete then we want to output incomplete right so this is a pretty simple if statement it uses what we call a boolean value so if we're looking at something and if it's true then yes this output will happen and if it's not true then no and then this other output will happen that is the basic syntax of an if statement in order to start writing an if statement just write if with an open and close parenthesis and inside of here once you click between those parentheses you'll notice up here that in this like yellowish box that there is a syntax guide for us if you're new to writing formulas pay attention to the syntax guide it is going to give you all the information you need to know in order to get your formula up to speed so you'll notice that the first part is a logical input then after the comma then we have value one and then another comma and value two so if you read the text here it says it's returning value one if the logical argument is true otherwise it returns value two so in this case there's an example if sales are greater than 50 then it's a win otherwise it's going to output lose so in our example same thing right we're just saying hey if that box is checked then completed and otherwise we'll say incomplete so that's fairly straightforward we are going to first reference that completed field that is right here so i'm saying if complete so if complete equals one or if complete then we are going to output completed otherwise we are going to output incomplete so this is how our syntax looks now quick side note we can add spaces in here if we want to it's not going to impact the way that the formula reads so if it's a little easier for you to visualize what is happening in here by spacing it out feel free to do so so really what we're saying is if this complete field is marked or if it is if it's checked then we're going to output completed otherwise incomplete and so if we save this formula you'll see obviously all of these are incomplete because nothing is yet checked we can see if the formula is actually working by making sure to check a couple boxes and of course we get this nice output so that's great but in this case you know not only do we have this complete checkbox but we also have a done status so there are occasions where you might say well maybe i didn't check the box for some reason but i changed the status to done and so filing taxes this task should be complete so now our statement is not sufficient the way we wrote it because essentially what we're saying is we have multiple conditions that could drive us to the fact that our task is complete it could be that the box is checked or it could be that the status is marked as done or maybe there are some other and so in this example we can build a nested if statement alternatively we could use an or statement inside of the logic function i'm going to highlight both of these for us and my first big tip for formula writing especially if you're newer to formulas or you don't feel super comfortable with them my biggest tip is break it up into pieces i've already written my if statement for checkbox and it's working perfectly so rather than trying to build on this and making things overly complicated all at once i find it very advantageous to break it up into smaller pieces so we've got our checkbox working great let's build the if statement for the status is done so we're gonna again choose the formula field type and in this case we're going to again use the if statement and once we're inside here we have to perform our logical value right or our logical statement in this case we're going to say if the status is equal to done quick pause you might notice i'm using single quotes here earlier i used double quotations to output single quotes or double quotes doesn't make a difference in air table the syntax is going to pick them up either way so long as you're consistent within that particular piece so if i used an open a single quote and then tried to close it with a double quote that breaks so you just be consistent within that so but in this case i'm saying hey i'm i'm checking this logical formula if the status is done then so comma what's our first thing going to be well then we're going to output completed and if it's not done then we're going to output incomplete right and so now we have a nice little formula that's working looking specifically at that status so taking this a step further we can then nest these if statements or comma alternatively we can write an or statement to get this uh working the way we intend so let's take a look at what the first alternative would be a nested if statement this is where we use two or more if statements inside of one another so we check multiple logical values depending on how it goes so in this case i will start with my first if statement i'm just going to copy that and i'm going to write a new one this will be my nested if i'm going to paste what i just copied right so again this is working fine for just that complete checkbox if complete is checked then output completed otherwise output incomplete and that's working just great so in order to add that next step i need to say well if complete is checked then i will output completed however if it's not checked then perhaps i need to check the status column to see if it's marked done because if it is then i still need to mark it as completed so prior to running this part of the of the formula we want to actually run another if statement and so what we're going to do is now bring in our if statement from the other one so i'm saying the way that logically this works the way the formula syntax works it says if complete is checked then i output completed otherwise if it's if it's not checked then i'm going to check if something else is true and then have an output dependent on that so this is what we call a nested if statement my nested if is going to be that status is equal to done so if that is true then again we want to output completed and if it's not incomplete now quick tip in order for us to output incomplete that would mean that both of these logic logical arguments have to be false that is if complete is false then we're going to not output completed here and then we're going to check on this second if statement if the status is equal to done then we're going to output completed but again if it's false if the second condition is false then we will output incomplete so if i go ahead and save this this is now a nested if statement that is checking first for the checked box and if the checkbox is not marked then it's checking to see the status if the status is done and if either of those conditions are met then it outputs completed and if they're not then it outputs incomplete now that is a nested if statement these can go on indefinitely you can nest many many if statements within one another and it can get very complicated so as i said breaking it up into pieces as i did here with first the check box and then the status is a pro tip that's going to help save you a lot of headache especially if you're still relatively uncomfortable writing more complicated formulas but let's talk about the switch formula and what is it and how is it different from if statements the switch formula is a little unique in that it looks at a particular field and it establishes a pattern now that sounds a little crazy but it's pretty straightforward again just as with the if statement if we write the formula out and then we observe the little yellowish box over here we get some tips and tricks on how to use this formula so in this case it's looking at an expression and then comma there's a pattern with a result dot dot dot default that might not make a ton of sense if you haven't used this formula a whole lot in the past but i'll show you the most common way that i see the switch formula used let's say we wanted to look at the status and if the status is to do then we want to output get this done and if the status is in progress then we want to output hurry up and complete it or something along those lines right if we were using if statements we'd have to nest multiple if statements and it would get quite complex but the switch formula helps us write this in a much more succinct way so i'm going to look at the status remember that's my expression so in this case the first variable that i'm inputting here is my expression so i'm saying look at status and if the status is so i put a comma and now i'm saying to do so if status is to do i want to output this needs to get done again these are in quotes right because i'm looking at text now the next part is if it's in progress whoops and i better remember what i just said and put it in quotes if it's in progress then i need to say hurry up and finish and lastly if it is done then i want to output great work and one other thing that i do want to point out in this syntax bar over here or in this little syntax helper there is this square bracket with default here i'm not going to use a default just yet and i want to come back to exactly what that square bracket means essentially though it means that this is optional so this thing this square bracket is telling us this could go on for as long as you want it to and this square bracket is telling us that there is the option of putting a default variable or a default output in this formula let's let's go ahead and set this up though and what we're going to find is that now this formula is outputting whatever we told it to output inside of here based on the status that we've picked right so each status is associated with a certain output so again the syntax here and this is the most common use case that we see for switch is we're going to look at the status and then if one of if the output is to do in status then we're going to output this and if the input i said output earlier if the input is in progress for status then output this and if the input is done then output this now let's talk about a default we might have the possibility that a record is created without a status and so we want to have something labeled here that alerts us no status selected so this is the default and again it is optional so i'm going to go ahead and save this up and if i create a new record without a status you see that that instantly is reflected there so hopefully that helps you know understand helps you to understand what the difference is between the if and the switch statement is and also wrap your head around how to put together a nested if any questions you do have please post them below look forward to hearing from you and i'll try to get to answering those as quickly as i can as always i hope you found that to be very helpful if you did and you'd like to learn more swing on by our website and check out all the resources we've put together we have a free airtable crash course that will get you up to speed quickly and easily in air table and we also offer some paid services including hourly consultations with our experts we have some online group coaching programs and courses and for the very advanced needs we can build a bespoke project for you from scratch so swing on by and i look forward to connecting with you soon

Channel: GAP Consulting

Views: 6,712

Keywords: airtable, gareth pronovost, airtable training, airtable consultant, airtable consulting, airtable demo, learn how to build automation, if statement in airtable, nested if statement in airtable, switch formula in airtable, switch vs if formula, airtable formula help, airtable formula tutorial, airtable formula examples, airtable if then formula, GAP Consulting

Id: DZpm1fZTuvQ

Length: 16min 10sec (970 seconds)

Published: Thu Oct 08 2020