1 2 3 Previous Next 32 Replies Latest reply on Jun 23, 2010 11:52 AM by AMalyuchik

    Trouble adding up the totals in the report view.

    AMalyuchik

      Title

      Trouble adding up the totals in the report view.

      Post

      Hello everyone, To get right to the problem, the report pulls two summaries from a record. One is the total number of consumable items (1) and the other is the maximum of the reusable items(2). After that I use the Get Summary command and add them together and multiply this number (3) by the price of a single item. At the end of the report I need to total these prices to find out the final price of this kit. However, when I total the number (3) I get a final number that is regularly about 60% lower than what the program does with the "Total" summary field. I feel that what is happening is the program adds up not only the maximums (2) but all of the underlying amounts as well. So the question is: Is there any way to constrain the totaling summary to look only at the numbers that are being displayed in the report and ignore the dropped ones? Thank You

        • 1. Re: Trouble adding up the totals in the report view.
          philmodjunk

          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.

          • 2. Re: Trouble adding up the totals in the report view.
            AMalyuchik
            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.
            • 3. Re: Trouble adding up the totals in the report view.
              philmodjunk

              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.

              • 4. Re: Trouble adding up the totals in the report view.
                AMalyuchik

                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.

                • 5. Re: Trouble adding up the totals in the report view.
                  philmodjunk

                  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?

                  • 6. Re: Trouble adding up the totals in the report view.
                    AMalyuchik

                    How do you want me to show them to you? I can take a screenshot of the calculations, but how do I show you the report?

                     

                    • 7. Re: Trouble adding up the totals in the report view.
                      philmodjunk

                      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.

                      • 8. Re: Trouble adding up the totals in the report view.
                        AMalyuchik

                        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.

                        • 9. Re: Trouble adding up the totals in the report view.
                          philmodjunk

                          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.

                          • 10. Re: Trouble adding up the totals in the report view.
                            AMalyuchik

                            Let's try this


                            GetSummary ( C_kit_reuse_max ; Mat_ID_fk ) + GetSummary ( C_kit_consum_qty_total ; Mat_ID_fk ) + GetSummary ( Refill_Qty_total ; Mat_ID_fk )

                             

                            • 11. Re: Trouble adding up the totals in the report view.
                              philmodjunk

                              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?

                              • 12. Re: Trouble adding up the totals in the report view.
                                AMalyuchik

                                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

                                • 13. Re: Trouble adding up the totals in the report view.
                                  AMalyuchik

                                  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.

                                  • 14. Re: Trouble adding up the totals in the report view.
                                    philmodjunk

                                    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.

                                    1 2 3 Previous Next