The goal of this document is to outline the steps necessary to create an analytical app that can filter to zero, one, or many values in a given field. Additionally, we will step through how to configure your action tool so that users can copy and paste directly from excel without having to take the time and place commas between values.
Step 1: Locate the tool that you would like to configure for optional filtering.
Step 2: Create a custom filter that uses the “IN” clause. Key in one value (in this case the one value is “9999999999”.
Step 3: Connect the “Q” output of your text box interface tool to the lightning bolt input of your filter tool. This will automatically place an action tool on the canvas in between these two tools.
← Before and After →
Step 4: Configure your interface tool to support multi-line inputs and enter the text or question to be displayed to your report user.
Step 5: Configure the action tool to “Update Value with Formula”
Step 6: Click on the following line to highlight it – this is not optional and is easily overlooked
Step 7: Click the ellipses at the bottom of the configuration pane of the action tool to create your update formula
NOTE: The following formula is almost correct. This formula will replace what is in the filter tool (Destination) with what the user inputs into the text box.
The problem with this formula is that it requires a user to input their values in a very specific format (commas to separate the values when the input in the text box). Our goal in this document is to step through how to strengthen your code so that it will still work when users omit the commas.
Let’s assume that users might copy values from a column in Microsoft Excel and paste into the text box of our analytical application.
If a user has copied what is highlighted above, they will be inadvertently copying newline characters as well. We want to replace these newline characters with commas. The way we will do this is with this formula:
Note: The ‘\n’ is how to denote newline character string in Alteryx. The “,” is how we denote a comma string in Alteryx. Notice that the comma is wrapped in single quotes as well as double quotes. This is because we would like our values to begin and end with single quotes.
If that isn’t quite clear, take a look at what would happen if we didn’t wrap our comma in single quotes. The following line is what our Regex_Replace would return:
‘123, 456, 789’
We are missing the single quotes at the beginning and end of each our values. When we add the sing quote in our Regex_Replace, our formula will then return this:
‘123’, ’456’, ’789’
Step 8: Now we want to embed this formula into our original formula from Step 7. The result will look like this:
Notice that this formula now has two replace functions. The Regex_Replace puts commas in place of newline characters. Once that is complete, the Replace will input that string in place of the ‘9999999999’ in our Destination. Select “OK” to save the changes you made to the formula. Your user can now copy and paste from excel and your interface tools will convert the newline characters appropriately.
We’ve configured our interface tools to allow for multi-value filtering but let’s now image the scenario where we want to provide the user the option to not filter. Maybe your user does not want to filter that field at all.
Let’s take a look at what would currently happen if a user was to leave the input field blank for our filter. Glance back at the formula from Step 8. The RegEx_Replace wouldn’t have any newlines to replace (as there are no newlines in a blank field) so it would just pass through the user input ([#1]) to the Replace function. The Replace function would then replace the ‘9999999999’ with the user input (which is blank). The resulting filter tool would then look like this:
[Field 1] IN ()
This will cause the workflow to error. The filter tool cannot evaluate this expression without something inside of the parenthesis. We can modify our formula tool to do something special when it recognizes a blank input.
Step 9: Here we add a conditional statement that first checks whether the user input field is blank. If the field is not blank, then our formula from Step 8 is returned. If the field is blank, then we tell the action tool to replace the entire formula with “1=1”
Because 1=1 will evaluate TRUE for every row in the data set, our filter tool will effectively do nothing (which is exactly what we want).
Using our final formula from Step 9, we give the user the option to no values, a single value, or multiple values.
Posted by Michael Morningstar