How to structure base for tracking finances with split transactions

Hi

I have a large base to track the finances in a project and have the same challenge. 

An invoice comes in for say £2,000 - which needs to be assigned to say 3 different budget categories. 

I got round this by adding the invoice three times - once for each budget line and adjusting the amount accordingly - then grouping by the invoice number so that the finance department can see it’s one invoice but three budget categories. 

a little clunky but hope that helps. If anyone has a better way I’d love to know!

Andrew

A “Line Items” table is a really effective way to handle this. It’s a junction table that brings together the Invoice Number (and any lookup/rollup fields you need from your Invoices table) and the Category (with any lookup/rollup fields you need from your Categories table). 

miniExtensions has (I think) an extension that allows you to enter the line items at the same time as you’re entering the invoice, which might be smoother than entering the invoice into the Invoices table and then creating the associated line item records in the Line Items table? I’ve never used it, but I do love their other extensions.

Good idea

Mini extensions I find is so expensive though

You can certainly do it without. Like the value of everything, the value of miniExtensions is judged by the use one gets out of it. But that’s another topic entirely 😁

I took a look at miniextensions bc I'd never heard of them and almost fell over at the price tag lol.  Maybe for a large enterprise but certainly not for my personal finances haha!  Good to know what they offer for the future, though - never know what needs may arise!

So this is the direction I have headed.  I've got the junction line items table linked to my transactions table.  Then I created an interface with a button that triggers an automation which creates a blank record in the line items table linked to the currently loaded transaction.  I've got the line items table displayed as a grid so i can make the split really quickly with a minimal number of clicks!  My next step is to add the automation functionality that adds a single blank line item every time a new transaction is created since I know I'll need AT LEAST one line item for every transaction.  Automate that first step and then use the button for adding additional splits.

Then I've gotta figure out how to deal with all the summing of all the things into a useful budget report, haha!

Thanks Andrew!  When I first had the idea to do a finances base in AirTable, this was my solution.  It's functional, but as you said, clunky.  Hoping I can flesh out a better solution - if I do, I'll let you know!

Once you have this, rollups to sum everything shouldn't be too difficult!

RE: "I know I'll need AT LEAST one item for every transaction."    ...why not just structure your main table with both a line item amount and split line records, then configure a formula field to use the former when there are no splits or the sum of the latter when there are split lines?  That approach might help reduce complexity in the splits table and conserve the total number of records in your base.

I have actually employed exactly this strategy for dealing with the amount of the transactions - debits need to be negative but I couldn't remember to enter the negative sign haha - so I made the entry field be positive and then used a formula to change it to negative.  Also, when I import transactions from my bank (which I'm doing manually for large updates), they import debits and credits in two separate columns, so I used my same formula field to combine all 3 fields into one that can be used for calculations.

Now, as far as line items go... If I have bucket links in both the transactions and designations tables, how would I handle rollups?  As I have it rn, I simply roll up the designations amounts and that tells me what I've spent in each category which I can then use calculate how much of my budget is left for the month.  But if amounts are stored in two locations, I guess I'd have to roll up two different fields and have a third that combines them?  How do you see that working?

Ah, I get how you're rolling up category spending from that line-time table, so having a one-to-one transaction-to-designation record is expedient. Am unsure how I'd otherwise structure that, but a thought is that you could set the spending categories up in their own table, then link to the category from either the single transaction or multiple line-times. Then roll up spending on the category records.