How to structure base for tracking finances with split transactions

[Ashley_Jackson]

Hello all!  You guys have been so helpful in the past, I've got a new question to share tonight!  I am working on a base to track finances.  We categorize all our expenses (and income) and then budget for those categories.  I need to assign a category to each transaction I import into the base.  Occassionally, we will have a single transaction that purchased items from multiple categories, for instance we bought clothes and groceries at Walmart.  Clothes and groceries are separate categories.

How would you guys structure your base to make recording these transactions as smooth as possible?

Thanks!

[Andrew_Davies]

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

[pressGO_design]

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.

[Andrew_Davies]

Good idea

Mini extensions I find is so expensive though

[pressGO_design]

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 😁

[Micki_O_Neil · Accepted solution]

If I were doing this without any extension, I would have a "Line Items" table with an automation that duplicated and linked every invoice I entered, assuming most of the time one invoice is one category. Then, in the cases where I had to break out line items, I would duplicate that record and enter the right adjusted data.  You could even have a field with number of line items to tell the automation how many records to create. (That would be a much trickier automation, though.)

[Ashley_Jackson]

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!

[Ashley_Jackson]

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!

[Ashley_Jackson]

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!

[Micki_O_Neil]

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