8 Replies Latest reply on Dec 1, 2009 8:42 PM by user14360

    Searching in reports with sub summaries

    user14360

      Title

      Searching in reports with sub summaries

      Post

      Hi,

       

      I'm using Filemaker Pro 10 Advanced on Mac OS X, new to intermediate developer.

       

      In a report with sub summary parts, I'm wondering if it's possible to search within the subsummary part headings.  It seems that it's only possible to search via a field in the body section of the report.  Therefore if I want to search just for values in a field that is in one of my subsummaries (for example, I want to show only the people who are in a particular group, and the name of the group is in a subsummary heading field), I have to also include that group field in the body, which of course looks messy.  To get around this, I've been including the field in the body, making it invisible and doing the searching via a script so no user has to actually find the field and do the search themselves.

       

      It's all a bit cumbersome though.  Am I missing something? 

       

       

        • 1. Re: Searching in reports with sub summaries
          philmodjunk
            

          Yep, in find mode, the subsummary parts disappear as there's no "sort state" that FMP can use to determine whether or not to display the subsummary part. Sounds like a nice "new feature" to request though.

           

          "To get around this, I've been including the field in the body, making it invisible and doing the searching via a script so no user has to actually find the field and do the search themselves."

          If you are scripting the search, you don't need to actually place the field in the layout for the script to work.

           

          Here's a trick I've used since upgrading to FMP 10 (won't work with earlier versions):

          Put a button set to "do not print" in the subsummary part.

           

          Script it like this:

          Set Variable[$GroupID; Value: YourTable::YourGroupIDfield]

          Enter Find Mode[]

          Set Field [YourTable::YourGroupIDfield; $GroupID]

          Perform Find[]

          Sort[Restore] //sort on your "group" field to keep the subsummary visible.

           

          Now users can click on the button to "filter" the summary report down to just the selected group. The field "YourTable::YourGroupIDfield" does not have to be present on the layout in order for this script to work.

          • 2. Re: Searching in reports with sub summaries
            user14360
              

            Thanks for the response.  I'll give it a try.

             

            Two questions before I go and beaver away at it:

             

            Does this work when there is more than 1 subsummary part if I want to still retain those subsummary sorts once the find has been performed?

            Does this work in a subsummary report in which there is no body part at all, just the subsummary parts?

             

            Cheers,

            Hildy. 

            • 3. Re: Searching in reports with sub summaries
              Sorbsbuster
                

              That's a very neat GUI feature, Phil.  It is the same as right-clicking and selecting 'Find Matching Records', isn't it?  (I am not being critical - it is very neat - just wanting a technical clarification.)

               

              Maybe the user may want 'Constrain Found set'?  I'm thinking ahead to a sub-summarised report for a particular found year, say, that has a sub-summary for 'California'.  When they click the button they'll find every record for 'California'. No?

               

              Thanks for the inspiration.

               

              Alan.

              • 4. Re: Searching in reports with sub summaries
                philmodjunk
                  

                Sorbsbuster,

                 

                Hey I welcome constructive criticism--I've learned a lot here that way!

                 

                I just tried right clicking a subsummary report of mine. Find matching records is a "new to me" feature and hadn't considered it. It does part of the operation but loses the sort order where the button's script can include a step to sort the found records so that the sub summary parts remain visible.

                 

                Using Constrain Found Set in place of Perform Find[] sounds like an excellent idea for improving on my original suggestion.

                 

                Hildy,

                 

                "Does this work when there is more than 1 subsummary part if I want to still retain those subsummary sorts once the find has been performed?"

                More than one part for the same group of records? If so, then yes you just have to sort your records after the find (Or constrain found set) is performed so your new found set is correctly sorted.

                 

                "Does this work in a subsummary report in which there is no body part at all, just the subsummary parts?"

                It'll work, but if you have only one subsummary part, the result will be a single row of data in your report.

                • 5. Re: Searching in reports with sub summaries
                  user14360
                    

                  I've just realised that the thing I've been missing all along is that, as you said above, you don't have to have the field on your report in order to search in it via a script.

                   

                  I have been scripting the finds pretty much how you had it above PhilModJunk, but assuming you had to have the field on the report, and so putting it in invisibly.  However, in this particular report I have no body, as the report is a collection of subsummary parts, so it seemed that I couldn't do it that way.  But if you don't have to have the field on the report then that makes it nice and easy!

                   

                  • 6. Re: Searching in reports with sub summaries
                    user14360
                      

                    This doesn't seem to work after all, searching in a field that's not on the report.  

                     

                    It's annoying how you can't use variables in the scripted finds (other than using the 'set field' step).  Perhaps the solution is to use a global field where I set the search criteria I want into it, and then specify the find request to equal that global field.

                     

                    (It's possible that I'm missing something though - I'm going a bit too fast on it now because I have to go to work!  I'll revisit this later on and get back to any replies.) 

                    • 7. Re: Searching in reports with sub summaries
                      philmodjunk
                        

                      I use this method all the time.

                       

                      Compare my sample script to yours and see what's different. Note that I use a variable to capture the value before entering browse mode. Then use that variable to enter the find criteria after entering find mode.

                      • 8. Re: Searching in reports with sub summaries
                        user14360
                          

                        Hi again.

                         

                        Yes, that worked.  Thank you.  I had neglected to actually put the variable that I wanted to search by into the Set Field step.  It's always something small! 

                         

                        I never knew you could do a search without the field actually being on the layout.  Thanks again.