Whilst building an Alteryx App for a Weekly Challenge I came across a problem: there isn’t an immediately-apparent way in Alteryx to populate a drop-down menu from a column in your dataset. But don’t worry, there is a workaround! Let’s delve into the problem and the workaround using a sample coffee chain data set.
The coffee chain sells beverages across different states of the US. I want to create an app that lets the user pick a state. For that state, the app should display the top 5 selling beverages, like in the workflow below:
Right now the state is hard-coded as California. But I want to make this an interactive app, so that the user is presented with a drop-down of all the states in which this coffee chain is active.
Below, I have added a summarize tool to create the list of distinct states. I have also connected the ‘Chosen State’ input to a Drop Down tool. The Drop Down tool has various configuration options, including ‘Fields from a connected Tool’.
At first, this sounded promising. But if you connect the list to the Drop Down tool, only the column name shows up as an option.
Puzzled about what to do next, I had a peek at the solution to the Weekly Challenge. The solution suggests entering the list manually into the drop down tool. But that means if the coffee chain opens a branch in another state, that state will not automatically be included in the dropdown. So how can we make the app scalable?
Here is a workaround for this problem:1. In the summarize tool, group by your drop-down values twice
2. Add a Cross tab tool and configure it so the drop-down values are column headers.
3. Add a Dynamic rename tool and use the first row of data as the column headers. You need this because when using a cross tab tool, any spaces become underscores, so New Mexico becomes New_Mexico.
4. Connect the output to the Drop Down tool
When we now run the workflow as an analytic app, we are presented with a dropdown of the states 🥳.
Looks like people in Florida really like their Decaf Irish Cream.
I wish Alteryx had the option to populate a drop down from a column in a data set. But given there is no such option, I hope you will find this workaround useful and that it will make your app-building more gratifying.
Thanks for reading and contact me on Twitter if you have any comments or questions.