Using AND OR Formulas in Airtable

if you're looking for help with and slash or formulas you've come to the right place in this video i'm going to be going into detail about these tricky formulas and exactly how to use them in your airtable database so if that's of interest stick around let's get into it hey my name is 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 check out our website i'll include links below and take our free airtable crash course it's going to get you up to speed quickly and easily in airtable but without further ado let's hop into the database here on my screen and you can see that we've just got a really simple like table for people right and i have a full name right just checking out some uh some people's names here then we have the status field and we've got a couple of different statuses that are available to us right the idea here is that we're kind of tracking people maybe through some sort of a crm system okay and then we're collecting email now of course your your people database is probably going to be much more complex than this but i needed a few different data points for us to play with in order to put together some formulas so the first thing to know about an and or an or formula they both kind of are similar so we're going to talk about both of them from a high level the and formula and the or formula will always output either a true or a false value so really what you're saying is are these conditions all you know in in the case of an ant formula are they all in existence and if the answer is yes then you get a yes and if it's not then you get a no or a true and a false or a one or a zero right it's a boolean answer same thing with the or formula it's basically saying i'm going to list a lot of different criteria in here two or more criteria if one of these things is true then output one or output true or output yes and if none of them are true then output the opposite right so really what we're talking about here they're they're very similar formulas but the and formula is going to output in the case where all of the conditions inside of it are true the or formula is going to output when one or more of those conditions are true so let's take a look and see what we have here let's put together an and formula and let's talk about some possible uh criteria that we're going to say let's say for example that we want to check that uh status is client right we have a various number of status here statuses let's say we only want to bring back if they are a client and if they have an email address in our system so this would be i'm gonna you know write the conditions up here in top on top with the formula so client status and uh existing email so in our formula we're going to write the and formula open close parenthesis this is standard for every formula you put together an air table and now if you're new to formulas when you go inside of those parentheses you can see the syntax that you're supposed to follow in order to make this formula do its thing and the only required condition is the first logic the first logical condition the one that does not have brackets anything with brackets is an optional uh an optional uh parameter that you can add to here and you notice then you get these three ellipse here the ellipse and this is basically saying this can go indefinitely you can put as many different logical conditions scrunched together in this and statement as you want so in our case we have those two examples right we are saying the client status uh or the status equals client and the exist and the email exists so how do we do that we say and so we're checking for status and this is a field is equal to and since this is text we need to contain it within quotations so i'm so this little first part is just saying the status is equal to client comma and the and is implied because it's already a part of this formula and then the second part we're checking for is that email exists and so to see that it exists you just need to say and email so the comma here is separating the different parameters that we want to check for to make sure that they are all true at the same time so when i save this formula you'll see that we get either a one or a zero as an output and this just means true or false right yes or no either the conditions are met or they are not met so of course we only have one example in our three records here that meets these two criteria if i change one of the statuses of a client or one of these statuses to client that formula will update for those particular clients and also if we remove an email for one of our clients then suddenly we don't have an email so because both of these things are not both true at the same time it changed to a zero so that's how the and statement works now taking that to the next level or rather playing with a different formula we could write a similar formula for the or statement so we could say well client status or client is equal status or there is an existing email and the syntax for this is exactly the same except for the fact that we're using or right so the formula is no longer an and formula it's an or formula and so we're going to say the same thing for this example we'll say the status must be client and an email must or or excuse me or an email must exist so now that we pop that in we can give it a save and of course this is going to produce some different outputs for us this formula of course is a different formula meaning that only one of those conditions must be met at a time now i should have pointed out when i was writing inside of this or formula when you look at the syntax it's exactly the same as the and syntax we have at a minimum of one logical condition the second and third and fourth are all optional and it can go indefinitely right so you can bring in as many different things here as you want to check for and if any one of those things is true for the or formula then this is going to get marked off so let's verify that this formula is working correctly if i remove the email address and the status is not equal to client here then the or formula is turning to a zero so it's saying none of these conditions are met here so there you have it those those are the basics for and slash or formulas now in more advanced use cases these formulas can be nested together as can pretty much all formulas in air table and of course they follow the order of operations right so the thing that is inside all the parentheses is always going to happen first and then it kind of grows out from there so we can write combined conditions that makes sure that uh that they're followed in the order that we determine so let's say for example we want to find someone who is either luke or han solo so i'm going to write or well i'll just write and slash or combo here and the formula i'm going to start writing it piece by piece so first i'm going to say or or did i say and said or so we're looking for either first name or full name is equal to luke skywalker comma or the full name is equal to han solo now in this case of course we know just with an or formula that we're going to see that this condition is met for the first record the second condition is met for the second record and the third record does not mean any of these conditions so it throws a zero let me move this over so that it's closer to our raw data here so that's the first part of this so we have an or field but now let's suppose we wanted to combine it with an and as well so we can set up some rules that say uh we want this to be true and we want there to be the status of client so the easiest way to do this in my opinion is to start by writing the and formula the first condition that you want to be true we just copied before i cut it out so i can paste it again so i'm saying that this or formula is the first condition inside my and formula that must be met and i want the status in this example to be client so this might look a little full of syntax here but when we break it down especially because we just wrote it and we know exactly what it says we know that the or formula is going to be looked at and we're going to say is the full name luke skywalker or is the full name han solo if either of those is true then that first condition will be met the second condition is the status's client and then these two conditions are wrapped around an and or wrapped inside of an and formula so both of them must be met at the same time so we know that we have one client or we have a record here that that does contain the name luke skywalker but the status is not client so we wouldn't expect this to meet both criteria right now so it would of course be false in the case of han solo han solo is the name and the status is client so this meets both of those and criterias in this case princess leia does not meet the or criteria but she does have the status of client so she should throw a false as well so we only have one positive uh from this formula and it's the record that meets the name of han solo and has the status of client and these things can be put in any order right so you could wrap that or out you could wrap another statement outside of that and it can just go indefinitely the ultimate output here from an and slash or formula is always going to be a one or a zero a true or false now the reason this is all really important is because now you can build really robust if statements because if you recall an if statement is always going to use a logical qualifier upfront as its first parameter and so you can write a very complex and slash or formula at the beginning of an if statement to make your logical output and then given if that if that condition is met that logical condition then you can embed your outputs for if this otherwise this so used in parallel with an if statement this can be incredibly powerful i'll be sure to link a video below where we went into detail on if statements and please let me know what other questions you might have around this and or formula syntax and how to use it i'll try to get to those in the comments 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 you

Channel: GAP Consulting

Views: 1,942

Keywords: airtable, gareth pronovost, airtable training, airtable consultant, airtable consulting, airtable demo, learn how to build automation, airtable formulas, AND formula, OR formula, logic formulas in airtable, logic formula, writing a logic formula, understand logic formula, formula help in airtable

Id: 7jqVHfiJh_w

Length: 12min 33sec (753 seconds)

Published: Fri Oct 16 2020