Hidden Rollup Formula in Obsidian Bases ![rw-book-cover](https://i.ytimg.com/vi/dWNvs0rEg0c/maxresdefault.jpg) So rollups are available inside of Obsidian Bases, but they're in the formula property, not a unique rollup or relation property inside of the bases view. And they're not simple to do either. So what I'm going to do in this video is show you my personal use case in a demo vault and walk you through how I got there. But I do want to say a massive thank you to everyone inside of the Obsidian Discord server that has helped me out in various aspects with this setup. I will also leave a download link in the description below to the vault so you can explore yourself. But you can see we've got organized folders much more than the uh vault that I'm actually using. Then inside of this daily note, it is a daily note inside of the folder called logbook using the core plug-in. I should mention there's no community plugins in this vault. It's all core obsidian. So no additions needed. Then I have a property categories. It's a list property type. And then I have a link to daily which is a category inside of this folder. So we've got different categories of files. We've got daily, participant, payment, and session because uh I work as a trampoline coach. And so this is the use case that I have. Then inside of the basis folder, you can see we've got a base here. We have the daily bases which shows all the dailies. Then we've got participants, payments, and sessions. And what this is if I go down is this is the participants base which is here. And because of the exclamation mark I can see it in line. So I'm looking at the all participants view of the participants base because if I click this dropdown you can add more views. And in my vault I have far more than one but we're keeping it simple here. But where the really exciting stuff happens is these properties. So these are formula properties. If I open up this folder view, this paid value is a formula property which is actually a rollup of all of the related files to the person one file. So all of the payments inside of the payments folder that is related to person one are being added up to create this number five. Then sessions is very similar. And so any session that has a relation to the person one property is being added up here. that is then being multiplied by five to give me the session fees. And I'm showing all three of these formulas separate because what I'm actually doing is all of these at once to create this balance. So what it's doing is looking at the session fees, which is 15, how much they paid, which is five, taking them away from each other, which makes 10. So they still owe £10 to the club. And I've also got another formula in here, which is age. So I can see how old this person is, which comes from a date property inside of this file. So let's explore this a little bit further. If we go to the basis view, we go to the daily. This is the daily base. There isn't really much in there. It's just the ability to go to the daily category, which is a category page, and then see all of the days. So I can go through all of the days of the log book. And what this means is when I'm in here, I can write notes on the day for the session or for transactions or things I need to keep up with. And so I have a log of everything going on, plus the all participants view, which gives me uh a quick way on my phone to see who needs to pay and who doesn't need to pay of what's going on. And then I have other views in here which gives me more information. That's all the basis view does. The participants basis view is this view that you would see inside of the daily notes. And at the moment there is just one view in here. So when we go to the daily note, you can see there it is. If I write some notes in here, so creating a video and then I go to the daily note, I can then open today's daily note. Open up the templates and now go to the daily note template. I will see this inline participant view in the daily log. So I always have access to this. But what you could do and what I do is have a different view. So if I go to a person, you can see the birthday is a property for this person. And then there's various other information such as emergency contact, etc. And so what I could do if I come to this view is go to properties, type in birthday, and then show the birthday inside of this view. But if I right click and hide that column, I don't have to see it. So I don't get the scroll bar. So what I might want to do is hide some of these formula properties and show that. Or I can come up, go to the arrow, three dots, duplicate view, and then call it all information. Now I have two different views in the base. So when I go to participants, I can see there's two different views. And then in the all information, I can go properties, birthday, it's now showing birthday. Let's move the birthday to the front. Then we go in to our log book. And it's defaulted to all participants because that's the default view in the participants database. But what you can do is come to the end of this, go hashtag, and then save all information. So if I go to yesterday, it shows the all participants. If I go to today, it's all information because I've saved the view of the base inside of the file. And so now when I go to the daily view, I can see the two different dailies. And there's just the one view in that database, remember? And then if I jump to the daily note, you can see this template doesn't have the hashtag. So it will always show all participants first. If I want to change that, I can then obviously add the hashtag in here. So that's how the template shows the specific view for the file that's been created. Now this is important because after our daily category we have the participant category and in the participant category you can see all of the participants. So we got two different views all participants and all information because it is that same database the participants database. But if we click into person one as I did before you can see we've got these two different views and these views are the two other databases the payments and the sessions. This is the sessions database. You can see we've got two different sessions session one and session two and then we've got one payment currently. And if I go down, you can see this is actually saved to sessions. So it's the sessions view, not the all sessions view. If we come up here to the database, these are all of the sessions and these are the sessions which has a different filter. So if we go to the right, actually let's go to the all sessions. If we go to the right and go to the filter all views, the all sessions view and the sessions view both use this as a filter. So the category contains session and the file name does not contain template because I don't want the template file appearing in the view. Both of these filters happen in both of the views, the all sessions and the regular sessions. However, you'll notice this view, there isn't anything filtering. But if I go to the sessions view, go to the filter, there is uh an advanced filter, I guess you'd call it. It's something you can't put in the little editor here, which is looking for the list called attended and asking whether it contains this file. So to keep you orientated, I'm in a participant file and I'm looking at the sessions database and the filter for attended is a property inside of one of the sessions. So if we bring up session one, this file has a property called attended. It's a list property and person one is backl inside of that property. And so inside of the sessions view in the filter, it's looking for the list. Does the attended list, this one here, contain this file? So this file is person one. Does session one inside of the list property includes person one? Yes, it does. So it's going to show that file. Does session two? Well, if I middle click on session two, person one's actually in there twice. So both sessions are appearing because person one is in both of those files. And you'll notice we have two formulas in here. We have days ago and number of sessions. So the days ago, four days ago is how long ago this session was. And that's being worked out from the date property in session one and then in session two. So if I right click on the property and then click edit formula, it says today. So it's looking for today and then taking away you see there note date. It's looking for the note property date. And that note property date is this one right here inside of the session file. And then it's saying, "Hey, give me that in days and round it to a number." And then all I'm doing is adding the text. Just going to highlight that. There is a there is a space here just so there's a space between the number and the the text days ago to make it look a little nicer. Four days ago, 0 days ago. So if we change this date to the 1st, you can see it's now 3 days ago. If we go into edit that formula and change, let's remove that space to so you see what I mean. Now there's no space there. So I edit the formula, add the space in. Now we've got 3 days ago. Then for the number of sessions database, when I right click, go to edit formula. So again, this is looking at the attended list. So person one, person two, person one, person one. It's then filtering that list by whether the value equals the link to this. So, does it equal the person one? So, person one, yes. Person one, person one, yes. And then it's doing a length. So, it's adding that together, which means in this one, there is one link. So, there is a number one being shown. In this file, there are two. So, in session one, it's filtering out person one and then adding them all up, and there was one left. In session two, it's filtering out everything else. Well, there isn't anything else. And then adding it up. And in this case, there's two. So, you've got one session and two session. If we add person two to session two, it's filtering out the person two because they're not this file. This person one file. And so, we've got two still showing. So, I can exit that out. If I get rid of the one from there, now there's only one session showing. So, let's bracket bracket person one. Add that back in. Uh, and now you can see we've got two for the payments database. You can see there's no formulas here and the filter works exactly the same, but it's looking for the participant property instead of attended property. So if we go onto the session one and then we go to payment one, you can see we've got a participant property and it's looking for the link to person one, which is there. So you can see number five. So if we change that to 55, now numbers 55. So let's just remove that. And because these filters are specific to the file, if we jump back to session one and we actually create person two, it's a blank file. We add the template for person. You can see if we scroll down, it's now showing a different view because it's looking for the payments view. There's no payments for person two. And then the sessions, there is one session, and that's the one session they were involved in, which was session one. So, if we click on session one, you can see person one and person two. And the reason I moved from Excel to Obsidian is because of the ability to jump quickly backwards and forwards between all of the sessions that I needed to and find the information that I want. So now we covered the layout and the setup of the vault. I'm going to go through the rollup formulas. What I've done is listed all of the formulas out inside of a file. So if I close all of these folders down, you can see the formula file right at the bottom. And what I've done here is shared the explanation that it was given to me about how this rollup formula works. So the first thing we need to do is actually get the back links. That's the roll up element of this formula. So file backlinks. What that's doing in this database's case is looking at person one. So if I open up that file, you can see the person one file has three different backlinks. Payment one, session one, and session two. So this is looking for the back links. There's the back links. Then it's saying for each value in that give us something. So that's what the map bit is. And all of this is looking for the properties called number and giving a value for that. So what it's doing is it's looking at all of the backlink information. So if we come back here, it's looking through these files for the property number which in this case the session if we look down in the bottom left that doesn't have a number property. So it's not going to show anything. If we come back here and then we go to the payment one, this one does have a number property. So it's found found the number five which means this first line has changed the backlink list from payment one session one session two to the numbers well five nothing and nothing. The next line is filtering for a value. Well if there is nothing and nothing then get rid of it and only show the values that are left which is in this case number five. And the last line here is essentially adding it all together starting from zero. So from zero add the values which in this case is all positive. So it's going to go up to the number five which is why when we go back to our participants base in the log book we can see the number five because it's gone through all of the back links of person one and found there's a number five payment. So put five in there. So if I come into payments I go new note add the template payment. Let's add the participant in. So person one we're going to add another five. So they've paid another £5. We go person one. You can see there's the payment. I haven't titled it. I haven't put a date on it, but it's there. And if I go to a day, so let's go to yesterday. You can see the balance is now -5 because it's paid 10. Looking at the sessions formula is essentially the same thing. Look through the back links, map it, find the property attended, filter it for a value that equals the file name. So in this case, we're looking for person one. So, does person one appear in the attended backlink, which would be session one and session two? Yes, person one appears in some of those back links. So, filter that and then length it. So, add them all together. And that's where the number three comes from. But because this is a file name, I believe this comes out as a string, which we don't want to use because we want it to be a number, which means I need to change it from a string to a number, which thank you very much to those in the Obsidian Discord for helping me. This is how I've done that. And that means I can then times it by five, which is basically what this formula does. It just times three by the number five. So every session is £5. So I can say it's £15 that they owe because they've attended three sessions. I've already got the paid value. And so what I need to do is then take those away from one another. And that is what I've done. I've got the paid formula. I've taken it away from the session fees formula. And that gives me the value of the balance. As you can see, I've got all of the other formulas in here. So if you want to have a look, please do. The date format of the week, attended days, etc., etc. I'll leave the I'll leave the download in the description below. So rollups and calculations inside of Obsidian basis is possible. And what this has allowed me to do is actually move my entire Excel spreadsheet of data with pivot tables and all the rest of it into Obsidian, which is easier for mobile. It's easier to navigate to, as you can see, participant information or go to certain dashboards of certain filtered views, and it's just easier to manage in Obsidian than it was in Excel, especially on mobile. So, maybe I'll do a video explaining that in the future., Hidden Rollup Formula in Obsidian Bases, Danny Talks Tech, 01ked53q4bq7s8xr04pcbaabh2.