Using customer filter "contains" for case insensitive filtering

I would like to use the customer filter “contains” to filter where user entered text is in a field, however the filter is case sensitive so “Manager” will not return “manager”. Is there any way to make the filter case insensitive?
As a workaround, if I create a calculated field with lower case only then can I force the text entered into the filter to be lower case only?

1 Like

Hi! I think you are spot on with the workaround you are exploring, i.e. add a lower case version of the field in your data set and define the filter based on that. If you encounter difficulties with performance, try physically baking it into the data as opposed to using a calculated field.

If you want to go completely fail-safe, you can explore creating a new parameter linked to a text box control and then using this parameter in a calculated field where you compare the parameter value in lower case form to your lower case field in the data set. For example:

{String Found} = 
        locate({Lowercase Text Field to Search},toLower(${pSearchString})) = 0
            OR ${pSearchString} = 'Enter search term' /* default value defined for parameter */
        ,'N' /* return N if keyword is not located or text box control has default value - i.e. user has not entered a search term */
        ,'Y' /* return Y if keyword is located */

And then use the field {String Found} as the filter field for your visual. It’s a bit heavy-handed but the benefit is that it will work regardless of whether the user enters “Manager” or “manager”.

Ideally, the behaviour for contains should be configurable for contains-type filters in terms case-sensitivity but until such feature is available, the above workaround should do the job.



It’s a case sensitive, so not possible to handle it.

you can handle it before the data upload in QS. because creating new lower case colum will increase your storage cost and decrease performance if yo using the large dataset. :slight_smile:

great solution!

I implemented this in my dashboard and this works great. But if I delete the text in the control, I get an error, that the calculated field has invalid arguments.

I tried to solve this with COALESCE, but this still does not work.

Do you know a solution for this?
The people in my company will not unterstand that they have to reset the filter, instead they will delete the search string. :smiley:

A couple of options come to my mind. Firstly, you could try using an empty string as the default value. That way if the parameter is set to empty string (either because nothing has been entered yet or users deleted their search string from the text box), the end result is the same, no filter would be applied on the visual. Obviously, this would mean you also need to use an empty string in the ifelse expression instead of Enter search term.

Alternatively, you could introduce a “reset” button on the screen. It could be an image within a narrative or just enlarged text in the narrative. The main purpose would be to set a navigation action on it that resets the parameter value to whatever is your original default.

Third option would be to hide the entire target visual to be searched using rules if the parameter value is an empty string and show instead a narrative with a prompt for the user to enter a search term. This last one is only possible in free form mode using rules:

1 Like

The empty string as default does not work, because the toLower function gives an error with empty arguments. I tried working with COALESCE and even using another calculated field with COALESCE(Parameter), but the error remains…

And I wanted to avoid the free form solution, because then I need to maintain every visual two times. :sweat_smile:

I will try the reset button. Otherwise I have to write a short explanation at the top.

Thank you for your help!

1 Like

I am hitting this same issue. In my case the dataset contains a load of blockchain transaction hashes (which I keep in lower case) but when people search they sometimes put some characters in upper case so no match is found. IMHO there either:

  • needs to be an option to make parameter setting to be case insensitive

OR - we need some kind of hook to be able to run functions on the search box entry field before it’s used.

The first option feels like the better one to me.