Currently, Sigma’s Textbox control allows you to filter a table for rows that contain the input string if you set the control to Contains.
But what if you would like to filter a table for rows that match any one of multiple comma-separated strings?
This is possible by using the same Textbox as a parameter in a formula.
To do this, you’ll need to:
- Create a new column with a formula checks each row of your table to see if any of the comma-separated values in the text box can be found within that row.
- Apply a filter to that new column to removes any rows that do not contain any of the comma separated values
Example Solution:
1: Create a new column with a formula checks each row of your table to see if any of the comma-separated values in the text box can be found within that row.
In this example …
[SearchTextBox] is our parameter (which is being referenced by it’s Control ID), and
[SearchedColumn] is the column that we want to search through.
[SearchMatch] is my new column with a formula that checks each row of [SearchedColumn] to see if it matches any comma-separated values of [SearchTextBox]
Here, the formula for [SearchMatch] =
If(
If(SplitPart(Upper([SearchTextBox]), ", ", 1) = "", False, Contains(Upper([SearchedColumn]), SplitPart(Upper([SearchTextBox]), ", ", 1))) or
If(SplitPart(Upper([SearchTextBox]), ", ", 2) = "", False, Contains(Upper([SearchedColumn]), SplitPart(Upper([SearchTextBox]), ", ", 2))) or
If(SplitPart(Upper([SearchTextBox]), ", ", 3) = "", False, Contains(Upper([SearchedColumn]), SplitPart(Upper([SearchTextBox]), ", ", 3))) or
If(SplitPart(Upper([SearchTextBox]), ", ", 4) = "", False, Contains(Upper([SearchedColumn]), SplitPart(Upper([SearchTextBox]), ", ", 4))) or
If(SplitPart(Upper([SearchTextBox]), ", ", 5) = "", False, Contains(Upper([SearchedColumn]), SplitPart(Upper([SearchTextBox]), ", ", 5))) or
If(SplitPart(Upper([SearchTextBox]), ", ", 6) = "", False, Contains(Upper([SearchedColumn]), SplitPart(Upper([SearchTextBox]), ", ", 6))),
True, False)
In this example …
[SearchTextBox] is our parameter (which is being referenced by it’s Control ID), and
[SearchedColumn] is the column that we want to search through.
This example only supports 6 comma-separated search terms, but can be modified to support more.
2: Apply a filter to that new column to removes any rows that do not contain any of the comma separated values
Apply a filter to your new column [SearchMatch] so that only rows that have a value of True on that column appear in the table
Notes on Use:
This method relies on a parameter-based formula using the Contains function to work, so I recommend making sure that your parameter control is not also being used as a filter directly. Controls act as filters when they have Targets, so you can ensure that your control is not also filtering anything directly by removing any Targets as listed under the Targets tab of your control’s element properties:
This method relies on a parameter-based formula using the Contains function to work, so your users won’t be able to filter in ways other than Contains. For that reason, it’s recommended that you uncheck the Show Operators setting since, the changing the operators here.
The formula presented above is limited to 6 comma separated values to be searched for at a time. However, you can easily modify the formula to accomodate more values by adding more or-separated conditions to the first argument of the outer If() in this form where N is the index:
If(SplitPart(Upper([SearchTextBox]), ", ", N) = "", False, Contains(Upper([SearchedColumn]), SplitPart(Upper([SearchTextBox]), ", ", N)))