1 Reply Latest reply on Mar 3, 2017 7:57 AM by philmodjunk

    Filtering Subsummary Count Results

    rbickings

      Good Morning,

      I have developed a FMP database for a local church over 2 years time, replacing an old windows only DB that was 15 years old.  This database is hosted inside with a FileMaker Server 15 server and has recently gone live.  I have a rather complex attendance module that works well but some of the reports they require are rather complex and I am reaching the limits of my knowledge.

       

      For instance, they would like to know how many people attending only 3 or less times in the last 6 months.  The data is all there, and if I use subsummary reports and Grand summaries (using the count function), I can get each individual over a date range with their number of attendance occurrences displayed.  I want to then further filter that to only show those whose attendance occurrences is say 3 or less.  I can not for the life of me figure out how to constrain the found set further...if anyone has any ideas concerning this, that would be deeply appreciated.

       

      Thanks,

      RBickings

        • 1. Re: Filtering Subsummary Count Results
          philmodjunk

          You can't use a find or constrain found set for this from the context of a summary report.

           

          There is a scripted method that works or you can come at this from a different direction that does support performing a find.

           

          You can use Sort Records to group your records for a summary report, and then you can re-order the sorted groups using the value of one of your summary fields--such as the one that counts attendance. A looping script could then loop through your records and omit groups of records for each person whose attendance subtotal does not meed the desired criteria.

           

          Two tricky parts:

          1. Use GetSummary to get the value of your summary field so that you get the subtotal and not the grand total.
          2. In your script, you will need to check the value of this subtotal first and if it doesn't match the specified attendance level, then omit all records for that person. If you check the subtotal after each omit, the attendance will appear to drop by one each time so only check once before omitting all records for a given person.

           

          To come at this from a different direction, if you could pull up a report with one record for each parishoner with a calculation field that counts attendance, you can perform a find specifying criteria for that unstored calculation field.