1) Define a calclution field in Purchase Order Table as:
Sum ( PurchaseOrderItems::Item cost )
2) Define a summary field that computes the total of Item Cost in the Purchase Order Items table. Put this summary field on your Purchase Orders Table based layout.
If this is to total the items shown in a portal on the same layout where data entry can change the total thus computed, use the first option as it will update more smoothly. In that situation, the second option requires a refresh window script to update the total shown in the summary field.
Thanks for the quick reply.
How do I incorperate an If statement into the Sum (PurchaseORderItems::Item cost). I would like to be able to sum only the relevant costs (via the PO#).
Please explain what you mean by "relevant costs".
The sum function adds up the total of all related records so it will total all the values in Item Cost for a given PO. If you need to be more selective, you must either define and use a relationship that excludes the "nonrelevant" records or you add a calculation field with an If function to the line items table that returns the value of Item Cost for the "relevant" costs and is blank for those that aren't.
So in my purchase order table, I have the following data:
PO# Unit Cost
From the Purchase order table, if I have PO14 open, I want the total field to go through the above table and sum up all the costs pertaining to PO14 and not PO99.
So if I was to use the sum function, it would just sum all the values up so I need to konw how to create the calculation that will filter out the non-related records.
If this is a calculation field defined in the purchase order table, it will sum up just the item costs for that one purchase order. It will do exactly what you have requested here. The relationship is what limits the records summed to just those linked to a given purchase order.
Sorry for bringing up this older post. I've figured out why the sum isn't working. I've attached a database to this post called Test_Sums which contains two tables as listed above)
So the sum does not work if I have a Portal (with the option to create new records in the Materials table). If I switch off that option, than the sums work. Is there a work around for this problem?
The ultimate goal for me is to allow a user to enter in a purchase order with various items and have the total automatically update.
Unfortunately, you can only upload graphic files to the forum, so we cannot see your uploaded file.
The "allow creation..." option you describe should have no effect on whether sum does or does not work, so this statement is a tad confusing here...
You are correct that the "allow creation" option has no effect (I must have confused myself). After starting fresh again, after summing the total for the first time, any subsequent additions to the Purchase Order Items table will not result in an update in the total field found on Purchase Order table. Is there a way to have the total field update constantly?
The sum function will do that. Are you using a field of type number with an auto-enter calculation for this? That would explain the lack of updating here. If so, make it a field of type calculation and it should update correctly.
Awesome. That worked.
Thanks for the help!