I'm having a bit of a logic block that I think is directly tied to the way I'm thinking about the underlying data for my AppSheet app.
My app has 4 tables: Account (account info), Transactions (financial transactions per account, by date), Balance History (running balance histories per account, by date), and Net Worth - which is supposed to be a running total of Assets minus Liabilities per date. This Net Worth table can then be sliced and diced to display trends, by timeframe (weekly, monthly, quarterly, annually, etc...) about that data.
I've been wracking my brain around this for weeks and I've settled (based on observing other solutions) that I will need to create a synthetic table (Net Worth) and have rows, by date, with the running total of Asset balances and Liability balances on that date. The Accounts, Transactions, and Balance History table already have the relevant reference columns established - but I'm at a loss on how to construct the Net Worth table.
From a very high-level pseudocode perspective, I'm trying to do something like:
- Create rows in the Net Worth table for ALL the unique dates in the Balance History table (this would indicate there was a balance change of some sort on that date)
- Retrieve the most recent balances for that date from all Asset Accounts and all Liability Accounts. Sum each and store in the Net Worth table.
- Calculate Net Worth for this row's date ((Sum of most recent Asset Balances for this row's date) - (Sum of most recent Liability Balances for this row's date) and store.
- Resulting Network Work table resembles: { Date, Assets($), Liabilities($), Net Worth($) }
There is no natural reference between rows in the Net Worth table and the Balance History table. The Net Worth table is in reality derived from the Balance History table.
I actually considered just creating virtual columns in the Balance History table to derive the Asset and Liability sums per each date in that table, but the circular logic of looking up the relevant accounts in that table, per date, and storing them in the same table just really got my knickers knotted. And other solutions (including the source GSheet - Tiller) use their own synthetic tables for Net Worth. But their solutions are very custom to displaying, trending, and filtering in the source GSheet - not very useable for an AppSheet app.
Thoughts? I'm suspecting I'm treading into the land of actions and automations - but I'm hitting a block on how to accomplish this.
LinkedIn
Twitter
