A summary field that computes the total of your calculation field that uses the Get Summary functions would appear to do exactly that.
If you want separate totals, write calculation fields that return the desired max value and set your summary field to total that calculation field.
That's the thing, I want only one total but the one that ignores the quantities that aren't the max of reusable stuff. Also, the GetSummary fields then end up getting added together and multiplied by the price so I'm totaling the calculation fields.
Post an example of your calculations. I believe I gave you a solution in general terms:" Write a calculation field that returns the max value and refer to it in your summary field."
But I'm getting a bit lost in the generalized descriptions of the problem. Specifics would help clarify things here.
Maybe I am not giving enough background information. I have many lab activities for kids to do. Each lab has materials that go with it. Some materials are consumable, some are reusable. When I assemble a book with the labs, I need to
1) separate what is consumable and what is reusable. To do this I have 2 fields that multiply an initial quantity by both, reusable field which contains either 1 or a 0 or the same for consumable field. If I mark an item as consumable, then for that record there's a 1 in that field and 0 in reusable so this separates reusables from consumables.
2) I then get the total summary of consumables and a max summary of reusables. After this step I need to add the summaries together to get the total quantity to put into the kit (in some labs you can reuse cups but in others they get consumed so I need to add both together so there's enough)
3) I use GetSummary to add the above summaries.
4) I use the resulting number to get the total price of each item in the kit.
5) Then I total 4 to (theoretically) get the total price of all components in the kit. But it doesn't work.
If I do only one lab in the "book" the DB returns right prices or if I use a combination of labs that do not have overlapping materials everything is great, but if I have a mix of labs that use same materials as the others, I get the doubling of the prices.
I need to see the actual calculation expressions for your calculation fields and I need to understand the table structure better so that I can see how you are summarizing consumables and reusables.
I think you have a report based on some kind of "materials" table where each record is one material needed for the lab activity and it is either flagged as consumable or reusable. I think you've grouped these records by the "Lab" that they belong to.
Am I correct?
I'm particularly interested in the actual calculation you use for step 3 and where you place it on your report. In a SubSummary part?
Just a text description of your report layout should do. (Did the description I gave in my last post match what you have?)
You can copy and paste text from the specify calculation dialog to a forum message and that's a lot easier than linking to a screen shot.
There, I put the screenshot of the calculations.
As far as the report goes, I have it sorted by Material category (Science, Household, Liquid chemicals, Solid Chemicals, Geology etc...)
The report doesn't have a "body" I have a subsummary by material so it calculates a total of the material, then, inside that subsummary there's the price for that item.
At the end thatr's a grant total which is where the total price of the kit goes.
I can't see your posted image. It just shows as an empty box with a red X. Simply pasting the calculation text from filemaker is all you need to do here.
Does a summary field set to be "total of" C_kit_plus_Refill_Total compute the right answer when you place the field in a grand summary part?
Here, I changed it up a little.
I now GetSummaries into separate fields and then do calculations on those fieldsso I don't do anything with GetSummary by mistake.
Here is a new screenshot:
Now the totaling field is the Grade_kit_price_total and if I pu it into the trailing grand total, it gives me the original price that was too high... NUTS
C_kit_final_sum is not being used for the time being. It is there for a different report. I will change it as soon as I get this report fixed.
Yeah, I figured out my last suggestion wouldn't work after I posted it. Since each record has the same calculation field for computing the group totals, a summary totaling this field won't produce the correct total.
I think I'm getting a clue here and if I'm right, you may want to restructure your report.
If we had a table where for each lab, we had one record for each type of material, we could link it to your existing materials table by material and lab ID fields. Then your report layout could be based on this layout with the sub-summary part changed into a body part. The max and total summary fields that you are using for subtotals in each subsummary would be replaced by calculation fields in this new table that use the Max and Sum functions to compute these totals. Then summary fields that total these fields should work.
The downside to this, is that you now have another table to create and maintain.