Show total of matching records from related table

[lalcorn802]

I have a database for tracking budgets, amount spent, and balance. I have a layout that I show information about each account. I want to have a field that will total all amounts written out of that specific account. How can I show that amount in a specific field. For example I have an account for the warehouse supplies. I want to have a field that shows a total of all the purchase orders that was written out of warehouse supplies.

[Hare]

If you have stored the accountID in the purchase orders table, then

sum ( PurchaseOrders::Amount )

[lalcorn802]

The account number is stored in the purchase order. The purchase order is divided into 2 tables, purchase order info and items purchased. Both tables have the account number stored. So i assume I would want to sum the purchased items correct?

[Hare]

I don't think that's necessary, anyway it would require you to store the account ID in PO Items. See sample: you can total the PO totals.

SumOverPOs.fp7.zip

[lalcorn802]

Thanks for the help, but thats not what I'm really looking for. I have that part working ok. The screen I show above is a seperate table listing all the accounts. I want to show the amount spent out of individual accounts not the purchase order. The items sold are in their own table and it has the acocunt number in that table.

[Hare]

The items sold are in their own table and it has the acocunt number in that table.

All right, then you can sum over the items

sum ( POItems::amount )

the result will be the same as in my sample (see the Client layout: shows totals over all POs for that client). What I attempted to show was you can see the grand total over all POs even when the Account ID is not stored in the items.

[lalcorn802]

Do I have to have check to make sure the account number on the po items match the record in the account list, example

if (account list::acct_rel = po items::account rel (sum price); 0)

[Hare]

no, that's not necessary.