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.
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 .
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...
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
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 :)
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.
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
I produce a sub summary report which shows
Product Code Quantity
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.
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.
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.