10 Replies Latest reply: May 15, 2014 9:55 AM by Grant Perkins RSS

    VBA: Set a filter based on a passed variable value

    Mark Huston

      Here's a head-scratcher for the coders on this list. I'm using version 6 Pro.

      Using VBA, is there a way to pass a variable to a filter, that is, in effect, to create a filter "on the fly"?

      I'd like to create a model with a filter where ACCTNUM = "MyVariable". Then the user runs the code, and inputs the value for that variable, say 30. Then MyVariable = 30 is passed to the filter so that ACCTNUM = 30.

      TIA,

      Mark!

        • VBA: Set a filter based on a passed variable value
          Data Kruncher

          Hi Mark.

           

          If we can't set filter values dynamically, and I don't think we can (next version please?), then you may be able to build a solution which is a bit of a compromise.

           

          Because it appears that you want to work with account numbers, this might not be a good solution for you, just because of the sheer volume of data potentially created. But, if you can work within a range of filters, say a filter created for each of the most needed account numbers (hopefully not too many), you could prompt the user with a text box or a collection of radio buttons, and process the correct filter with a Select Case structure.

           

          You could dynamically generate a "Top 10" or "Top 20" or w.h.y. similarly. Usually these are top 10, 20 or 50, and not so much 8 or 14.

           

          Kind of a mixed static/dynamic solution, but a potential solution nonetheless.

           

          What do you think?

           

          Sandy

          • VBA: Set a filter based on a passed variable value
            Nick Osdale-Popa

            Very good question!  The COM object does not have an interface for setting a Runtime parameter (possible next version, or maintenance update??), the very thing I believe you are looking for.  Your best bet would probably be to use a Shell() function and pass the runtime parameter to the /set option.

               /set:[Field Name]=

            Sets a runtime parameter named to from the command line, instead of typing the values into a dialog box. If all runtime parameters are fulfilled from the command line, then the corresponding dialog is suppressed upon program startup  /quote

             

            [size="1"][ December 12, 2003, 03:44 PM: Message edited by: Nick Osdale-Popa ][/size]

            • VBA: Set a filter based on a passed variable value
              Nick Osdale-Popa

              Thanks for pointing this out to me Sandy.    

              The /set:[/b] command is only available in v7 of Monarch.

               

              I knew runtime parameters were introduced in v6, so I thought that the /set command was also available.

               

              My bad.

              • VBA: Set a filter based on a passed variable value
                Mark Huston

                Well, that's what I figured, and Sandy Data Kruncher's suggestion is the way I'm currently doing it. My actual issue was that a co-worker wanted to calculate the number of days to filter from a report by comparing the report date to today's date. My solution was to extract the number of days, and have a bunch of models, DAYS_OLD1, DAYS_OLD2, DAYS_OLD3, and so on ... maximum of about 10 days so this was workable.

                But this technique seemed awfully "kludgy" and ham-fisted, and it's unworkable if, for example, you had thousands of customers and wanted to filter by customer number. The other option, of course, was to export everything and filter it downstream in Excel or Access. So I had to check with you folks to see whether I was missing a more elegant technique.

                [font="courier"]

                NumDays = Int(Now()) - RptDt

                With MonarchObj

                    .CurrentFilter = "DAYS_OLD" & NumDays

                    .CurrentSummary = "TotalsByDate"

                    .ExportSummary (DestFile)

                End With

                /font[/quote]As for the /set command, that's really what I'm looking for. It's time for me to plead with the boss for an upgrade from version 6 to version 7.

                • VBA: Set a filter based on a passed variable value
                  Grant Perkins

                  Mark,

                   

                  I've read these posts several times now and suspect I may be missing something - brain getting addled?

                   

                  But I just remembered something from a few months back that was a V5 pro solution to a lack of 'Lookup Field' and I think it would work here as well.

                   

                  If you create a calculated field to compare to you date field and then make a link (Join) to an external file which has a single entry - that being the date (or whatever) that is the variable you need, you get a form of pseudo lookup value. Bring the field value into the records, do the comparison and filter on the results.

                   

                  As long as the user maintains this 'file' (A VB coded prompt to get a value and write the file?) before running the Monarch process you wouold have a mechanism for making the required comparison and processing on the result.

                   

                  Does my description make any sense?

                   

                  Grant

                   

                   

                  Originally posted by Mark Huston:

                  Well, that's what I figured, and Sandy Data Kruncher's suggestion is the way I'm currently doing it. My actual issue was that a co-worker wanted to calculate the number of days to filter from a report by comparing the report date to today's date. My solution was to extract the number of days, and have a bunch of models, DAYS_OLD1, DAYS_OLD2, DAYS_OLD3, and so on ... maximum of about 10 days so this was workable.

                  But this technique seemed awfully "kludgy" and ham-fisted, and it's unworkable if, for example, you had thousands of customers and wanted to filter by customer number. The other option, of course, was to export everything and filter it downstream in Excel or Access. So I had to check with you folks to see whether I was missing a more elegant technique.

                  [font="courier"]

                  NumDays = Int(Now()) - RptDt

                  With MonarchObj

                      .CurrentFilter = "DAYS_OLD" & NumDays

                      .CurrentSummary = "TotalsByDate"

                      .ExportSummary (DestFile)

                  End With

                  /font[/quote]As for the /set command, that's really what I'm looking for. It's time for me to plead with the boss for an upgrade from version 6 to version 7. /b[/quote]

                  • VBA: Set a filter based on a passed variable value
                    RalphB _

                    You could set up a runtime parameter in a calculated field and set a filter based on the value of the runtime parameter.  When you run the model, it will prompt you for the runtime parameter and have the filter set to select based on the value inputted at the runtime parameter.  This even works using vba code to run the model.

                    • VBA: Set a filter based on a passed variable value
                      Mark Huston

                      Well, I'll be darned. RalphB's solution is exactly what I was looking for!

                      I had done a bunch of formula-based calculated fields but hadn't yet worked with runtime-based calculated fields.

                      Now that I've been enlightened, I have a formula-based calculated field that compares the dates to the runtime-based calculated field that's populated with the user input, and the filter returns only those records matching the user input.

                      So I got a very nice holiday gift this year. Thanks, Ralph, and thanks to all who responded.

                      • VBA: Set a filter based on a passed variable value
                        Nick Osdale-Popa

                        I now have a project where the info in this thread is pertinent. Maybe this can be used as a Monarch Tip in the next newsletter: Dynamic Filters with VBA

                         

                        My VBA macro will write a .CSV file in the following format:

                        [font="courier"]JobLink,JobFilter

                        J02.113, J02.113[/font][/quote]In my Model I have a field called JobNum.

                         

                        I created an External Lookup called lkupJob:

                        File: jobnumber.CSV

                        Linked Columns: JobLink(external) to JobNum(Table Field)

                        Columns to Input: JobFilter (since you can't import the linking column - which would be better)

                         

                        I then created a filter

                        JOBNum=JobFilter

                         

                        Named it Job_Filter and set it as default.

                        Works like a charm!

                         

                         

                        The only drawback is when files are moved around, the external lookup will have to be re-connected.

                         

                        As stated, if there was a way to populate a runtime parameter with VBA, it would be an even cleaner solution.

                        • VBA: Set a filter based on a passed variable value
                          Bruce _

                          Nick & Ralph

                           

                          SWEET work arounds! I will have to try them out myself!

                           

                          Bruce

                          • VBA: Set a filter based on a passed variable value
                            Grant Perkins

                            Nice to know the lookup works well with VBA code.

                             

                            This seems like a good thread to mention that I have been playing with a project that requires the possible input of a number of parameters for the same field at run time (fortunately all 2 digit codes currently). I used the calculated run time entry field to supply the codes and then the Instr function in a filter to reduce my record selection to only those records that matched one of the entries in the string.

                             

                            The codes I am working with are simply the numbers 1 to 39. I had to add a leading zero to the single digit codes to make it work but that was not really an issue since the leading zero offered other benefits to the process.

                             

                            Clearly there are some potential limitations to this and it may not always be possible use the idea cleanly but in the right situation it could be useful. A short list of vendor or customer numbers for instance  - anything where the comparison can be unique ...

                             

                            Grant

                             

                            Originally posted by RalphB:

                            You could set up a runtime parameter in a calculated field and set a filter based on the value of the runtime parameter.  When you run the model, it will prompt you for the runtime parameter and have the filter set to select based on the value inputted at the runtime parameter.  This even works using vba code to run the model. /b[/quote]