1) See this method for counting unique instances in your found set: How to count the number of unique occurences in field.
3) Use your existing summary field, but design your report layout to include a sub summary "when sorted by location". Put your totalQuantity field in this sub summary part and sort by your location field and you'll see a total for each lcoation in your found set.
Thank you for your quick response.
I made the suggestions you proposed, and they worked! two quick followup questions:
For 1) the cFraction method will only appear when sorted by LocationID be design, which works well enough for internal purposes. However some of the reports are sorted by the Location's Name (stored in a related table) to be more human-readable. I can't choose that field for a Calculation obviously. What would be the best approach to allow sorting of the data in other layouts by different Fields?
For 3) The solution works great for generating reports! Awesome, the only issue is we also generate distribution sheets with summed quantities once again keyed to a different field in a completely seperate table. So, I guess like above. I need a way to have that total 'show up' correctly in it's field, not just when sub summarized. Does that make sense?
Am I right in assuming I need to push the bulk of the work to scripts for the other layouts?
1) You can sort records by a value in a related table and you should be able to specify this field as the "break" field in getSummary.
3) Not really, In what field of what record would you put such a number? Please describe those distribution sheets as you can print or PDF any number of different reports from this type of layout to get the document that you need.
Sorry, I don't think I explained where I am getting stuck very well:
1) The layout which is sorted differently "LayoutA" (by name instead of location number) still is based on the underlying "Deliveries" table where all these fields are located and that another layout "LayoutB" generates most of our reports from. When I trying to sort by a breakfield that isn't in the Deliveries table (in this case the Location Name) cFraction just appears blank.
I found this link which has given me some more workaround leads: http://help.filemaker.com/app/answers/detail/a_id/3614/~/the-getsummary-function-can't-reference-related-fields
3) On the distribution sheets (currently generated in Access, migrating to FMP) we have 2 columns with summed quantities per location, 1 contains the totals for the previous issues distribution, the other contains the year to date totals for the current edition both of the same productID, just a different portion of the deliveries, determined by circulation dates in another table. So, the distribution sheet layout needs to have 2 columns drawing data from a the same related table, but a both are summarizing a different subset of that data, currently I have the layout based on a "Location" table which has information like the Name, Address, etc...
I'm assuming to have the quantities for both editions displayed side by side I need a column like "Previous" and a column "Current" where the TotalQuantity by Location will be stored by script(?), then sorted by a completely unrelated field with the delivery order.
I'm still having some issues with visualizing the different structure between FMP and Access, but just can't see how this can be accomplished without scripting at the moment. In Access we had two queries "Current," "Previous" joined to the query for the Distribtion Sheet.
I've attached an sample sheet because I'm failing to explain this well. The numbers "2010/11 Delivered" column would be the totals of the found set between the distribution dates for that year, with the "2011/12 YTD" would be the totals by LocationID of the found set between our initial distribution and today. These are for the productID "148" only. The "Delivery Order #," "Delivery Address" and "Location Name" are all in the Location table. The "LocationID" exists in both the Location and Deliveries Table and is the field all of the data hinges around.
Thank you again for your help so far. It has been really informative especially in illuminating some of the differences between the two.
1) if you change the sort order, you must change the break field to be the same field. This may require multiple sets of these calcualtion and summary fields in order to get the unique counts for each possible grouping you use in your reports.
3) It makes sense to base this report on Locations in FileMaker as well. You have two basic options when setting this up in FileMaker 11:
a) Set up a single relationship between locations and distribution, but use a portal filter expression to limit the records to the specified date range. A single row portal with your summary field that you already have can then be used. You'd set up two such one row portals and each would filter for a different date range.
b) Set up two relationships between locations and distribution. This requires two table occurrences of the distribution table in Manage | Database | relationships. This makes for a more complex relationship graph, but then do not need to put one row portals on your layout--you can just put a copy of this same summary field from the two different table occurrences to get the totals for each date range.
Both A and B methods would use global date fields to documented the needed date ranges. By giving the fields global storage, you can specify the dates once and they will apply for each location record in your found set.