6 Replies Latest reply on Oct 1, 2012 9:57 AM by TurtleKoala

    Loop through filters

    TurtleKoala

      Title

      Loop through filters

      Post


           Hi,

           I have a report that is a list with a few leading sub-summaries. The first of these is Department. I am trying to create a loop that filters the list by department and creates a PDF out of that filtered list. Essentially what I am looking for is a For Each loop. Any help is greatly appreciated.

           Thanks

        • 1. Re: Loop through filters
          philmodjunk

               Why do you need to do a loop? Why not use Constrain Found Set to filter your list by department? That will be many times quicker than a looping script.

          • 2. Re: Loop through filters
            TurtleKoala

                 Sorry, I was unclear. My plan was to do a constrain found set, but I wanted to make a script that would print the report for every department one by one without me having to run it for each department. I wanted to have a loop where in each iteration, I would constrain the set for a department, and then it would create a PDF of that report, and then it would go on to the next iteration in which I would constrain it for the next department.

            • 3. Re: Loop through filters
              philmodjunk

                   Ok, describe your table or tables that are involved. Do you have a table where there is one record for every department? If you do, you can loop through the records in that table. If you do not, I recommend such a table as it is likely to be useful in a number of different ways. If you really don't want to do that, you can define a value list based on your department field using the "use values from a field" option and then there are ways to put that list of values in a variable so that you can use it in a loop to do what you want.

              • 4. Re: Loop through filters
                TurtleKoala

                     The list that I am generating is based on a table called Feedback, which is linked to another table called Employee. Unfortunately, Department is not it's own table; it is just a field on the Employee table. I could create a new table, but I am curious as to how to implement your second suggestion, as I may want to perform a similar task for filtering by fields which do not necessarily merit their own tables.

                • 5. Re: Loop through filters
                  philmodjunk

                       Set Variable [$TheList ; value: valueListItems ( Get ( Filename ) ; "YourValueListNameInQuotes" ) ]

                       will load a variable with the items from a value list. You can then loop through the values in the Variable like this:

                       Set Variable [$TheList ; value: valueListItems ( Get ( Filename ) ; "YourValueListNameInQuotes" ) ]
                       Loop
                          Set Variable [$K ; value: $K + 1 ]
                          Exit Loop If [ $K > Valuecount ( $TheList ) ]
                          Enter Find Mode []
                          Set Field [Yourtable::Department ; GetValue ( $TheList ; $K ) ]
                          #Specify other criteria here with more set field steps if necessary
                          Set Error Capture [on]
                          Perform Find []
                          #Do what you need with this found set here, sort records, print, etc...
                       End Loop

                       With a table of these records a similar loop can be done without using the value list:

                       Freeze Window
                       Go to Layout [Departments]
                       Show All Records ---> Or perform a find for just certain departments here
                       Go to Record/Request/Page [first]
                       Loop
                          Set Variable [$Department ; value: Departments::DeptName]
                          Go To Layout [YourReportLayoutHere]
                          Enter Find Mode[]
                          Set Field [YourTable::Department ; $Department]
                          #Other criteria here
                          Set Error Capture [on]
                          Perform Find[]
                          #other stuff here
                          Go to layout [Departments]
                          Go to Record/Request/Page [next ; exit after last]
                       End Loop

                       Note:

                       I use get ( FileName ) instead of "your filename in quotes" because using this funciton ensures that the valueListItems function will still work correctly should you change the name of your file where it will return an empty list if you rename your file and use the file name in quotes without finding and updating that text in your function call.

                  • 6. Re: Loop through filters
                    TurtleKoala

                         Thanks, that's exactly what I was looking for.