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!

[jasonsamuels]

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.

[Ashley_Jackson]

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?

[jasonsamuels]

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.