4 Replies Latest reply: Dec 29, 2011 3:18 AM by Marcin Dworak RSS

    Changing filters on multiple variables simultaneously

      Hi,

       

      I have two tables like attached below (just an example) with two similar variables Continent for Client and Region for Person. What I would like to have is one list box that would allow me to change the filters on both variables in a way that if Continent=Europe then Region=Europe. Is there a way to set this up somehow? (and I don't want to create a new table with both Client and Person in it and one "location" variable). I know that it would be easy to have two list boxes and just change the filters on both of them but I would really appreciat if someone knew a more elegant solution (and I actually need it).

       

      Client     Continent

      ABC       Europe           

      XYZ        Asia

      DEF       North America

       

      Person   Region           

      John       Asia

      Mike       Europe

      Pete       North America

       

      Thanks in advance for your help,

      Marcin

        • Changing filters on multiple variables simultaneously

          You could probably do this with actions.

           

          Right-click the sheet and go to properties, then the triggers tab. Find your list box for Continent, and add an action. Make the action a 'Selection in field' action. make the field 'Region', and in the search string, create an expression to make it equal to the Continent selection. Something like

           

          =GetFieldSelections(Continent)

           

          Should work. If you have to deal with multiple selections, this may not work, but give it a try.

            • Changing filters on multiple variables simultaneously

              Hi,

               

              This does work to some extent because the trigger works when the list box is either activated or deactivated (depending which you choose) when the filter is on Contintent. So for example (lets say the triggers is set off when I deactivate the list box) if I set the Continent to Pan Europe I have to click somewhere on the report, outside the Continent list box, for it to be applied on the Region variable. If I use the trigger that's set off on activating the list box then I have to first deactivate and then activate the list box.

               

              Additionally it doesn't seem to work with multiple selections like you've said.

               

              Thanks,

              Marcin

                • Re: Changing filters on multiple variables simultaneously
                  John Witherspoon

                  I did this once with macros before, back before there were actions.  Here's an example:

                   

                  sub setMonth()
                  ActiveDocument.getField("Display Month").selectValues ActiveDocument.getField("Month").getSelectedValues
                  end sub

                   

                  It's triggered on select or on change of the field "Month", not on activate or deactivate of the list box.

                   

                  I'm sure there's some correct expression to do it with actions, which would be preferable.

                    • Re: Changing filters on multiple variables simultaneously

                      Hi John,

                       

                      Thank you, this was very helpful. The macro works for multiple selections as well. I've used Triggers under Document Properties and then in Field Event Triggers I've found my Continent variable and put the macro as an action for both "On Select" and "On Change" to ensure that the filters are execty the same on Region when I select or deselect values from Continent the list box (used your macro just changed the names of the variables):

                       

                      sub setMonth()
                      ActiveDocument.getField("Region").selectValues ActiveDocument.getField("Continent").getSelectedValues
                      end sub

                       

                      One important thing was to add the macro to Field Event Triggers and not Sheet Object Event Triggers (which only allow "OnActivate" and "OnDeactivate") - adding some explanation in case someone else has the same problem.

                       

                      Thanks and take care,

                      Marcin