Accounting p&l / bal sheet summary and date range help
I have an accounting solution built into my database:
Account: id, Type (Asset, liability, equity, A/R, A/P, income, expenses), name (bank acct, office supplies, etc), sum of related journal debits and credits, asset account balance (sum of related debits - sum of related credits) and vice versa, and summary field of the like.
Journal: related account id, related transaction id, debit, credit, and various failed calculation fields
Transaction: id, date, payee payor, memo
I need a formula to get the total equity (Assets = Liabilities + Owner's Equity + Revenue - Expenses - Draws).
I have a working P&L, and the net profit shows up fine. The balance sheet works ok with the get summary asset field: getsummary(summary asset; Type) in the sub-summary when sorted by type. But I don't know how to subtract liabilities in the trailing grand summary or subtract the net profit. I also can't seem to get a date range working properly. The P&L and Bal sheets are based on the Account table. I tried it on the Journal table but it seems to list records.
The script I use for P&L is: Go to layout P&L (account); enter find mode specify type: income, type: cogs, type: expense, perform find; sort records based on value list (income, cogs, expenses);
The script I use for bal sheet is: go to layout balsheet (account); enter find mode specify type: asset, type: liability, type: equity; perform find, sort records based on value list (assets, liabilities, equity)
Any help is appreciated. Thank you in advance.