Dynamic Filter - Based on list of data values

[ropra]

Hey Community,

I hope you're doing well!

I have been struggling with creating a dynamic filter for a long time - and decided to reach out to the community.

Find below the attached sample workflow.

The goal is to create a dynamic filter based on a data field. Ideally, I would love to have a dynamic "contains" filter but even if I can reach a dynamic "equals" or "in" filter, that would be of great help.

Let me know if you need any more details.

If you can explain the creation of such macros or guide me to any material, it would be helpful too.

[RWvanLeeuwen]

Hi @ropra ,

The workflow you have can be modified slightly to adjust for your needs. I have attached my solution for weekly challenge 215 for you to reverse engineer.

Using the summarize (twice) and crosstab, you can select fields and after that you can get a list of selected values. This exploits the following Boolean filtering statement: [Field] In ("list", "of", "things")

And lastly, and I'm sure you're aware of this, this app can be saved as a macro and subsequently used in your workflow.

Best,

Roland

Infotopics - Benelux partner of the year

Empowering business through analytics. See, Understand, Act!

[RWvanLeeuwen]

Best,

Roland

Infotopics - Benelux partner of the year

Empowering business through analytics. See, Understand, Act!

[DavidP]

I have followed roughly the same approach as @RWvanLeeuwen.

I used your macro as a starting point, so there were a few thing I had to change.

1. It needs to be a standard macro rather than a batch macro, as you don't have a Control Parameter input

2. Change the way the data is presented to the List input

3. Created Record ID and a new field called New Date, which I split to rows so that there is only 1 year per row

4. With this I can now use the IN statement in the filter tool formula.

5. Remove Dups created by the Text to Rows

Hope this is useful.

[ropra]

Hey Roland,

Thanks for such a prompt reply!

Actually I'm using version 2018 and can't use the attachments you shared - any way you could share the same for 2018 version.

Best,

Rohit 

[DavidP]

Here is my macro saved as 2018 version.

[RWvanLeeuwen]

Hi @ropra ,

You can open the workflow file I sent you with a tool like Notepad (or Notepad++) and edit the version info directly in the text.

Hope this works out for you 🙂

Best,

Roland

[ropra]

Hey David,

Thanks a lot - this is super helpful.

One last question is that the list doesn't show updated options if the data is updated i.e. 2009 is not shown as an option if it's present in the data. How can I change the macro to get that feature?

Best,

Rohit

[DavidP]

Hi @ropra,

That's a very good question. I've played around with a number of options, but could not get it to work.

Even when I convert it to an App, I have to run the App as a workflow first and then run it as an App for the list values to be updated.

Perhaps a question to throw back out to the Community.

[DavidP · Accepted solution]

This is the closest I can get. The listbox in the macro uses a separate macro input as a source for the list.

The workflow has to present the data to the macro in the right format, which is why the summary and crosstab tools are needed in the workflow.

This does update the list options based on the data in the text input, but only AFTER the workflow is run.