What you describe is typically referred to as a BOM or Bill of Materials table set up as part of an item's manufacturing spec.
You should be able to put your summary field directly on your layout, not inside any portal and it should report the correct total. That assumes that you define the summary field as a field of the BOM table as a "Total Of' summary field and that your field for recording the number of parts is correctly defined as a number field.
You can also define a calculation field in the layout's table that uses Sum ( BOM::Parts ) and it will compute the same total. This last option is a better one to use in cases where you are editing the portal quantities on the same layout as it will update more smoothly as records and quantities change in the BOM portal.
Thanks for the input, but it still isn't working. Yes, I'm creating a BOM and here is what I have.
- Table called Master (with fields for all the widget details)
- Table called BOM (with three fields ... ItemNum, RMNum, Quantity)
- Table called Materials (with details on individual BOM items, including a Description)
- Relationship between Master and BOM on the field ItemNum
- Relationship between BOM and Materials on the field RMNum
On my layout, I have details from Master displaying. I have a Tab Control for a variety of things, one of which is a BOM Tab.
On the BOM tab I have a portal with three columns. BOM::RMNum, Materials::Description, and BOM:Quantity.
I created a Calculation field, Master::Total with SUM(BOM::Quantity). I have placed that field within the portal, on the tab outside the portal, and on the main layout outside the tab. In all three cases the Total is always that of the first BOM item and not a sum of all the items listed in the portal.
My portal is set up to use Active Row State (which it defaulted to, so I didn't change it), no sort, and no filter. The filter is in the relationship.
I tried creating the calculation field in the BOM table as well, but that gives an identical result.
Probably something painfully obvious, but I'm just not seeing it.
I'm not seeing it from here either. The calculation field should be defined as a field in master and it should sum the quantity field from the BOM table. The field should not be placed inside the portal.
The thing to be careful of are your table occurrence names and the sum calculation field's "evaluation context". Table Occurrence names are the "box names" found in Manage | Database | relationships. When you select a name from "show Records from" in Layout setup (or in the new layout wizard) you are selecting the name of a table occurrence. When you select a name from "Show Related Records From" in portal setup, you are selecting a table occurrence name. When you select a name from the context menu inside Specify Calculation, you are selecting a table occurrence name. And the name to the left of the :: when you set up a calculation such as Sum ( BOM::Qty ) is also a table occurrence name.
So if you have a relationship such as:
Master::ItemNumber = BOM::ItemNumber
Then the layout's table occurrence and the specify calculation context should both specify "Master". The portal's table occurrence and the name inside the sum function, should both specify BOM. If they specify something else, such as another occurrence of the same table located elsewhere in your relationship graph, your calculation field will not evaluate correctly.
And make sure that you have set up a field of type calculation, not a number field with an auto-entered calculation as that approach will fail to automatically update when you edit the BOM records and their quantities.
Just now getting back to this project, but your help has been greatly appreciated. The calculation field now sums the portal ... sorta. :)
When I enter a new related record into the portal, the calculation field is not updated (it's a sum, giving a total). I am using an unstored calculation field. If I enter several items into the portal, navigate away, then navigate back the calculation works fine. If I update an existing value in the portal, the calculation updates just fine. It only fails to work when ADDING a new record to the portal.
I tried using a script onvalidate to refresh the object (calculation field) but that didn't seem to help.
You need to commit the record. This can be done simply by clicking a blank area of the layout.