Create an Invoice Template Notion for Nonprofit Effortlessly

Invoice template notion for Nonprofit

i've been pretty disappointed by accounting apps they're all either functionally limited buggy or ugly and often a combination of all three and i thought freshbooks would be different after seeing its sleek branding but ultimately that proved to be lipstick on a pig and after one particularly agonizing experience i decided to build my own accounting system using notion so notions unique blend of databases and documents lends it beautifully to managing finances and the growing collection of integrations powered by the new api makes it even more suitable so i spent the long memorial day week in developing my new system and the outcome leverages notions most powerful features including relations and roll-ups formulas database templates and those integrations and that makes this system a remarkable learning tool notion really clicks when you get your hands on practical examples and everyone in some capacity manages finances so to help you learn notions most robust capabilities i'm going to guide you through the construction of a sophisticated accounting system and of course you're more than welcome to customize and use the system for your own finances but what's most important is understanding the underlying concepts and for your reference as you develop the system i've made the final outcome available as a template on notion market which i'll link to within the description of this video and if you're entirely new to notion databases you may want to start with some of my introductory resources which you'll also find linked within the description of this video so if you're familiar with my notion resources you know that i approach every workspace with an essential tenant and that is to use databases not pages and as the foundation of my bulletproof framework this strategy is known as the bulletproof principle and i detail this approach in my post about optimizing your workspace for the api which i'll link to in the description of this video but generally speaking a page called master databases centralizes all information within master databases such as projects tasks companies people and resources but you rarely visit those databases directly and instead you access them through linked databases which are filtered formatted and sorted to display your information in various contexts so for example you view tasks filtered for a particular project and resources filtered for a particular resources category and these contextual linked databases live in two primary locations one being at a top level headquarters page which might be the only non-database page in your workspace but you'll typically access these contextual linked databases within items of other databases where pre-configured templates automatically filter for items linked by relation properties so for example a company template often filters the people database for each company's employees and these concepts will click as we construct our accounting system so our system is going to be contained in a top level page we'll call accounting databases and that page is going to contain the four master databases that facilitate our system so the first of those databases is going to be the organization database now many workspaces will already have an organization's database it's common to want to master database of organizations for all of your other databases to reference but in this case we'll include it in our accounting databases page so that we can have a contained system so this organization's database is going to contain two different categories of organizations it's going to have the clients and customers who pay your invoices and other forms of revenue and it's also going to contain the vendors who you pay for your expenses so the accounting databases page will also contain a database of your accounts and those accounts will include your bank accounts and then as applicable it'll include your payment gateways that facilitate your credit card and ach and other types of transactions and then it might also include your accounts receivable for your issued invoices that remain open and then this accounts database for each account is going to display the balance of the account and then you'll have an invoices database to track all of your invoices with helpful information and all of these contextual properties are going to be automated they're all going to either be formula properties or rollup properties so that you never really have to make modifications to this database you can open it just to view all of this helpful information for each invoice and then the hub of our system is going to be the transactions database this is where you log all of the movements of every dollar that impact the balances of your account so that's going to include a handful of types of transactions and as this transactions database grows over time you'll never have to look at all of your transactions in bulk in a way that would be unmanageable instead will have pre-configured views that are filtered and formatted and sorted in ways that make it really easy to manage all of your transactions so those types of transactions are going to include your revenue for your issued and paid invoices and other forms of income it's going to include your expenses for your cost it's going to include equity transactions for owners contributions and withdrawals it'll have transfer of assets between your accounts and then it'll also have balance transactions for establishing your opening balances and then making ad hoc balance adjustments so let's build this thing we'll start by creating our top level accounting databases page and within that page we are going to create our four master databases now i'm not going to add the icons to these pages and databases as we build them out here together but if you want to use the icons that are represented in the template you can access those at my tool notion icons at notion dot vip slash icons there you can access that nice monochromatic collection of line icons that'll make your workspace nice clean polished and sophisticated so we're going to create each of these databases as a full page database and the reason i'm going ahead and creating these databases is so that when we create our properties we can establish the relations between them and as i name them here i'm going to add in parentheses and a s that makes them easy to search for when we establish those relations as you'll see so i'll start with the organization's database so what i'm going to do is i'm going to search for table here and choose the full page table option and then name it organizations and we'll go back and we'll do the same thing for our accounts database and for our invoices database and lastly for our transactions database and you can leave them in this vertical list but i like to kind of arrange them in two different columns so with those established and again i'm going to add those a s is to the name that makes them easy to search for we can then go in and add the properties and views and so forth to each one so that'll begin with the organization's database now what we'll do is we'll rename the title property to organization because this is a collection of organizations and then we can convert this multi select tags property to just a single select property and what we'll do is we'll name it category and remember we're working with two different categories of organizations in our accounting system we have clients who are going to be the companies that pay our invoices and other forms of revenue and then we also have our vendors who are going to be the recipients of our expense payments and then our other property of the organization's database is going to be our transactions database relations so we want to name this property transactions and it's going to be a relation property that's going to it's going to relate to actions database and because we added this as to the end of it it's very easy to search for so we can choose that and create relation and then that's going to give us the ability to choose items from the transactions database but more importantly reciprocally when we create transactions we're going to have the ability to choose organizations as either the vendors of our expenses or the clients who are paying our invoices and other forms of revenue so these are the only properties of our organization's database and again your workspace may already contain an organization's database that's related to a variety of other databases in which case you can just add the properties for the accounting system but for demonstration's sake we're just creating a new organization's database with these simple properties and because we're just using this simple version of an organization's database we don't need multiple views other more kind of widely used organizations databases are likely to need multiple views but we're just going to have one default view here and what we're going to do is we'll sort by the organization property because it is the best practice to apply a sorting rule to every database in your workspace and then we'll just hide the transactions property because you're never going to really need to view those from this view of your organizations and so for our accounts database we will rename the title property accounts because this is a collection of accounts and then we'll convert this multi-select default tags property to a single select property and we'll rename that to type and remember we're going to be working with three different account types we're going to have bank accounts which will be applicable to almost all instances of this accounting system but then some businesses will also have payment gateways and those are going to be used to facilitate credit card and ach transactions and then businesses might also have invoices for which they'll need an accounts receivable account and that's going to store the balances of open invoices and then i like to use a date property to indicate the date when each account was last reconciled against its statement and then just as we did for our organizations database we're going to relate the accounts database to the transactions database and that's going to allow us when we're creating transactions to tie each transaction to the account whose balance it affects so we'll choose the relation type and then once again we'll search for transactions and we'll choose that version with the as afterwards and then once again we're not really going to need multiple views of this database so what i'm going to do is just as we did in organizations is hide this transactions property and then i'm going to sort by the type of account because it's nice to group those together if you have more than one account within each type and then after it's sorted by type we'll just sort alphabetically by the account name and then what i'm going to do is i'm going to input a sample account for each type and so we'll have the accounts receivable account for the accounts receivable type and then we'll have checking as our bank account type and we will have stripe as our payment gateway so again i'm not applying icons to these pages and i'm also not going to take the time to apply coloring to these select and multi-select properties but generally i just use the default gray for options that don't necessarily need a color to indicate a certain meaning such as completion status and so for these bank account types or all of these account types i typically just stick to that default gray so with our properties established and our view rules set and our sample inputs established we have our accounts database created and we can move on to transactions so remember the transactions database is the hub of your accounting system where you log every event that affects your account balances and those transactions assume five types each of which has its own options in its category property so the first of those transaction types is going to be balance transactions and these are used to adjust your balance when conventional transactions are inapplicable they increase or decrease in accounts balance and categories of the balance type include opening balance and balance adjustments and then equity transactions are owner contributions and withdrawals that don't affect the businesses reported revenue or expenses so they also can increase or decrease in accounts balance and equity categories can include owners draw and owner's contributions and then expenses are the purchases of goods and services that are deducted from revenue to calculate net income and in this system expenses always reduce an accounts balance so example expense categories include sas transaction fees hardware and health care and then revenue transactions include full invoice amounts and other income prior to transaction fees and other associated expenses and so in this system revenue always increases and accounts balance and also worth noting is that revenue for an invoice is registered when the invoice is issued not when it's paid so the selected account is accounts receivable and then when the invoice is paid the amount is transferred from accounts receivable to the funded account so example revenue categories include invoices issued and other income and then lastly transfers migrate assets from one account to another and each transfer is going to include two transactions one of those transactions will be for the origin account and then there'll be another transaction for the destination account so example transfer categories include invoice payment and payment gateway payout so within our transactions database we can start by renaming our title property transaction because this is indeed a collection of transactions and then this default multi-select property will rename title generator and this is going to be a formula property that automatically generates a value that we can paste over into the title property and we'll configure that formula once the other properties are in place so next i like to have a date property to indicate the date of the transaction and then we're going to have our type property with the types that we have discussed that's going to be a select property and we'll go ahead and pre-configure those options that we mentioned which are balance and equity expense revenue and transfer now i do like to use colors for these items and the reason is because each of these types are going to have a category and i like to create colors that correspond with the categories so the categories that correspond with each type will be colored the same way as their respective types but for demonstrations sake i'm not going to spend time coloring all of these properties but you will see those in the template so then we can go ahead and add that category property and remember there's going to be a selection of categories for each type of transaction and those will kind of vary with the nature of your work i'm going to make sure that this is properly spelled category and so i'm not going to populate all the options but we'll add a few when we create some demo transactions but i already mentioned a few sample categories and you can just kind of add them as you create your transactions as well so then we are going to establish the relationship betw between the transactions database and the accounts database now that's already in place because when we were creating the accounts database and configuring those properties we established that relation with transactions so here's the reciprocal relation property so all we need to do is rename it and each transaction that we create is going to be connected to an account and affect the balance of that account so this is where you choose the account from the accounts database and then we're going to have the payee property and the pay is going to be in the case of an expense it's going to be the vendor that you're paying and in the case of revenue it's going to be the client or the customer that's paying an invoice or providing another form of revenue so then we're going to create a new relation property that payee property was already in place it was the reciprocal from the organization's database when we established the relation with the transactions database when we were configuring those properties but we haven't yet established the relation between between transactions and invoices so we're going to do that here by adding a relation property we'll call it invoice and we will choose relation and we can search for invoices and we have those parentheses in place that make it easy to find and choose and in some cases they're actually going to be multiple invoices for a transaction so let's make that plural and then i like to have a text property for additional context so in some cases you'll have transactions where you want to make a note for reference later and it might not be applicable to any of the other properties in place so in this case you can just add additional helpful contacts that might help an accountant when he's filing your taxes or you when you just need to reference your old transactions so then we're going to have two number properties we're going to have a money in property and we're going to have a money out property so for transactions that are increasing the balance of your account [Music] you will insert the value in money in and then for transactions that decrease the balance of your account you'll apply that to money out so these are going to be again number properties and you're going to format them in your currency so in my case that's going to be the dollar and then for each transaction i like to continuously monitor and update the status so i'm checking my bank accounts and my payment gateway accounts to see when those transactions have completed processing and as they kind of traverse the cycle of a transaction i update the status so what i do is i have a status property with select options and i do use colors for those typically i won't apply the colors now but the status options that i use generally are complete processing in preparation and so what in preparation i use that for situations where i'm kind of aggregating transactions within a payment gateway in preparation to transfer to a bank account in preparation for a payment gateway payout transaction and then a planned transaction would be like a monthly transaction that you have planned for later in the month but it has not processed yet and then speaking of planned transactions i also like to use a frequency property where i can easily manage transactions that occur monthly and annually and lastly we're going to have another formula property called balance effect and what this is going to be used for it's going to serve a few different purposes but what it's going to do is inform the accounts database that roll-up property that's going to calculate the balance of the account it's going to reference this balance effect property to know what value to use for that calculation so basically this balance effect property is going to check to see if the transaction is planned and if it's not it's going to subtract the money out from the money in and if it is planned it's just going to use the value of zero so i'm not going to discuss the details of this formula i'm just going to paste it but it does just use an if function and there are plenty of other resources for how to use the if function and other formulas and if you reference this template you'll have the ability to open up this property and see exactly how that formula is composed as well so this balance effect is going to be your currency so we'll format this as a dollar as well so this gives us all of our transaction properties and then we're ready to populate the title generator so again this is going to be a formula and i'm not going to explain the details of the formula and all of the functions that it uses but basically it aggregates a bunch of the other properties to generate a value that's always going to be unique for every item in your database and that's really a best practice you want every value of the title property to be unique across all of the databases in your workspace there are a variety of advantages to that so you're never going to really need to look at the value of the transaction property it's just technically a sound name because it follows that best practice so as you create transactions and populate all of these properties this title generator property will automatically kind of grow so that when all of the information is in place you can copy this value and paste it over to transaction and you can if you're creating a bunch of transactions at a time you can copy them and paste them in bulk which makes it really easy and with automations powered by the api you could actually have this title property populate automatically without that manual copying pasting but it's really no trouble to do it manually and it really is a worthwhile exercise in order to follow those best practices so with our transaction properties in place let's look at a few sample transactions to give you a sense of the various transaction types and the first one will be to establish an opening balance so your bookkeeping is unlikely to begin with empty accounts and with the balance type and then an opening balance category you can establish your starting balance for each account so the transaction property will populate from the title generator once the other properties are in place but for the date we can just choose the date of june 8th 2021 as sort of an arbitrary date and i'm going to go ahead and unwrap the contents of these cells to keep everything nice and clean here and then for the type of the transaction we're going to choose balance and i'm going to go ahead and give that a deliberate color we can do that as we go here with the types in categories we'll make the balance type brown and then the categories that correspond with that balance type will also be brown so in this case this will be opening balance and we'll color that brown and as i mentioned you might also have a category called balance adjustment for kind of ad hoc adjustments to account balances so then for the account let's choose our checking account and again we're choosing the account from the accounts database and then we're not going to need a payee or invoices or additional contacts for this opening balance transaction but we can give it a money in amount which will be that opening balance so we can just call that ninety six thousand seven hundred and fifty dollars the status will go ahead and establish as complete so that this transaction will be excluded from filters that show only non-complete transactions and we'll go ahead and color the complete option green because that's a nice indicator of a complete status and then we can remove frequency here and we can see that our balance effect is automatically populated with that opening balance of 96 750 dollars so another common transaction is going to be a recurring monthly expense and in this case we can use the example of a zapier subscription so again we'll populate these transaction titles after we've completed all of our demos we'll use the title generator property to do that but for the date of this next transaction we can just choose a couple of days later we'll choose june 10th and this is going to be a transaction of the expense type and we'll create a new category that falls within the expense type we'll call it sas and we'll give it the same color as the expense type to demonstrate that coordination and then for the account once again we'll use checking we can just copy and paste from the above transaction and the payee this time we're going to choose from the organization's database zapier so zapier doesn't exist as an organization within that database yet so we'll create it straight from this database we'll type the name zapier and choose to create it here at the bottom and zapier has then been added to our organization's database and then for additional contacts we'll just type automation service which tells us what sort of service we're using within that sas category and this time rather than money in we're going to include money out we'll have a 50 dollar subscription we'll go ahead and label the status complete which we can copy from the above transaction and then we're going to add a frequency of monthly to this recurring subscription and i'm going to go ahead and color these that default gray because there's no real need to have a color associated with these currencies and you can see that the balance effect of this transaction because it's an expense is minus fifty dollars so our next few example transactions are going to be related to invoices and the first one is going to be when an invoice is issued now you indicate revenue from an invoice at that point when it's issued not when it's paid so the first invoice oriented transaction we're going to create here is going to be an invoice issued transaction that's going to be the category we use within the revenue type so we will just choose a couple of days later from our previous transaction we'll choose june 12th and the type of transaction here is going to be the revenue type and we'll go ahead and give revenue a color of green to demonstrate incoming money and then the category of that revenue property is going to be invoice issued and we'll also make that green to coordinate with the associated type and then the account is going to be accounts receivable accounts receivable is going to hold the balance of open invoices so we'll search for accounts receivable within the accounts database and choose that account and then we'll create a new payee this is going to be the client who was issued the invoice so we'll just create a new client called blue ribbon sushi and choose to create a new item there so whereas zapier would have been a vendor within the organization's database blue ribbon sushi is going to be the client so if we go in to that organization's database we can actually indicate the category for each one which could be useful as that database grows much larger so then we're also going to create the invoice and there are no items within the invoices database yet you'll typically create invoices when you create these invoice issued transactions so we'll just call this invoice 101 and we'll choose to create it as a new item within that invoices database so then for money in we're adding money to our accounts receivable we'll make this a four thousand dollar invoice we'll go ahead and give it the status of complete because we're not going to need it to appear in any sort of pending or planned transaction filters and then this may be a recurring monthly invoice in which case we'll go ahead and give it a frequency of monthly which will add another opportunity for managing monthly invoices within nice filtered views so this gives us our revenue from this issued invoice 101 to the client blue ribbon sushi and so because this invoice issued transaction receives the revenue when the invoice is actually paid by the client it's logged as a transfer from the accounts receivable to the account that was actually funded by the payment so that's going to include two transfer transactions one for the accounts receivable where the money will leave that account and then the other for the account that was funded so we'll add two new items here and let's say that the invoice was paid on june the 14th we'll add that date to both of these transactions and they're both going to be transferred transactions so we'll add transfer there i like to use yellow for transfer transactions and then the category can be invoice payment so we'll create that within the category property and give it a color of yellow since invoice payments are associated with the transfer transaction type so the first of these two transfer transactions is going to be for accounts receivable and then the second one let's say the client paid directly into the checking bank account so we'll add checking within the account of that transaction so the payee is going to be left blank because we only need to include that client with the invoice issued transaction with the revenue transaction there's no payee necessary for these transfer transactions but once again we will use in the invoice associated with these transfers it will allow us to keep all of our transactions nice and orderly for future reference and then sometimes within additional contexts it's helpful to indicate the method of payment a lot of clients will use a service like gusto or ripple so you can add that information here for future reference and then what we'll do is we'll have within the money out property that four thousand dollar invoice value for the accounts receivable because it's leaving accounts receivable and it's going to be a deposit into the checking account so the balance effect then will be four thousand dollars leaving accounts receivable and added to the checking account and let's say the client pays but the transaction hasn't fully processed we'll go ahead and just give that a value of processing within the status property and i like to use yellow for processing and then another type of revenue transaction will be non-invoice income a lot of businesses will not always issue invoices for the income that they receive from customers and clients so that will be another category of revenue transaction so of course as always we'll be pasting the value of the title generator property into the title property once all of these are populated but we'll create a new transaction here we'll give it a date again a couple of days later and it's going to be another revenue transaction which we can paste from the previous revenue transaction and for the category you could either have just kind of a generic other income category like you see in a lot of accounting tools or if you're often receiving income from a specific source then you could create a category dedicated to that so template sales is one that i often use and we'll color that green to associate it with the revenue type and then i like to group the categories by color within the list here so this may be a payment that's received within a payment gateway the customer may be paying online through the payment gateway stripe to purchase these templates so we'll use the stripe payment gateway as the account here and the customer let's say is unc chapel hill which is always one of my favorite examples to use and we'll create that because it does not yet exist within the organization's database and then we may have a money in amount of 200 from this payment and this 200 is going to be applied to the stripe payment gateway account so typically what you would do thereafter is create an expense that corresponds with this transaction and that would be the fee charge to buy the payment gateway for this transaction we'll do that next so we'll go ahead and just mark this complete for simplicity's sake there is no frequency we can see that the stripe account has received 200. so then what we can do is add a new line item here we'll choose the same date we'll make this an expense and we'll add a new category that we'll call transaction fee and we'll color it red to correspond with the expense transaction type and the account here is going to be stripe because we're going to want to deduct this transaction fee from the balance of the stripe account and the payee here will be stripe as well because this is the vendor that's charging the fee so stripe while it is an account it's not an organization in the organization's database it's not a vendor yet so we'll go ahead and create that here and just as a note we could indicate that this is fee for payment by unc and let's say that the transaction fee associated with this just for simplicity's sake we'll call it an even five dollars we'll call it complete and we can see that we had a 200 deposit and then a 500 deduction from the transaction fee so that would give us a balance of 195 dollars in our payment gateway account specifically stripe and that would be available for a payment gateway payout which would be a transfer from the payment gateway account stripe to the destination account such as the checking account and then for our last sample transaction we will create an equity transaction which is when an owner contributes or withdraws equity from an account so we will create our last sample transaction here we'll choose a couple of days later and then for the type we're going to choose equity and i like to use the color blue for equity and we'll make this and owners draw which is going to reduce the balance of the account and we'll color this blue to match the equity type and then the owner is going to be drawing from checking account in this case so we'll paste that from the previous transaction we don't need a payee or invoices we might mention that the owner is drawing for personal taxes and then for money out we can say that the owner is drawing ten thousand dollars we'll go ahead and mark it complete and we can see that the balance effect is taking ten thousand dollars from the account so with all of these sample transactions in place we can very quickly highlight all of the values of the title generator property and bump over to the transaction property the title property and paste them and that gives all of the items of this database a nice unique value which once again is a best practice in notion it's really important that all of the items across all of your databases have a unique value within that title property so you can get a sense for how this transactions database goes to be quite expansive over time but like i've said filtered views keep it easy to manage and you can filter your views by transaction type by account by status and other properties so let's quickly walk through three views that i most commonly access and the first one is going to be a view of expenses so we're just going to filter the transactions database by the expense type so what we'll do is we'll add a view and we'll call it expenses and tables are really easiest to use for all transactions views i like to sort all of my transactions views by the 8 property and then we will apply a filter so that we see only the transactions where the type is expense and then from there we can kind of selectively display and hide properties so we're going to want our two title properties our date property we can actually hide the type property because we're going to know that all of the transactions in this view are going to be expenses but we're going to want to keep the category property we're going to want to keep the account and payee properties we can hide the invoices property but we will keep additional context expenses are always going to have money out so we can hide money in we'll keep status frequency and we don't necessarily need to see that balance effect so we'll hide that as well and this gives us a nice view of all of the transactions within the expense type and it displays only the properties that are relevant to that particular expense type so another transactions view that i like is a transfers view so what we'll do is we'll take this default view that's displayed here we'll click on that and we will rename it transfers and like i said i like to sort all of my views by date so we'll do the same for this view and then we will filter to display only the transactions where the type is transfer so once again we'll selectively display and hide the properties keeping only those that are relevant to this particular type so we know that the type is going to be transfer so we can hide that we're always going to have an account selected but we do not need the payee property these transfers often will impact particular invoices so we'll keep that we want the opportunity to add additional contacts we'll be working with both the money in and money out properties here the status property and then frequency may be relevant for your transfers view but typically i don't assign a frequency to transfers so i'll hide those as well as the balance effect and that leaves us with this nice view of the transfers database and then lastly what we'll do is we will keep a view of the transactions that are not yet completed so you can get a quick look at all of the transactions that are still processing so we'll duplicate one of these views and we'll rename it just non-complete and because we duplicated we're already sorted by date but we're going to want to adjust the filter to display the transactions where status is not complete and then for the properties we're going to want to come back we want to keep type because we will be working with multiple types here we'll also be working with payees and frequencies as well and then we can keep that balance effect hidden so we're really going to want to see all of our properties here because these processing transactions are going to include a variety of transaction types so here we can see that we are left with our two sample transactions that are processing and as those transactions complete we can update their status here and they'll be filtered out of this view so those are some common views that i like to work with and some other ones are filtering by invoices so you can see only the transactions that are affecting invoices you might want to view for other income i like to have a view dedicated to equity and balance so kind of merging those two transaction types and then also an administrative view that just includes all transactions pretty much unfiltered but still sorted by that date property and then we're also going to be able to view transactions by account when we create the template in the accounts database so at this point we can finalize our invoices database so we'll go back to our accounting databases page choose that invoices database now remember invoices are created when logging invoice issued transactions and then all the properties in this database are going to be automated through roll-ups and formulas and you can see here that first invoice that we created in that sample transaction so i'm going to delete these blank entries keeping that 101 and then i'm going to unwrap these cells to keep everything kind of nice and tidy here and then we can get into some of these properties so i'm going to rename the title property to invoice id and then we already have our relation to the transactions database it's that reciprocal property that was created when we established the relation from the transactions database so i'm just going to rename that property to transactions and then we are going to want to populate the client who is issued the invoice and so this is going to be a rollup property to configure that roll up what we'll do is we'll choose that transactions relation and what we want to do is for the related transaction we want to pull in the payee and just show unique values and that's going to give us the client for this particular invoice and then we want a date issued property and for this property i'm going to add roll up in parentheses because we're going to have a date issued that automatically pulls in that date but it's not going to be formatted in a very nice way so we'll hide this version of it and then use a formula to format it the way that we want so we'll choose the roll-up type and once again choose that transactions property as the relationship and we want to pull in the date for the transactions and we know that that first entry for the related transactions is going to be the date that it was issued so we'll choose earliest date and you can see that this isn't really a very nicely formatted date so we will hide this and what we're going to do is create another date issued property without those parentheses it'll be a formula and we're going to use the format date function to format the date that we want i want to explain that in detail i've got other resources about that so i'll just paste it here but you'll have access to this in the template you can see that this makes the date a little bit easier to read and we'll be sorting by these dates as well so we're going to take a similar approach to the dates completed we'll create a date completed and then put rollup in parentheses to indicate that this is the rollup version of that property and once again we'll choosing we'll choose transactions as the related property and date as the property to retrieve and in this case we want the latest date so this will be the last transaction that was processed for this particular invoice which would be the date that it was completed and once again i'm not going to explain the formula in detail but we will create a date completed property as a formula that will reformat that date in a much friendlier way so then we're going to have an issued amount property which will be a rollup and to configure that we will choose that transactions relationship and for this we want to retrieve the balance effect and we want the entry that has the maximum balance effect and that will indicate the full amount of the invoice so we'll choose the maximum here as the calculation and then we're going to have a property called paid or canceled so this would be kind of the amount that was paid or if it was cancelled it will be the canceled amount which would be basically the value of the invoice reversed and so this is going to be a roll-up and for this we want to sum all the values within the money out property of the related transactions and a lot of these properties will be hidden we're just basically making these configurations so that the formula properties can reference them to make their calculations so then we're going to add a property that pulls in the categories for all of the related transactions so we'll call that transactions categories and the reason that we do this is so that we can detect whether the transaction has been cancelled and that'll be important to performing certain calculations so as always we'll choose the transactions relation in this case the property we want to pull in is category and we'll keep show original as the calculation and then we're going to create another formula property that displays a particular status of the invoice based on other properties we'll call it status we'll choose formula and i'm going to paste this formula and not explain it in its entirety here but basically what this formula does is it detects whether the transaction has been cancelled in which case cancelled will be the status and then if it has not been cancelled it'll check to see the extent to which it has been paid in which case it will return either unpaid or paid or partially paid and then another formula property is going to display the amount of the invoice that has been paid so basically it returns the value of the paid cancelled property if the invoice has not been cancelled and we'll format that as our currency and then somewhat similarly we'll have an amount outstanding property we can just call it outstanding and if the invoice has not been canceled it will deduct the amount paid from the total amount issued to return the amount outstanding which we will format in our currency so that completes all of our sophisticated and automated properties within the invoices database and we're ready to establish a couple of different views now what i like to do with invoices is basically just have two views one for all invoices and then another only for the invoices that remain open which means they're either unpaid or partially paid so for all of the views there are a few of these properties that are used only for calculations that can be hidden so that's going to include our transactions relation we can hide we can hide these two roll-up properties that are just used to inform other formula properties so that's going to be that one and then the date completed roll up and then we can hide our paid cancelled property and we can hide the categories and then for both of these views we're going to want to sort by the date issued and so in this view they'll be exactly the same but the only difference will be that one will be filtered so we'll add a view here and the way that notion works is sometimes you just create a temporary view because what we're going to want to do is duplicate the default view we'll rename it to all invoices and then we'll duplicate all invoices and we'll rename the duplicate open invoices and we'll delete that temp property so then in our open invoices view we just want to add a filter to display only the invoices where the status is unpaid or status is partially paid so when we have invoices that are either unpaid or partially paid in other words they're open they'll display here in this view so that nearly completes all of our databases the last step we need to take in the invoices is to go back to the accounts database and we can add the balance property and so all this is is another rollup property that we can call balance which is the rollup type and what this is going to do is we'll choose the transactions relation and we're going to retrieve the balance effect property for all of the related transactions and then we want the sum of all of the balance effect values for the related transactions and that's going to return the final balance for the account and so when you reconcile your accounts and you compare the most recent statements you'll do so using this final balance as well as the individual transactions for that account and so if you want to view transactions by account what we can do is create a template of this accounts database and that template is going to automatically filter the transactions database to display only the transactions for the account that you're viewing so the way that'll work is we can come into this little drop down within the new button and choose new template and we'll just call it account you can call it kind of like the name of your company followed by account and then down here within the body of the page we can add a linked database and what we want to add is a linked database for the transactions database and of course this is going to be our version with the as in parentheses and so what we want to do is we want to filter this transactions database for the account that we're creating and so what notion allows us to do is to choose the template we can filter by the template so when the template is used the filter is replaced with that new account that you're creating so we'll come in here and we'll add a filter and we want the transactions where the account is the account that we're creating so we'll choose the account property and then the top option here is going to be the template so we filter by this template and again when we implement the template when we create an account using the template this value here is going to be replaced with that new account so we'll be filtering for the transactions that are related to that particular account and of course none of the transactions currently will be related to this template but when we create an account and create transactions for that account then we will be able to see the transactions that are related directly within the accounts so then we'll just kind of rearrange and and reconfigure the properties here to be most helpful so we can hide the account property because we know that the account is going to be the account that we are viewing and so we can just kind of rearrange here we can hide balance effect because we don't typically need that date of course is crucial and i'm going to go ahead and sort by that date property so you can see here that this is a pretty close reflection of the other views that we configured for the transactions database and you can create multiple views of this linked database within the account so you might want to view all expenses for example by your checking account but for now we'll just have this one view for you to evaluate your transactions by account so with this configured we can go back and open up an existing account and choose that template and we do that we're going to see all of the transactions specifically for that account sorted by date and so forth which with all of these useful properties in place now we're probably going to want to hide some of these properties up here in the list at the top of the page you can do that ing to your preference so whether or not you utilize this system by learning how it works you'll understand advanced principles of notion that you can apply across all of your uses and like i said you can access the template from notion market it's linked within the video description and if you encounter any roadblocks as you develop the system yourself don't hesitate to tweet it william nut you