Simple and Useful Formulas in Notion

I. IntroductionA. Explanation of Notion's Formula propertyB. How it can be powerfulC. How it can be daunting for most users

II. Examples of Formula propertyA. Explanation of the examplesB. How they can be easily implementedC. How they can be useful for users at every level

III. ConclusionA. Recap of the benefits of Notion's Formula propertyB. Encouragement to try the examplesC. Final thoughts and resources for further learning.

‍

In a previous video, I introduced notions formula property and covered the fundamental terms and concepts. If you haven't seen that, you'll find it linked within the description of this video along with the detailed text post on notion VIP. In this video, we're going to put those terms and concepts to work through a few simple and practical examples. And when we do so, we'll be working with two sample databases.

The first is a roster of basketball players that comprise a team and then the second is a collection of online orders. So let's start with our basketball players. In this database, we're given the last name and the first name of each basketball player. So we're going to use a formula to create the full name by merging the first name with the last name and adding a space between them.

So we'll start by using our concatenation operator which is the plus sign. When you apply a plus sign between two text strings, it's going to merge them. We're going to reference the first name by using the prop function. That function uses the property word and as its argument, it takes a text string which is the name of the property that we want to reference. In this case, it's the first name. So it will reference the first name. Our concatenation operator will add our space which we want between the first and the last names another concatenation operator and then we'll reference the last name. When we confirm we have our first and last name now.

To do this, we could also use the concat function which is the cat. And its arguments are just the series of text strings that you want to concatenate. So again, that'll be the first name and we're gonna use the prop function to reference that again. We want to add our space as the second argument and then the third argument will be our reference to the last name. So there's another way of concatenating or merging these names, the first name, and the last name this time using the concat function.

Now we can do it a third way using the join function. The join function will begin with a join keyword and its first argument is the character that you want to use to separate the subsequent arguments, the subsequent text string. So we'll start with just our space and then we'll reference our first name and then our last name. And there is a third way of creating each player's full name this time using another function the join function.

So let's go over to the age. So this database gives us the players birthday it's of the date value type and we want to use that birthday to determine each player's age. So to do so we want to find the amount of time between the players' birthday and the current time. So we'll use a formula to do that. There's a special function for finding the amount of time between two dates and that's called the day between function.

When we write the date between function, we're going to use camel case. Most of notions functions if they contain two words they'll use camel case. Meaning there are no spaces between the words, the first word is all lowercase, and then the subsequent words begin with the capital letter. So the day between function is going to take three arguments. The first two are the dates that we want to find the amount of time between the first one being the later date and then the third argument is going to be the unit that we want to use two for the returned value.

So the first argument here the later date is going to use the special now function which takes no arguments.

The function is always going to return the current date and time in the second argument. The earlier date is going to be a reference to the players birthday again using the prop function. And then that third argument for the units you're going to put the units within quotation marks to make it a text string. In this case, we want it to return that amount of time in years so we will apply years close out our parentheses and confirm. And there we have each player's unique H.

With that players age determined, we can create a nice labeled age property. In the previous video, we saw why that's useful when you view this database as a gallery. By labeling the players age it adds context to this number that would otherwise just sit out here alone without any real meaning. The viewer is going to be inclined to think that it's the players jersey number when in reality this number corresponds with the age. So by adding this age label to it, we know exactly what it means.

To add that labeled age property, we're going to combine the the term age with a colon in a space and that's going to be a literal value meaning we are not referencing another property we're entering this value and it's going to be the same for every item in the database when the formula runs its operation. So we have our literal value the term age with the colon in the space and then we want to combine that with a reference to the age property. However, we covered in the previous video the importance of value types and when you concatenate two values they have to be text strings. But in this case, the age is a number so we're gonna need a wrap this reference to the age in the format function which is going to convert that number to a string when we confirm we have all of our labeled ages.

So, if we were to avoid that step of formatting, we can see here that we have a type mismatch and it's not going to accept this input. You can see that we can't click the done button here. So by applying it within the format function, we're able to create that concatenation.

Let's move down to our collection of ohm line orders. It's not critically important to understand this but just for some context this database is going to include the order number for each item. And you can see that some of these order numbers are the same some orders have multiple line items. The reason is that there's a different line item for each product SKU. So if an order contains more than one type of product then it's going to have an independent line for each type of product that the order contains. So you can see here that order number one and order number four they each have two line items whereas two, three, and five have independent items just one line item.

The reason that that's important is because we know that if we combine the order number and the product SKU for each line item that's going to give us an entirely unique value. When you're working with databases, you'll often want to use a unique ID for each line items so that they can be referenced in an entirely unique way. So if we're given the order number and then we're also given the product SKU and we wanted to determine a unique ID for each line item, we can combine them as I mentioned. And when we do so, we might want to add a character between them for kind of a nice aesthetic and an easy way to distinguish between the order number and the product SKU. So in this case, we'll just add a dash between them.

This is where the join property comes in handy. Again, we'll use the dash between those two property references. And if we were going to use more than two properties to create this unique ID, we could just add that dash as the first argument, and it's going to be applied between every subsequent string within the join function. But right now, we're just going to be referencing the order number and the product SKU.

So, we'll start with a join function and we want the dash to be our separator. So that'll be our first argument, and then we're gonna reference the order number using the prop function, and then we're gonna reference the product SKU. So, once again, we are faced with a type mismatch and that's because the order number is a number. Not only it is within a number property but it's right aligned in the cell. If it was left aligned, we would know that it would be a text string. So when we referenced that order number, we are once again going to want to use that format property convert it to a string and that relieves us of that type mismatch. And when we confirm, we have our IDs.

Because notion doesn't allow us to generate to use formulas to generate the title property value, what I often do is just create a generator property with a formula and then I can quickly paste it into that title property, and then it allows me to use the unique ID as the title for each item. So let's move on to formatting dates.

Formatting dates is going to be one of the most useful ways of utilizing notion formulas. You can display your dates in almost any fashion using the format date function. That function is going to take two arguments. The first argument is going to be the date, so of course, that's going to need to be a big date value type, which will generally reference another property, a date property. So in this case, we have an order date property to reference. And then the second argument is going to be a pattern that tells notion how to display your date.

So we'll take a look at exactly how you determine how to kind of construct your pattern. But in this case, let's say that we want our date to be a three-letter abbreviation of the date with a period, and then the two-letter day with a comma and then we want it to have an apostrophe with the last two numbers of the year. So that's the pattern that we'll use for that. And here we have our error too many arguments in the format function because I didn't use the correct keyword which would do often. So, you'll just want to pay attention to those errors. So now that we've made this the format date function, and we'll confirm and our data is formatted just as intended.

The way to determine how you'll want to construct your patterns in order to display your dates as you want to is you can visit the cheat sheet that published an ocean VIP. So if you go to notion that VIP slash formulas, we took a look at this in the previous video as well. But within the top kind of sub-navigation, there's an option for date formats, and if you click on that, you're going to see all of these different patterns that you can apply to your combined patterns in order to construct your date formatting pattern. So you can see that you can format years, quarters, months, weeks, all units of time, as well as a.m. p.m. time zone, offset and more. So this is a very helpful resource for beginning to wrap your head around how to construct these patterns to display dates the way that you want to.

In this video, we will be discussing how to format dates within Notion. You can visit the cheat sheet that was published by an ocean VIP. If you go to notion VIP slash formulas, we took a look at this in the previous video as well. But within the top kind of sub navigation, there's an option for date formats. If you click on that, you're going to see all of these different patterns that you can apply to your combined patterns in order to construct your date formatting pattern.

You can see that you can format years, quarters, months, weeks, all units of time as well as a.m. p.m. time zone, offsets, and more. This is a very helpful resource for beginning to wrap your head around how to construct these patterns to display dates the way that you want to. This isn't the prettiest display of dates, but you can see how we've applied our pattern to display it as intended.

Let's move on to another topic. This database gives us the tax rate of each item as well as the price of each item. The price is actually calculated based on a number of other properties. But for our sake here, let's just say that the price is given to us and that each item has a different price. But the tax rate is consistently 4%, so we can easily determine the amount of tax to add to each item by multiplying the price by the tax rate.

To do that, we can either use the multiplication operator, which is just the star character, or we can use the multiply function, which is going to take its two arguments, the numbers to be multiplied, and it will return their product. So, let's reference the price property and then with reference the tax rate property. And when we confirm, we have the amount of text that we won't add it now.

Because the tax rate is consistent for all products, we could actually just enter the literal value of point zero four. And sometimes Notion acts a little funky, but we can okay and see here we have another example of a type mismatch and the importance of paying attention to the value types that you're entering. So, of course, that point zero four no longer needs to be wrapped in quotation marks because we don't want to give it a string; we want to give it the literal value of point zero four as a number.

So we can now confirm, and we are going to have the tax to be added to each item produced in a slightly different way using that literal value. Having calculated the tax and given the price, we can then calculate the total price for each item. Of course, all we need to do there is just add the tax to each item. So we'll just do that using the addition operator, which of course is the plus sign. And when placed between two numbers, it's going to return their sum.

So we'll just reference the price and then we'll use that arithmetic operator, the addition operator, and then we'll reference the tax. And another helpful little error here, we added a space after price. Of course, you're going to have to name your properties exactly as they're named within the database, and so you'll just want to pay attention to every character. You'll encounter those little errors often, and so it's helpful to see me kind of debug as I go about this.

We'll confirm here and we have the price of each item. Now, sometimes it will be helpful to track the fulfillment of your orders and we're gonna get into how to do that in a subsequent video. After we cover conditional functions, the "if" function, and how you can perform one series of operations if certain conditions are true and then another series of operations if that condition is untrue. You can even nest those conditions to perform a variety of different scenarios.

So we are not going to dig into all of that yet but what I do want to show you is just how we might calculate the number of days since the order and then these progress and urgency properties are going to factor those days, that number of days since the order, into their own calculations. But for now, we'll just do this first. And once you practice with some of these more introductory functions, you can get into those more complicated ones.

So to calculate the day since their order, we're going to use that date between function again, just like we did when we calculated the player's age. To date between, there's our keyword and then we want to start with the later date. So once again that's gonna be now, because we want to know how much time was between the date of the order and the current date. And then the second argument is going to be a reference to the order date property. And then remember the third argument for the date between function is the units that we want to use to display the output.

So remember we used years for the player's age but this time we want to display the output in days. So that'll be the third argument of this date between and when we confirm, we can see all of the different durations for each item.

Those are some simple and practical examples of the ways that you can use formulas to kind of bolster your databases in your broader notion workspaces. So practice with those and revisit that first video, "Notion Formula Property Part 1" if you haven't seen that yet and explore the posts on Notion VIP as well as the cheat sheet. And once you get comfortable with that, you can move on to some more advanced use of functions including those conditional functions with the "if" function.

‍