9 Replies Latest reply on Aug 27, 2014 7:25 AM by philmodjunk

    Sub Summary Report Help!



      Sub Summary Report Help!


           Hi all,


           I'm having a bit of a problem trying to get a sub summary report get the information I need.

           Everything is fine with the report, I get sub summaries for products as I expect.

           I have no body part in the report as all I want is a summary of each product code and the quantity

           However I need to calculate a hash total which consists of all the sub summary product codes added to the sub summary quantities. 

           I seem to be getting the hash total of all the underlying product codes behind the summary product codes and the quantities.

           Can anyone  point me in the right direction please?.


        • 1. Re: Sub Summary Report Help!

               When you refer to a summary field in a calculation, you get a grand total value not the sub total value of a sub summary part. To get a sub total value, use the GetSummary function.

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Sub Summary Report Help!

                 Hi Phil, thanks for taking the time, but I'm lost due to my own lack of understanding on sub summary reports.

                 I originally had a report that listed a sales persons daily transactions by product code and quantity

                 the hash total was very straight forward and calculated by adding hash_productcode Summary =Total of productcode (running with restart) when sorted by sales person

                 with hash_quantity Summary = Total of quantity (running with restart) when sorted by sales person.


                 I wanted to summarise this report to reduce the number of displayed transactions, in particular multiple transactions for the same product code.

                 That worked fine, I got single lines for each product code with a sub summary of the quantities using sub summary part when sorted by productcode.

                 I just can't seem to calculate the hash for the product codes, it's totalling every line item product code, not the single instance of each sub summary product code as I wish.


                 Any help or a more simple explanation would be gratefully appreciated .


            • 3. Re: Sub Summary Report Help!

                   You seem to be making an unusual use of the term "hash" which I've always understood to mean something quite different from a simple total or sub total. For that reason I hesitated to go into detail in my previous post--pointing you to the function that most often works for getting sub totals to use in a calculation. But GetSummary shouldn't be used with a running total summary field as such is not necessary.

                   What puzzles me is why on earth you would want to total product codes. It makes sense to total quantities, but product codes?

                   Can you provide a small sample of Product codes along with the "sub total" values that you want to compute from them? I still think that there's some detail here I am not understanding correctly...

              • 4. Re: Sub Summary Report Help!

                     LOL, Hash total is a term we use here when entering data into a legacy dos accounts system.

                     It was basically computed by adding all the product codes and quantities of a sales persons withdrawal from stock as they left to do sales calls.

                     This hash total was entered into the system prior to entering any of the items and quantities.

                     The idea behind it was pretty straight forward, if the operator made an error either keying the product code or quantity the operators hash total would not match the machine calculated hash total and would allow the operator to check their input.

                     Moving on to 2014 ... we now scan out these stock items through filemaker go and generate a day report for the sales person showing the products and quantities they took throughout the day.

                     This report was also used to key in the data to the legacy accounts system.

                     However, the sales people make several withdrawals throughout the day and I wanted to summarise their takings into a single sheet showing a summary of product codes and quantities.

                     This shortened list would then be used for keying into the legacy accounts system, hence I need a hash total.

                     I know a csv import is an obvious choice rather than keying, but it's not that straight forward.  

                     Product codes are generally 5 digits all numerical and quantities are generally to 3 decimal places (kilo weights).

                     In it's most simple form the hash total is calculated as follows

                      Code               Qty

                     51508              10.000

                     51516                 5.300


                     103024     +       15.300  = hash total 103039.3

                     Don't laugh, it's a legacy system in use since 1994, it works flawlessly but support at this stage is non existent .

                     My ultimate goal is to replace it with a more up to date financials package which is both filemaker friendly, native OSx or native filemaker even better, and does not reside in the cloud.

                     My hunt for such a package continues, meanwhile I'm getting to know filemaker by developing other in house manufacturing and traceability systems.

                     If you can point me in the right direction regarding my "hash total" or even a good financials package, I'd really appreciate it.

                     Thanks Phil :)

                • 5. Re: Sub Summary Report Help!

                       That sounds like something that can be produce with GetSummary and a pair of Summary fields that are not set up to be running total fields.

                       In FileMaker, if you define a summary field to total the product codes and a second summary field to total the quantities, adding the two in a calculation produces a "grand total" based on your current found set of records. If you are sorting to group your records and you need the sub total for each group produced by the sort, you can use a calculation with getSummary:

                       GetSummary ( sTotalProductCode ; BreakField ) + GetSummary ( sTotalQuantities ; BreakField )

                       In place of BreakField, refer to the field specified as the "when sorted by" field in the sub summary layout part that you use to show current sub totals for each such group of records.

                       Keep in mind these details:

                       The records must be sorted and BreakField must be a field in that current sort order.

                       the summary fields and BreakField must be fields defined in the layout's underlying table.

                  • 6. Re: Sub Summary Report Help!

                         Phil I have tried what you suggested but ...

                         Unfortunately it is still calculating the sum of product codes by adding all the underlying product code entries.

                         what I mean by this is, say I have in my table 3 transactions for 

                         Product Code           Quantity

                         21508                          10

                         21508                          10

                         21508                          15


                         I produce a sub summary report which shows

                         Product Code           Quantity

                         21508                          35


                         But my calculated hash total is showing 64559 which is 3 x 21508 + 35

                         Not 21543 (21508 + 35)

                         This is with the sort field and the break fields as you suggested.




                    • 7. Re: Sub Summary Report Help!

                           correct, that's what it would do. I had to go back and look at your previous post to see the difference between what you wanted and what you are getting. (In you previous example, you only show one instance of each product code.)

                           So a variation that will work for each subtotal is: ProductCode + GetSummary ( sTotalQuantities ; BreakField )

                           But that's still not your final version where you sum all quantities over all product codes but sum one only one instance of every product

                           Add a "count of" summary fields sTotalCount that counts your product code.

                           Then define a calculation field as ProductCode/GetSummary ( sTotalCount ; BreakField )

                           Then add yet another summary field that sums this calculation field, sTotalProductCodeReciprocal

                           sTotalProductCodeReciprocal + sTotalQuantities

                           Will total all quantities in your found set and add it to the total created by adding one instance of each product code in the same found set.


                      • 8. Re: Sub Summary Report Help!

                             Genius ...

                             Works perfectly, thank you so much Phil yes

                             I would never have thought to do it that way.

                             Much appreciation for your help on this and for all the effort you put in here on the forum, you are truly a huge asset to have. 

                        • 9. Re: Sub Summary Report Help!

                                It's not all my idea here. There's a long documented method for counting unique values by summing the reciprocal, I just modified that approach to compute a sum instead of a count. Please check you In box as I want to ask your permission on something that will take place outside of this forum.

                          Caulkins Consulting, Home of Adventures In FileMaking