Today I encountered the following scenario in Alteryx when we were building our first macros and apps. Let’s say we want to take the Sample Superstore dataset into our macro and filter by State using a Drop Down Interface tool:
The Drop Down Interface tool needs to know the potential values of State that it can filter by. By clicking on the tool we are presented with a few options in the configuration window.
Two options that could work in this scenario are:
- External source – load in the State names from a file external to the current workflow.
- Manually set values – paste the State names into a box in the configuration window.
But I want to keep this in-house and not have to rely on myself to copy and paste values into boxes (ever). One option that might be of interest is the “Fields from Connected Tool” option, where the Drop Down tool could be fed the list of States from the input dataset. Here’s how this works.
To create a unique list of states, use the Summarize tool to group by State and count the number of rows for each state, resulting in a dataset like so:
If you try to connect the Summarize tool to the Drop Down tool at this stage, it won’t work – the “Fields from Connected Tool” will only accept column headers to use as filters. So if we connected it now, it would be able to filter with the values “State” and “Count”, which makes zero sense.
To get around this, we’re going to cross tab the data so that the State values become the column headers. This is done with the Cross Tab tool.
There’s one last issue to take care of before we feed these column headers back into the Drop Down tool. You might notice in the last screenshot that the State labelled “District of Columbia” has become “District_of_Columbia” after it was cross tabbed. That’s because Alteryx hates whitespace in column headers, and replaces them with underscores. This is going to be a problem when we try to filter by “District_of_Columbia”, which doesn’t properly match with any of our original states.
Enter the Dynamic Rename tool. We can replace all underscores in our State names with whitespace using the formula expression:
REPLACE([CurrentField], “_”, ” “)
When we connect the Dynamic Rename tool back to the Drop Down tool, the List Values field will automatically update to “Fields from Connected Tool”, and it will finally take our State names as filter values. Here’s a screenshot of the filter when completed:
We now have all of the States loaded into the Drop Down filter list! As I mentioned, the approach is useful when you don’t want to export and read the State labels, or copy and paste the labels manually into the configuration window.
However, you might think (as I did) that when you package the app, change the data source (for example, to a dataset containing new States or States with spelling mistakes), that the Drop Down filter list would update dynamically in accordance with the new list of states – it does not 🙁
Unfortunately, Alteryx apps take the Drop Down list from the meta info of the app, rather than from new data loaded with the input tool. To update the list, you have to re-run the workflow with the new data source before you run it as an analytic app, which could be a problem if you’re only sharing the app, and not the full workflow.