expressions are what separates your app from the outof boox solutions to the custom built apps perfect for your business yet I hear all the time how intimidating writing's Expressions could be but it doesn't have to be like that in my six years of training app sheet developers I've helped out thousands of people like you master expressions and build amazing business apps in this video I dissect the anatomy of Expressions helping you to understand how and why they work so that expressions like this can be a fun puzzle if you have experience writing formulas and sheets or Excel this is really going to translate but trust me for the rest of you I was once in your position we'll be breaking this down to make it really easy what is an expression well an expr expression is a Formula like statement used to return values or Define a behavior in your app before we jump too deep into Expressions we need to talk about data types in app sheet a data type refers to the kind of data that a column and a table can store let's check them out within the API editor on the data tab you can find the data type for every single column as we can see the driver name is a name key is a text and so on you have over 30 options to choose between not only do columns have data types each expression you write will have a return value that must be a specific data type based on where you're applying that expression within the expression assistant right here you can see the expected data type that means that anything inside this input requires to have an return type that matches email to test this out let's go ahead and put an invalid value let's say 12 since 12 has a result type of number it is not one of the expected data types of email let's try putting in an email this function user email Returns the email of the person currently using the app which is a data type of email so it is being passed in as a valid value all right let's dive in what makes up an expression you'll find expressions are made up of four components there's constants columns operators and functions constants are a static value that you manually type into the expression assistant for today's examples we will be using this field delivery template app if you would like to follow along click the link inside the description press managed collaborate and publish and then copy the app for yourself let's go ahead and go into the status within status if we scroll to the right into the initial value we'll go ahead and put in pending this ensures that anytime a new job gets created its initial value will be pending columns these allow you to retrieve a value from a spe specified column in your apps data they look like this you'll see the name of a column wrapped in squared brackets columns return Dynamic values what this means is that based on where that numb column is running it returns different values for an example LeBron James's record if you were to run that numb column expression it would return 23 however someone like Kobe Bryant on his record it it would return 24 to see this in action let's look at Kevin Durant's Kevin Durant's number is 35 and so on his record that numb expression would return the number 35 the next important feature is defferences think of D references as a portal into a ref column what this means is that in this example we have NBA basketball players at the end of the table we have a reference to their team the team table looks like this using notation for D references you can use a team ID where reference column in square brackets followed by a period and then a column that belongs to that referen table let's look at an example of this for Kevin Durant his team is Suns with ID Sons ID if we go to the team table and find the record where the key is Sun's ID we can go to that record and retrieve the colors from it which in this example is orange and purple this expression would return the same colors for teammates let's say Kobe Bryant and LeBron James both play for the Lakers so they both return purple and gold if you want to hear more about this feature we have plenty of videos on this topic Down Below in the description for the column example let's go ahead and create a new virtual column for this virtual column we want to label this one drivers phone since we just created this virtual column it has no specified data type and so no data type is expected however we called it driver's phone number and so what we want to do is we want to return the driver of this job their phone number so let's go aad ahe and do an expression that does that so if we were to start off just by putting in driver let's see what that would return for the driver it simply Returns the ID of that driver's record no other information yet but we can use the idea of a dfference to retrieve a column from the driver table let's go ahead and try it out first we want to do the period and then square brackets if we wanted to return the driver's name we could do something like this and it would return the name of the driver who is assigned to this job but that's not what we want we want their phone number so let's go ahead and add that this is a common use case of a reference whenever you want a child record to retrieve a characteristic from a parent record you'll do something like this next we have operators these are broken down into two different families we have arithm metic operators and logical operators first we have arithmetic operators these allow you to apply math to an expression think plus minus multiply divide let's say for an example you wanted to find the net total you could multiply the quantity by the price or let's say you wanted to find the average hours worked per day you could take the total hours of the year and divide it by the number of days in a year next we have logical operators these allow you to compare two values remember these always return true or false think like this true or false the quantity is greater than in stock let's say Quantity of how many they sold is 12 and the end stock is 10 this would return true however if quantity was five it would not be greater than so this expression would return false next we have weekly hours is greater than 40 this is a column being compared to a static value last we have status does not equal completed this would return true if the project is not completed but would return false if the status is completed this could be used to say yes or no is the project active for our arithmetic operators we want to go ahead and create a new virtual column this virtual column we want to label it completion duration a fun thing about arithmetic operators is that they don't only apply the numbers or decimals you can also apply them to dates times date times durations and lists okay so we want to start off by entering in the drop off date time and then subtracting the smaller value which is the pickup date time this expression right here Returns the duration of the elapse time between the pickup date time and the drop off date time and our example of a logical operator starts off with us creating a virtual column and calling it in progress we want to return a value of true if it's in progress and a value of false if it is not in progress the way we determine if a job is in progress is if the status equals started equals is a logical operator let's go ahead and use it first we want to do status is equal to the constant text of started this will return true for every single job that its status is started let's go ahead and check this out right here we can see that all these are false currently on the screen because we see canceled pin ending canceled finished let's scroll down until we see one that is started right here this one is started and it is returning true perfect and last we have functions a function is like a pre-built command that helps the app return a value in a way that both you and the app can understand when I say that we use functions to communicate in a way that both the app and US understand it's like us speaking a language that we both share but to speak that language we have to follow a specific syntax what is syntax well syntax is the precise format and rules for constructing a function think of it like this function syntax will always look like this a function name followed by opened and closed parenthesis in this example you see within those parentheses there are things called parameters parameters are inputs you plug into a function to control its returned value these parameters have a delimiter of a comma that means that a comma separates each of the individual parameters this is true for all functions function syntax is almost like learning a second language what I mean by this is an English speaker beginning to learn Spanish would translate each word from Spanish into English so that he can properly understand what he's saying or hearing an example of this using App sheets function syntax is this in this select function we are going to return a list of all users emails where their rle is manager and we don't want to remove the duplicate emails another example is in this select function we're going to return a list of projects clients where their status is not completed but we do want to remove the duplicate clients so this would look like a list of all the clients who have active projects any client who has multiple projects that are active would still only show up once being expected to know all this information can be a little daunting when you're constantly running into to new functions you've never seen before so we built this free dashboard to make all that searching easy as you can see for every function and app sheet we have the data type the function returns as well as the parameter inputs required to build the function we've also included all that information in a practical example in a short recorded video breakdown for the in progress example we talked about how we can return yes no if a column is equal to another value what if we wanted to see if that column was equal to this value or another value let's look at two ways we can do this let's go ahead and create a virtual column called closed one we want this to return true if it is a status that means the job is closed and false if it is a status that does not mean it's closed this means that we want it to return true if the status is equal to finished or if the status is equal to canel let's see how that looks we'll be using the or function the first parameter is expecting a yes no condition for this yes no condition we're going to do status equals finished the second parameter is also a yes no this one we're going to do status equals cancel to convert this function into English syntax like we talked about we want to replace the commas with the word or this looks like this function will return true if the status equals function the comma or status equals canel let's go ahead and create a virtual column for our second option this one we'll call closed two in closed two we want to use the in expression in the in expression to convert it to English the first parameter is a text you want to search for the second parameter is a list that you want to search through for that first text think of it like this the needle is inside of the list Hast stack all right so the needle is in the Hast stack if this needle is inside this list it will return true otherwise it will return false so the needle that we're looking for is not needle but we're looking for the status so if the value of the status is found in this list and the list is going to be finished is the first element of the list and the second element of the list is canceled so what this says is if the status of the job is in the list of finished or canceled this will return true otherwise it will return false for pending or started let's go ahead and see this tested out so on this test page this one is cancelled and we can see that it's true this one is not finished or cancelled it's pending so it's false for the final example we're going to create a driver level this is going to use all four of the expression components in one expression let's check this out a driver level is going to be an attribute of a driver so we want it to belong on the driver table first we need to determine which jobs have been assigned to which drivers luckily whenever you do a reference to another table let's say this driver column is a reference to the driver table what this does is inside the driver table creates a related jobs on the other end this is going to be a list of all the jobs that are related to that driver as we can see each of these drivers have a list of jobs for each of them we want to take this column and wrap it in a count function the count function takes a list of items and converts it to a number of the count of items inside that list let's go ahead and check that out since this is an attribute of the driver table let's go ahead and create a virtual column right here in the driver table we'll call this driver level in this virtual column we're going to go ahead and type in the function count within that count we're going to put in the column jobs this currently Returns the number of jobs assigned to each driver this is an important value for us to save so let's just hit enter a few times to put it aside the main function we're going to be using in this expression is the ifs the ifs function is a conditional function meaning based off of a condition it returns a different value for the first condition of the if statement we want to go ahead and copy this count of jobs and paste it right there we can get rid of that initial one so if the count of jobs of this driver is greater than 20 we want to return the value of platinum this would mean that this driver is a platinum driver we want to go ahead and copy this expression and paste it down below now if that driver has completed more than five drives we want him to be gold paste it again if he has completed more than one job we're going to say silver and then last if they've completed what is equal to zero jobs then they are going to be bronze since this is the end of our expression let's go ahead and remove that last comma so to convert this function into English syntax let's say like this if this driver has more than 20 jobs return the value Platinum for their level if they don't but they have a count of greater than five then gold well if it's not greater than five but they do have a count greater than one then return silver but if it's not greater than one but it's equal to zero then let's return bronze let's go ahead and test this we remember that the top three right here all had 7 s s and six and then six again let's go ahead and scroll down to these people who are silver these people have very few drives last we're going to go ahead and add myself as a user to see if I end up being broads let's hit save now if I go to the driver View and add myself let's go ahead say Cameron Noob and putting in a fake phone number hit save scroll down here Cameron Noob is a bronze driver because I have zero jobs if you've enjoyed this video and would like to see more content like this please subscribe and like this video also if you have any remaining questions please don't hesitate to leave a comment down below we'll make sure to get to you we appreciate you joining us for this video and hope to see you again bye