3 Replies Latest reply on Oct 23, 2012 1:27 PM by philmodjunk

    Filter Report using Custom Layout



      Filter Report using Custom Layout


      FileMaker Pro



      Operating system version

      Windows 7

      Description of the issue


      I have a report that i am trying to filter using a custom layout. I need the user to select a year, as well as specify the Industries. Based on the values input, i will filter the report to only show data for the selected parameters. At this time, using Global Fields and Local Variables, i am able to filter the report when the user specifies a year and ONLY 1 Industry. If the user selects multiple values for Industry, the report does not show any data. Please advice what i am missing here and any guidance or direction is greatly appreciated!



        • 1. Re: Filter Report using Custom Layout

               First thing that you have missed is that you posted your question to the section of the forum intended for reporting possible software bugs. Had you clicked the FM Pro Forum Tab at the top of this screen before posting your question, you likely would have gotten a quicker answer and it would have been seen by more people.

               You don't tell us HOW "multiple values for industry" are selected, but it sounds like you have a check box group in a global field. If so, you can set up a a script that loops through the selected values generating a separate find request for each selected industry. This will find all the selected industries for the specified year.

               Assuming that you have two global fields: Globals::gYear and Globals::gSelectIndustry, here's how your script might look:

               Enter Find Mode [] --> clear the pause check box
               Set Field [YourTable::Year ; Globals::gYear ]
                  Set Variable [$K ; value: $K + 1 ]
                  Set Field [YourTable::industry ; GetValue ( globals::gSelectIndustry ; $K ) ]
                  Exit Loop if [$K > ValueCount ( Globals::gSelectIndustry ) ]
                  Duplicate Record/Request
               End Loop
               Set Error Capture [on]--->keeps "no records found" dialog from interrupting your script
               Perform Find []

               This script will only work as written if gSelectIndustry and gYear have global storage specified.

               Values selected from a group of checkboxes are stored in the specified field as a return separated list of values in the order that the check boxes were clicked. If you use a different value list format and the user selects multiple values by holding down the shift key, this script will still work as that also generates the same return separated list of values.

          • 2. Re: Filter Report using Custom Layout

                 Thanks PhilModJunk. I can't belive i made that mistake. Anyway to move this to the correct forum?

                 Yes, i am using a Drop Down List, where the user can select multiple values by holding down the shift key. Your script is perfect and does what i need. I had arrived at a similar code by looping and using -

                 Set Field [YourTable::industry ; MiddleValues ( globals::gSelectIndustry ; Get(RequestCount);1 ) ]

                 Obviously, i got the above looking at forum posts on similar requets. So,

                 a. Is using GetValue better than MiddleValues?

                 b. And, if i wanted to now omit records as well, after the loop is completed, how do i do that? I tried using another Perform Find with Omit criteria but it did not work. Neither did Constrain Found Set nor Omit Records




            • 3. Re: Filter Report using Custom Layout

                   a) Filter, right, left and middle values functions all append a return character onto the end of the data they return. Sometimes this can be ignored, sometimes it creates a real issue. getValue does not do this.

                   b) your script can use the Omit Record script step to turn a request into an "omit" type request to omit all records matching the specified criteria. Whether in a script, or in manual finds, make an "omit" request the last request for your find.