1 Reply Latest reply on Nov 3, 2009 2:11 PM by TSGal

    count number of sub-summary breaks

    della

      Title

      count number of sub-summary breaks

      Post

      I'm experienced with DBMS and SQL, but new to FMP 10 (Mac OSX 10.5).  I want to learn the best FMP idiom for counting sub-summary breaks. For an easily visualized example, consider a national sales database with the need to list sales, summarize by  state (the break) and then to produce a count of unique states.  The trick is that the report may be for a selection of sales - e.g. sales  > $200, or sales of a particular  product.

       

      I have seen people on this forum, and elsewhere, give 3 different solutions for the count of states.  Only one of which is half-way reasonable, although still awkward.  These 3 methods are: scripted loop to count states, summing a unique state flag via self join, and summing the reciprocal of the count within each group (state).  If you know what I'm talking about, you can just tell everyone now the cool fourth technique I overlooked.

       

      I trust nothing more need be said about scripted looping.  

       

      For those who don't immediately see the problem with the self-join, consider the case of using selected data.  The join won't always have a match, in the selected data, to the top of the join list.  So the "unique" flag will not be set for any sale in one or more state groups.   There are some ways to handle this for some selections - for  instance by sorting the join - but that's not universally useful.  I suppose you could create a custom join field(s), but this would require knowing all the ways in which the data could be selected.  Even if you could magically copy the last search parameters into a custom join, that wouldn't cover random "Omit Record"s.  Am I overlooking something that would allow the use of a self-join to flag one, and only one, sale in each state?

       

      So that leaves the reciprocal count technique.  This puts a data field on each sales record equal to the reciprocal of the count of sales in the group (state).  The sum of that field for each state is always 1, and the sum over the complete data selection is the number of states.  It works and it's fast.  But it seems like just such a kludge.  Isn't there a more elegant way?  A relational way?

       

      Thanks

       

        • 1. Re: count number of sub-summary breaks
          TSGal

          della:

           

          Thank you for your post.

           

          At this time, there is no SELECT.... DISTINCT... option in FileMaker Pro 10.  The methods you have are probably the best options.

           

          The portal into the same table works fine with all records, but when you have a found set that does not include the initial record, the count will be incorrect.  Therefore, using the reciprocal approach (1/total records for that group) and totaling the field is the best approach.

           

          If you were to build a relationship, it would be based upon the key field and the condition, and if the condition changes constantly, the portal would need to be updated.

           

          TSGal

          FileMaker, Inc.