12 Replies Latest reply on Jun 7, 2016 2:48 PM by erolst

    Limiting the contents of a subsummary report

    davehob

      I have a fairly standard subsummary report, which list related records for selected people. (The related records are ‘outcome scores’ – date, outcome indicator, and score obtained, held in an Interactions table.)

       

      The report has to show the total number of scores for each person, so I have a summary field in Interactions, defined as a count of Interactions::id.  So, with the report layout based on Interactions, I do a Find of all the Interactions meeting the criteria specified (date range, outcome type, etc.), then, when it’s sorted by People::id, I get a subsummary for each person, with the total number of scores.  All fine so far.

       

      But the users now want to be able to limit the report to only those people who have obtained a given number of outcomes – e.g. show me everybody who scored 3 or more outcomes (with the required number of outcomes to be selected by the user as part of the report selection criteria).

       

      I’m having trouble working out how to do this.  I’m thinking that I need to test the value of the summary field before it exists!  Any help with this mental block much appreciated,

       

      Dave.

        • 1. Re: Limiting the contents of a subsummary report
          erolst

          Dave Hobson wrote:

          I’m having trouble working out how to do this. I’m thinking that I need to test the value of the summary field before it exists!

           

          That of course is something you cannot do.

           

          What you can do is test if after the fact, by applying (a variation on the theme of) the Fast Summary technique:

           

          # [ define $LIMIT ]

          # [ find, sort ]

          Go to Record [ first ]

          Loop

            Set Variable [ $sizeGroup ; Get Summary ( sCountOfInteractions ; People::id ) ]

            If [ $sizeGroup < $LIMIT ]

               Omit Multiple Records [ $sizeGroup ]

               Exit Loop if [ not Get ( FoundCount ) or Get ( RecordNumber ) = Get ( TotalRecordCount ) ]

            Else

              Set Variable [ $nextGroupAt ; $sizeGroup & Get ( RecordNumber ) ]

              Exit Loop if [ $nextGroupAt > Get ( TotalRecordCount )

              Go to Record [ $nextGroupAt ]

            End If

          End Loop

           

          Written from memory, so untested in detail.

           

          Another idea would be to use a utility field (that you need to clear beforehand) into which you Replace Field Content the summary result, then constrain the found set to all records above the limit. Could be problematic in a multi-user environment, though.

          1 of 1 people found this helpful
          • 2. Re: Limiting the contents of a subsummary report
            siplus

            Very interesting situation, indeed. It can occur in RL and as you said you can't test a summary before it exists.

             

            What I'm doing - see attached - is the following:

             

            - ask for the minimum entries to be considered

            - build a list of the ID's to be taken out from the already found set, based upon how many of a specific ID we got

            - exclude them from the found set with a loop of constrain omit.

             

             

            Run the "ReduceFoundSet" script to get it.

            1 of 1 people found this helpful
            • 3. Re: Limiting the contents of a subsummary report
              davehob

              Thanks for these replies, both of you, and for the sample file, Siplus.  I'm going to be looking at this later today, and will report back.

              Dave.

              • 4. Re: Limiting the contents of a subsummary report
                davehob

                Thanks, Erolst, for this suggestion, which works really nicely.  For the benefit of anyone using it in future (and not in any way to imply criticism of your memory!), the step;

                Set Variable [ $nextGroupAt ; $sizeGroup & Get ( RecordNumber ) ]

                needs to be

                Set Variable [ $nextGroupAt ; $sizeGroup + Get ( RecordNumber ) ] (plus, not and).

                (Probably obvious, but worth mentioning for posterity.)

                 

                Thanks again, your help is very much appreciated.

                Dave.

                • 5. Re: Limiting the contents of a subsummary report
                  davehob

                  I've integrated both of these (similar) solutions, and both work well.  I'll decide which version to use when I test them for speed in the WAN environment.

                  I appreciate both of your inputs, which have been very informative and helpful.

                  Dave.

                  • 6. Re: Limiting the contents of a subsummary report
                    siplus

                    Please come back to us with the speed results !

                    • 7. Re: Limiting the contents of a subsummary report
                      erolst

                      Thanks Dave,

                       

                      thanks for telling me. That's a favourite bug of my own making when I don't pay attention. Need to keep +, & and and apart.

                       

                      In other programming languages, the + operator is overloaded and can be used for addition and concatenation.

                       

                      Anyway, glad we could help you.

                      • 8. Re: Limiting the contents of a subsummary report
                        siplus

                        I think I found a problem in Filemaker.

                         

                        I began testing my own solution for speed, so I created 1 mil records. What I discovered was a never-ending sorting party.

                         

                        The script step Sort Records by Field - which I was using in my example - toggles the keep records in sorted order option by himself to ON. It can't be specified in the script step at all but it's a nuisance. It's not documented, either, and it behaves that way in 13, 14, 15.

                         

                        Keep this problem in mind, always. I don't consider it a bug, but depending on the wind direction it will eventually smell like one.

                         

                        ----

                         

                        Please find attached a new version of the file, which has a corrected script, plus erolst's script, plus you can run both and get the execution speed.

                         

                        ----

                         

                        P.S. Erolst, GetSummary is the speed killer, me thinks.

                        • 9. Re: Limiting the contents of a subsummary report
                          davehob

                          Well, Siplus, you’ve set up a fine test in your demo file – thanks very much.  A comparison, both on your file and on mine (local and WAN), shows that erolst's version is quicker when the 'exclusion limit' value is low, whereas yours is quicker with higher exclusion limits.  The point at which they are approximately the same is at about 6. 

                           

                          Given that the GetSummary resolves to the same values whatever exclusion threshold is set, it seems to be down to your use of ‘Constrain found set’ vs erolst’s ‘Omit multiple recs’.  I'm not sure why that should be so.

                           

                          Thanks again for your help with this, and for the examples.

                           

                          Dave.

                          • 10. Re: Limiting the contents of a subsummary report
                            siplus

                            It's not the exclusion limit that counts, it's the records.

                             

                            I tried it the following way:

                             

                            deleted all records (preferably with FM15...)

                            created 100'000 records

                            Set to exclude less than 11 (correct final found set was 53'498 records)

                             

                            First run:

                             

                            S+: 8193 ms / EOS: 364552 ms

                             

                            Second run:

                             

                            S+: 7973 ms / EOS 364125 ms

                             

                            ---

                             

                            I'm not using constrain any more, look at the second pass in my last attachment post, it's implemented differently: I create omit requests then I fire the perform find just once.

                            • 11. Re: Limiting the contents of a subsummary report
                              davehob

                              That's quite a difference as the no. of records increases.  And sorry I missed the amended technique using 'Omit' requests.

                               

                              Also, I meant to say, a side benefit of all of this is that, because of the loop through all found records, it's easy to keep a count of the no. of people in a variable, with minimal overhead.  This has often been an issue for me, i.e. counting the no. of subsummaries.

                               

                              Dave.

                              • 12. Re: Limiting the contents of a subsummary report
                                erolst

                                Thanks siplis for doing the benchmarks. Time to get familiar with a new approach ...