Advanced Notion Formulas & Rollups for System Automation

I. Introduction (0:00)

• Brief overview of Notion and its capabilities

II. New Task & Project Views in Alignment Zone (2:10)

• Explanation of the new feature
• Demonstration of how it can be used to organize tasks and projects

III. Automated Progress Bar (4:32)

• Explanation of how the feature works
• Demonstration of how it can be used to track progress on tasks and projects

IV. Automated Completion Checkbox (10:52)

• Explanation of how the feature works
• Demonstration of how it can be used to mark tasks as complete

V. Rolling Up Rollups (14:48)

• Explanation of what rollups are and how they can be used
• Demonstration of how to create and use rollups to organize and summarize information

VI. Conclusion

• Summary of key features covered in the video
• Encourage viewers to try out these features for themselves and see how they can improve their workflow in Notion.

‍

Hi, everyone, welcome back to our ongoing series on how to create a life operating system in Notion. After the previous video, there were a lot of comments about what about this approach? What about that approach, and there were a number of different ways you could do things.

And I had made certain trade-offs. Each of them has pros and cons. And I had made the trade-offs and made a commitment to certain decisions on how to implement them. But the counterpoints are legitimate. And they do have some advantages, even though they have some trade-offs.

And so I wanted to slide in this extra video to show you some of the alternative options that are highly relevant to the system we're building and different ways to do things that have some benefits, even though they have some costs that go with them. So I'll let you make the choice. And at the same time, I want to keep an open mind.

So I'm actually committing to trying these even though I had rejected them for alternatives. I'm going to actually try them for at least a few weeks, maybe a couple of months, and then make a decision for myself only after having tried both methods for a while. Then I'll have a much better, more informed basis on which to make my final decision as to which I use in my ongoing system.

But regardless, I'm going to give you the option to make the choice by presenting now, the alternatives. The alternatives tend to be a little bit more advanced. But if you followed everything in the last video, you'll easily follow this step up on the new applications of various formulas and roll-ups that take things to a little bit more automated level.

And then finally, the third of the three examples I'm going to give today contradicts to a degree what I said in the previous one, that you cannot do roll-ups of roll-ups. It's true that you cannot do a roll-up of a roll-up, but there is a way of working around of mirroring the roll-up and then rolling up the mirror of the roll-up. So there's a way to do it. In some circumstances, it doesn't work for everything. But it works particularly well when you're rolling up numbers. So I'll give an example of how to do that. And that becomes very relevant when we're talking about the daily, weekly monthly roll-ups of your performance statistics lets you roll up your daily information up to your monthly through the weekly.

As usual, share any thoughts or questions in the comments below. If you want to expand the discussion, join me on Twitter, ask a broader range of questions or chime in on any topics far beyond the scope of this particular video, we can do that over Twitter. And with that, let's dive in.

So we're jumping into the alignment zone. I'm going to do all the examples in this area. I'm going to close the sidebar just to give you a cleaner view going down or I'm going to open the project. Just quickly before we dive in, I've changed the views. And I find this much better for reviewing the action items in the alignment zone, I've changed the default view to the calendar view that is to me just a much easier way to work with actions when it's a large pile or a long list rather than the way I had previously had it, which was just a long list view here, I liked the ListView a lot when it's just one day, whether it's the today toggle, or the tomorrow toggle in the action zone.

It's hard to visually distinguish different tasks and projects in a single day view, so I prefer to use the calendar view. I've made this the default view in my alignments, and it's just easier to work with. I've also changed the default for projects and made it a double view. The top level view is more concise with a gallery of only the active projects. This view is sorted by task progress bar, which I'll get into later. I've made this the smallest settings going to properties, you can do different sizes, I made it small so I can fit the most in and it's just a quick access view of the active projects only. This is the same view we have in the action zone.

So when you're just jumping in to manage tasks or align them with the goals in a specific project, I can quickly access the active ones right there. In the same toggle view, still in the same toggle, it opens a second view underneath the gallery view. This view is a board view based on status, which I call the status board view. I've got a number of views, but this is now my default, whatever you put on top here is the default. That's what will naturally open up when you're opening a clean session in Notion. So it's particularly important which one you put on top, because that'll be the default open.

So I've got the future, the next up, on hold and active. So if I'm dealing with an active project, I'll just engage with it up here. And it's got all the relevant stats here. But if I'm managing which ones are active, and which ones are next up and what I'm putting on hold, but managing the lineup, then I use this board view, and it's a really effective way. The old view is great in other ways, it does provide the maximum amount of information in the smallest amount of screen real estate. So that's useful in a way. And if I'm managing the design of the board, then I'll use this. That's what I'm going to use as we do our demonstration here.

But when I'm just implementing my own personal operating system, managing my life, I like using that board view a lot. But when designing, changing and building the system, I think nothing beats the table view. So that's what we'll work in here.

The first of the advanced roll up features and alternate approaches I wanted to show you is the automated progress bar. We're going to bring automation to the progress bar. Previously, it was called just the progress bar. I've changed the name to task progress bar, and I'll explain why. But first, let me show you what the change is. Now this is a formula and it's going to automatically adjust. There's been a lot of discussion on this approach versus my previous approach. My previous approach was to just do a subjective assessment and after I show you how this works, I'll explain the pros and cons of this approach versus the previous approach.

So this here is a remaining actions roll up.

This is a completed actions roll up.

And this is a total actions roll up actions or tasks.

These are action items from our action items database down here. And the first one is rolling up the remaining actions were in the project database. The relation is the connection to the action items or task database, we're selecting property, the done checkbox, and then we're choosing calculate, which is total add up all the unchecked boxes that are linked to this particular project.

So that gives us seven, there are seven unchecked done boxes, which means there are seven tasks assigned to this project that have not been completed, then we do the same thing with another roll up for the checkboxes. So this time it's done box is checked, it's totally in them. So there are five that are done, and seven that are undone, unchecked, and then we do a total, we roll up all of them.

So relation to the action items, properties action item, and count all which is just total or sum. So that's the checked and the unchecked combined.

So first of all, the remaining actions total is useful in and of itself, it tells you how many to go or how close you are to completing everything. And either needing to add more tasks to complete that project or checking the project office done.

Now these two are where to use to create the progress bar. So these are where the formula is pulling from. So the formula is pulling from completed actions and total actions, I'm going to put a link in the show notes below to how to access this formula. So you can just copy and paste it, all you have to do is change the name here for completed actions. And for total actions to match whatever you have titled these columns, these two columns here. So if you have different names than these, you'll change the names in the formula to just be the same as the two names you have for the equivalent properties in your database.

So now at this point, in this new application, you don't touch any of these, this is completely automated, you don't touch the total action, you don't touch completed actions, you don't touch from any actions, it rolls up, all you do is go into the project. And as we've discussed before, manage the actions down here, you make sure that any active project, this project is an active status. So it needs at least one active task, if not two, or three, and then you line up the rest, all you're doing is managing these and then finishing them and checking them off as done as you go.

When you check it off as done, it'll disappear from this list, but filter is set for done is not checked. So that means once you check it, it disappears from here. So this is managing the active tasks to go. And it says we have seven remaining tasks. So we'll have seven tasks down here. That's the count, going back to the overview. So that's going to automatically calculate this, you don't touch any of these that's purely automated.

Now, here are the pros and cons. So obviously, it's nice to have it automated, that's great. The downsides are first of all, tasks are not all equal. So that means this is assigning equal weight to every task, even though some tasks are big, and some are very trivial. So it's not an accurate representation of the percent of the project toward completion, it's the measurement of the percent of your tasks that are finished of the total task count.

The speaker is discussing a project management system and the pros and cons of using an automated system versus a manual approach. They mention that the manual approach allows for a more accurate assessment of how close the project is to being finished. However, they also mention that the automated system can be useful as a trigger to jump in and add any additional tasks as it gets higher. The speaker also introduces the concept of an active column, which allows for a roll-up of how many unfinished projects are remaining in pursuit of a goal. They mention that they will be using the automated system for a while and everyone can make a choice after trying it.

Now in this example, I'm going to show you how we're going to automate this project count. So already, we're rolling up the project count, but the only way to roll it up is by a check box.

Previously, I had a manual check box here, where you just check it when you're done. And the same way, we have a manual check box here, which looks like that in the task database. But a lot of people found it more effective. And I can see certainly some clear benefits to having this as a formula.

So I've changed that to a formula. That's the formula indicator. And now it says to not have an if then statement. If the status, which is this column here, is active quote, then the result is True. True means checked. Otherwise, it's False. False means unchecked.

A lot of people who have shared comments do it a different way. They have when it's completed, it's automatically checked. But I like to have the checkbox as active in this case. And that way, we have more of a visual indicator at the top of those that are active.

Although really, I'm going to hide this column, I only have it active here, I only have it visible here for the sake of this demonstration. Because once this is activated, read the formula, you don't need this anymore. Previously, you needed it in order to do the manual check.

But right now active will indicate a lot of people are using completed as the trigger for the check. So the formula would say if quote, completed, then check. And then when it's checked, it would either disappear from this from the filter, but then you have all these various types other than active that would be counted.

So I've made it such that active is the trigger for a check. So all the active ones are checked in. And therefore, when we go up here, project count, we're rolling up the formula, reactive and counting all it's totaling the number of active projects tied to each goal outcome. And this way, all we do is set the setting here, we change it to hold that just dropped down to here and it's unchecked. If we change it to active, it jumps back up, and it's checked. This is automated now.

Now the trade offs here are not significant. It's really not a big deal. It's easy enough to check off a checkbox. But this way, you can completely manage it with one field, which means you can hide the other field and you have fewer fields that are visible, visible, so less clutter, which is the positive, the negative, which is fairly slight, but something which is this takes two clicks, to choose one to open, and one to select. Whereas a checkbox takes one. When you just click it, boom, it's faster.

So in the case of projects, it's not a big deal. But in the case of tasks, which I'm checking off 910 12 a day, I actually just want to go fast, boom, boom, boom, boom, especially when I'm on my mobile device where it's even faster just to hit check, rather than having to double tap and open Dropbox selections.

So what I've done is I've left it as a manual checkbox here in the task database. But in the project database, where I'm checking these off much less frequently, and really running everything by the status selector, I've made it an automated formula. And that rolls up nicely.

Now it's true, you cannot do that. That is not a functionality. You can do a notion. Unfortunately, I've added it to a list of things that I'm encouraging them to put into the software. But at the moment that can't be done.

But there is a workaround, in some circumstances, particularly when you're rolling up numbers, which is a lot of the most important stuff to roll up. So let's go up here, we're gonna take a look at our review cycles. And we're gonna first go into the weekly review.

So I've opened the week of May 11, through 17th, just as an example. And what we have down here is from our daily down here is the relation the direct relation, that's the arrow to the daily tracking. So for the week of May 11, through 17th, we have the individual days from the daily tracking through the 11th through the 17th. All the days are linked through a relation. That's the relational link to the daily tracking database.

And because of that, we automatically roll up all these magnifying glass icons over here indicate an automatic roll up, that once you make that link, all that data comes with it automatically. That's a beautiful thing when we sit down to do our weekly review.

But that information rolls up and we cannot roll it up, then again to the monthly review, we had to summarize, and the summary could be rolled up, which in some cases is better, like here, we're rolling up the improvements. And we've got this list of improvements.

And what I like about having to actually do a summary and not rolling that up directly is if you were to roll that up directly to months, you get 30 days worth of entries. And that's just too many. So if during the weekly review, you review those and look at what's most significant, what's reoccurring what's most troubling in terms of needed improvements for the week, and then you pick one or two and entered here as a summary, and then that would roll up to the month. So that's what I'm doing. And that works really well. I think that actually works better than rolling up the roll up.

But if you wanted to do a roll up of data, sometimes the numbers would be helpful because you can average numbers that can consolidate and simplify just by averaging. And that could be helpful to roll up directly from these daily entries.

So for example, we have sleep average, the average, this is each night's sleep, the length of sleep I got each night. And then this is an average of that. And that's great that both the individual listing is useful because you can see outliers, and the average is great, especially when you're looking at a table and looking at trends over time from week to week. But this is a roll up. So you cannot roll that up to the monthly review. And that would be interesting to look at.

But what you can do, and I've already done it here, but I'm gonna show you how to do it again, is I've applied a formula that mirrors the roll up number, so we're taking the roll up number and entering it into this formula, and it is duplicating it. And then it's also giving us the ability to round up the decimals 2.5 rather than point 486, which is a bit messy and a level of precision, that's just not useful. So I'm gonna show you how to do both of those.

First and foremost, let's add a new one here. We're going to add a property, I'm gonna drag that up right under this one.

And the property we're going to choose formula to, we've already this top one here with a magnifying glass is the roll up. This is connected to the daily tracking database, it's choosing the sleep time calculation property, that's the total amount of hours slept during all those days. And then we're doing an average, we could do some we could do median, min, max, whatever. But average is what's most useful here. So that's what we choose.

So that's already rolling up now. But that can't be rolled up further to the month. So we want to mirror it with this formula. So what we do here is we take this new property we added make it a formula, we'll call it sleep of form, in order to make it clear for the sake of this demonstration. Now we're going to take this formula. Again, I'll link to this below. So you can access the formula to copy and paste it, the formula in the middle is what mirrors it. I'll show you how this all works in a moment here to paste that in there.

And now it mirrors that number, but it gives you way too many decimal points. And that's just not helpful, but it is mirroring it at this point, we can now roll this up to the month because it's not a roll up. It's a formula, you can roll up a formula, but not a roll up. But we really want a cleaner number. And that's actually going to help the view here having that cleaner number.

So the way you do that is you put this round part in the front and then you put this times 10 parentheses slash 10 divided by 10. Now it's kind of absurd that you have to do this. But this is how you round a number in notion, if just to show you how this works, if this were 100 the number of decimal places here, then we would get two decimal places. You see we got it there right there. If we put three here, watch the number here, but three here, then you get three. If you put just one zero, you get point five, it's completely insane.

That is what you have to do to round a number of notion should be a simple selector. You can do all these other things with numbers, but you can't round it to the right decimal place, but you put this formula in and it will take any crazy long number and it will round it nicely. So we just wrapped that round around it that when that whole number, that whole formula is applied. So now from this week, we have created a formula that mirrors that mirrors the roll up.

So when we go into the month to now we've jumped over to April, just as an example. And now when we come down here and add a property, so here we got the property we just added, change it to a roll up, because now we're rolling up. And now you select weeks, because we're rolling up where in month in the month database now, but we're rolling up a real relational link two weeks, in the weeks, we choose any of the three formulas. Now, these are all formulas that are mirroring roll ups, you notice none of the roll ups are listed. There's no magnifying glass icon, which is the indicator for roll up. But we do have all three of the some indicators, which means these are formulas.

Each of these three are formulas that are mirroring a roll up, the one we just looked at and calculated is the sleep average. So let's choose that.

And now by default, it's taking each of the individual numbers. Why is that? Because these are the weeks that this month of April is linked to through a relational link. And because of that, we're getting five, five weeks, five numbers rolling up from that formula. And so that's one sleep average for each week.

Now we can change this to an average itself. And then we get the average for the whole month by averaging each of the five weeks that are connected through relations to this month. Now we've got the average sleep for the whole month. And that's pretty useful. So it's nice to be able to do that. So that works perfectly for numbers.

The problem is when we go to text, see, it'd be nice also to be able to roll things up from action items database, the project database to the goal outcomes. Let's look at goal outcomes. For example, in here, one of the additions I showed you in the last video is the pillars are now a roll up. So we'll add the goal will connect the goal outcomes to the value goals through a relational connection. And then since the value goals are already connected to the pillars up above, these are rolling up along with them automatically. So that's working out great.

Well, it'd be nice if we could mirror this and then have these pillars roll down to projects. For example, the problem is when you do that, you make this a formula. And you select that column, it is visible here, pillars is listed, and it does mirror it. But since this is a link, it's coming through as a link, it's giving you the URL, which is totally useless and doesn't help you at all. So you can't just mirror the name.

Now remember, when we did the roll up of the number, we had to add a formula that's called the two number formula. So it's a two number in parentheses, and it converted it into a numerical format. So it wasn't just a text, if you didn't do the two number, if you just did the prop length, it would show you the number but it would be in text format. So you couldn't apply math to it. So we did the two number formula to turn it into a number format, which worked perfectly for numbers.

Now, this is not a number, so we can't so there's no point in using the two number formula. And of course, it wouldn't work anyway, it's almost like we need a two text formula. But there isn't a two text formula to translate this into text stripping it from the URL. So if anyone has a solution to that, enter in the comments below, if anyone and whoever enters the most concise and clean explanation, I'll pin that to the top if there is a solution. And someone in our community here, one of our viewers articulates that in a comment, I'll pin that below.

So be sure if you want to be able to mirror these enroll these text lines up and down, especially through the pillar to pipeline pyramid here, which would be a nice use of it, then take a look and see if there's a pinned explanation on how to do that. But I don't think there is I've looked pretty hard for this, and I can't find a solution. So I think this is just not functional for this usage. As far as I can see, it's only functional for numbers.

‍