Best Way to Design an Accounting Base with a Combined Income & Expenses Table

[MettaSong]

Hello AT Community,

I'm a "newbie" and have really appreciated all the ideas and suggestions I have found here. I have been trying to do something that I think should be pretty straightforward because I know how to do it in Google Spreadsheets easily, but I can't figure out what the best way to do it in AT is. I've searched the web trying to find an answer... and still haven't found what I'm looking for so I'm hoping a friendly person could get me on the right track.

I have an Accounting Base that has 3 main Tables

1. Income
2. Expenses
3. Combined Transactions (A table that is a limited # of Fields from Both the Income & Expenses Tables)

I know I "could" have the income & expenses in the same table and just create a custom view, but I prefer having the income & expenses in two separate tables. However, I'm having trouble figuring out the best way to create the Combined Transactions Table.

I know I could link the tables - and I like this option bc it syncs the 2 tables easily - but I don't want to have to individually add each record income and expense line, and don't want two separate Date or Name fields (one from Income, one from Expenses).

I also tried creating an Automation to automatically create a new record in the Combined Transactions table. This worked well bc I was able to map the individual Date field from the Expense & Income Field to the Combined Transactions Date Field easily.

However, I was nervous about having to rely on an automation to update these records regularly. It seemed like a link record would be a safer way to go. Also, because I am a newbie... I don't completely trust my automation setup skills and really don't have any scripting skills. Also, it seemed weird having another record of what is basically the same info but in a different location in the base.

So my question, what is the best route to take - should I use Link to Table, or create an Automation - or a combo of both?

Thank you for helping me get past this basic structural problem! I feel like I keep making this more complicated than it has to be.

[TheTimeSavingCo]

I vote for Link to Table (with an automation that handles the linking), with separate Date and Name fields and formula fields to clean up the data as needed (i.e. if "Income Date" lookup is empty, show "Expense Date" and vice versa)

[MettaSong]

Thank you @TheTimeSavingCo ! I love that idea of using the IF formula to combine the dates! Brilliant! 

Unfortunately, I'm struggling w/ creating an automation to handle the linking multiple rows on the Combined Transactions page. I've figured out how to do it manually, but haven't figured out how to link w/ automation. I'd be grateful for any tips. I know once I can overcome this - a whole new world will open for me in AT. Thank you!

[TheTimeSavingCo]

Hm, what do you mean when you say 'linking multiple rows on the Combined Transactions page'?  I was under the impression each record in "Income" and "Expenses" would be one record in "Combined Transactions", so if "Income" had 5 records and "Expenses" had 15 records, there'd be 20 records in "Combined Transactions"

If my impression is correct, I would create an automatioh that triggers when a new record is created in "Income", and its action would be to create a new record in "Combined Transactions" and link it to the triggering record.  I'd create the same automation for "Expenses"

[MettaSong]

Just wanted to follow up on your helpful advice. You were correct about the end result I was trying to achieve.

The point that I was tripping on was the data was already in Expense and Income Tables. I was able to create an automation that updated my Combined Transactions by creating a new Checkbox Field I titled, "Add to Combined Table." I used that marked checkbox as a trigger in my automation to make the automation run and move the info from the Expense or Income Table to the Combined Expense Table. Thanks for helping me work through this!

I don't know if there is an easier way to do that, but that was the way I found that worked for me when I have pre-existing data in my tables.