Summing value fields dependent on a value in another field

[HarleyHacker]

I am trying to sum a value field dependent on a related value in another field. The related value is in another table, but I can put it in the same table as the value field.

I want to set up a Balance sheet, and I need to sum the "assets" and "liabilities" and then subtract the "liability sum" from the "asset sum" to calculate retained earnings.

[Fenton]

You need a relationship from the table where you want this balance to the other table(s). The thing is, you haven't mentioned what/where you are. So, I'll say you're on a Client record, and that Assets and Liabilities are separate tables, and that liabilities are entered as positive number.

[if they were the same table as each other, you could subtract them (if in same record), then access that "subtotal" from Client.]

So, from Client, you'd have a relationship to each of these tables based on Client ID. Then you can access the numbers via:

Sum ( cl_Assets::Amount) - Sum (cl_Liabilities::Amount)

[The "cl_" connotes that these are relationships directly from the primary table occurrence of the Clients table (on the Relationship Graph), in its table occurrence group (of connected table occurrences). These would likely be the main table occurrences for these tables, as they are always connected to a Client.]

The resulting calculation would be unstored, hence a little slow (though not so bad for one client; more so if you tried to get a balance for all clients). To make it stored, you would have to move into "transaction" processing, which is a whole 'nother level of design; and you'd need the above anyway, for checking if nothing else.

But I'm not a "proper" accountant, and there's more than one way to do this arithmetic.

[HarleyHacker]

Thanks for the response. "Asset" and "Liability" are the "Type" field tied to the value field to differentiate Assets and Liabilities. I want to sum the value fields by grouping them by the "Type" field.