Filter tool and multiple list boxes

[tomek1988231]

Hi All, i have some problems with setting up filters that are using list boxes to populate values that needs to be filtered from database. When at least one value is selected in each list box it is working perfect:

field1 IN ("blue") AND field2 IN("flower") AND field3 IN("Paris")

Problem starts when there is no selection in at least one list box:

field1 IN ("blue") AND field2 IN("flower") AND field3 IN("")

I know why it is not working - due to empty last IN operator - but have no idea how to solve this. One thing important, I want users to be able to ommit one or more list boxes when selecting filters. Anyone can advise how to solve this?

[fmvizcaino]

Hi @tomek1988231 ,

One quick suggestion is for you to keep a dummy value in all filters as below:

field1 IN ("dummy","blue") AND field2 IN("dummy","flower") AND field3 IN("dummy")

And insert an additional comma in your list box tool as well.

Let me know if this works for you.

Best,

Fernando Vizcaino

[tomek1988231]

Hi Fernando!Thanks for the reply but I think I don't understand how this could work while I am having AND operator this will not result in correct output. I am wondering about replacing whole part for 3rd filter with single space, but then I'll get info about the error in Filter...

[fmvizcaino · Accepted solution]

Hi @tomek1988231 ,

You are absolutely right, it doesn't make sense what I just said. hahah but luckly I have a solution for you.

You need to connect all list boxes to the same action tool and create a string based on conditions. The whole idea is to create your conditions based in your list boxes selections ([#1] and [#2])

You will use the formula type of action and use this as the formula, for example.

IF [#1]='""'
then '1=1'    //dummy condition
ELSE '[Column A] IN ('+ [#1] + ')'       //[Column A] IN ("selection1","selecion 2")
endif
+
IF [#2]='""'
then ' AND 1=1'           //dummy condition with an AND
ELSE 'AND [Column B] IN ('+ [#2] + ')'            //[Column B] IN ("selection1","selecion 2")
endif

Ex: If nothing selected in your first list box: 1=1 AND [Column B] IN ("selection1","selecion 2")

If nothing selected in your second list box:  [Column A] IN ("selection1","selecion 2") AND 1=1

You can test your conditions and see what is really going on by clicking in debug. It will create a new workflow with all the replacement your action tools has made.

Best,

Fernando Vizcaino

[tomek1988231]

Many thanks Fernando, works great, just one thing I need to change - instead of ISEMPTY I've to use ' "" '. Have no idea why function was not working but '""' did job.

[fmvizcaino]

It was actually not working for me and I did the same, but maybe I've sent you the workflow before saving that part.

Since the list box produces literally the string "", it is not empty as I thought it would be.

Glad to help.

Best,

Fernando Vizcaino