Not being an accountant or bookkeeper, it's not immediatly obvious to me when I read your post how, if you were working this out with paper and pencil, you would compute the Liabilities, owners Equity, etc totals that you need to compute the total equity.
I can make a few general observations and that may be enough to get you unstuck here:
The balance sheet works ok with the get summary asset field: getsummary(summary asset; Type) in the sub-summary when sorted by type.
Actually, if you were to place the summary asset field in this sub summary layout part you'd also get the correct sub total. I point this out to illustrate an important point:
The same summary field when placed in a sub summary part will compute a sub total. When placed in a grand summary, it will compute the total fo all records in your found set. If you refer to a summary field in a calculation field, it's as though you placed the summary field in your grand summary part, it returns the total of your current found set. This is were GetSummary comes into play as you can use it to access the subtotals from your sub summary parts by referring to the same "break" field in the getSummary function as you specified as the "when sorted by" field in the sub summary part.
Thus, if you can get the individual totals that you need for your total equity, in the sub summary part, you can create a calculation field to add and subtract these values to get the answer you want.
After writing the below paragraphs, I've figured out the two things I think I need. When I run the P&L, how to I capture the net profit from a summary field with a variable? Do you have any idea why my date ranges aren't working?
Thank you for your response. I'd like to clarify a little more and see if I'm just missing it. I think I've been working on it too long.
Basically you run a profit and loss statement on one layout. Input a date range (which I haven't figured out yet), perform a find of the income and expense accounts in that range and the result is your net profit for the period. The net profit works right now because the expenses are recorded as a negative (revenues are recorded opposite - credit is an increase whereas for expenses a debit is an increase) so the summary function automatically subtracts it from the income.
On the balance sheet layout you perform a find of the same date range and asset accounts then subtotal, liability accounts and subtotal. The difference would be the owner's equity: Owner's Equity = Assets - Liabilities. I would then need to add any equity the owner put in, and draws he pulled out, and add the net profit from the aforementioned profit & loss statement.
Assets = Liabilities + Owner's Equity + Revenue - Expenses - Draws
where: Revenues increase Owner's Equity
Expenses decrease Owner's Equity
Draws or Dividends decrease Owner's Equity
You haven't indicated how you are trying to use your date range. Here's the general idea, let me know if this does or does not work for you.
First you need a field of type date for your dates where you want to specify the date range. This seems obvious, but a lot of folks use text fields and don't realize that they have. This field must show "date" as the field type when you check it in manage | Database | Fields.
Next you enter find mode and specify a range of dates in this field as your find criteria or you use wild cards as part of the criteria.
Date range example:
entering 1/1/2012...1/31/2012 will find all records in the Month of January 2012. (Assumming MMDDYYYY date settings.
Wild card example
entering 1/*/2012 will also find the same records
Both methods can be used in manual finds or in scripts.
Since the dates are located in the transaction table I'm not sure how to do it. The layout is based on the account table.
The principle is the same regardless of the table, but there are two basic areas where date ranges come in to play. Performing a find--what I gave an example of in my previous post--or matching records via a relationship based on a range of dates. That is also possible using two date fields to define the range and inequality operators to compare the values to the same date field in the related table.
You'll need to tell me exactly what you need to do here before I can post any additional help.
It's like trying to search in a portal, a date find only looks at the first related record but return them all. The dates are in the Journal table. I'm doing a balance sheet based on accounts so that's why the layout is based on the accounts table. A bank account (which is one record in the Accounts table) will have many journal entries.
If I switch the layout and based it on journal entries, the date range find works. The problem with that is the report shows the account balance multiple times, one for each entry.
Please remember that I have no idea how your layouts are designed.
a date find only looks at the first related record
That's not quite correct, if you specify criteria in a field from a related table, the find does look at all the related records, but you are performing a find for the layout's table of record, not the related table so if you have even one related record the layout's record to which it is linked is included in the found set. Thus, if you specify a date range of 1/1/2012...1/31/2012 in the Journal's date field when performing a find on the accounts table, you'll find all accounts records with at least one journal record in the specified date range, but any portals, calculations etc will ignore this criteria once you return to browse mode and each account record found will link to all Journal records regardless of the specified criteria used in the Find.
It comes down to what you are trying to find and why. Do you want to find Account records or Journal records? Do you want to find all records with at least one journal record in the specified date range? (That's what you should be getting now) or all such records but portals, calculations etc must only refer to related records in the specified range?
All of the above is possible, I just need to know what it is that you are trying to achieve. A screen shot of the layout might help.