5 Replies Latest reply on Apr 18, 2012 3:33 PM by philmodjunk

    Sum of Field in Found Sets using Self-Join (not Summary Field)



      Sum of Field in Found Sets using Self-Join (not Summary Field)




      I'm trying to calculate three different Totals in a found set for a table that records deliveries of a multitude of products to a multitude of locations (so I need to summarize based on 3 criteria: Location ID, Product ID, Date):

      1. NumberOfLocations: Currently Summary(Count of LocationID)

      I can't figure out a way to have a summary field count only uniques, I think this has to be accomplished through a script/calculation field& self join but amd not sure the best approach given the other considerations below.

      2. TotalQuantity: Currently Summary(Total of QuantityDelivered)

      This works great as is! It is supposed to display the total quantity delivered in the found set (typically will be of one product)

      3. TotalQuantityOfLocation: 

      This is supposed to display the total amount delivered per location (as there is the chance that multiple deliveries will be to the same location and of the same product over time.)

      I have tried self-join AND summary field but not quite getting what I'm missing. The main issue with using a Summary is that I cant summarize by location AND product ID. The self-join Sum works, however it sums across all the records, not just the found set.


      Any help would be amazing! Thanks

        • 1. Re: Sum of Field in Found Sets using Self-Join (not Summary Field)

          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.

          • 2. Re: Sum of Field in Found Sets using Self-Join (not Summary Field)

            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?

            Thank you

            • 3. Re: Sum of Field in Found Sets using Self-Join (not Summary Field)

              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.


              • 4. Re: Sum of Field in Found Sets using Self-Join (not Summary Field)

                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.

                • 5. Re: Sum of Field in Found Sets using Self-Join (not Summary Field)

                  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.