Tool Mastery | Drop Down

[DanM]

This article is part of the Tool Mastery Series, a compilation of Knowledge Base contributions to introduce diverse working examples for Designer Tools. Here we’ll delve into uses of the Drop Down Tool on our way to mastering the Alteryx Designer:

The Drop Down Tool is part of the Interface Tool Category and can be used when creating Apps. This tool has many great configurations from loading data from within the workflow to using outside sources to update tools. Hopefully, after reading this article and looking at the samples you will feel more comfortable using this tool in your app user interface.

When building apps, whether to use on your own or to share on a Gallery, the Interface Tools you choose are important for convenience as well as efficiency. One of the more dynamic Interface Tools to use is the Drop Down Tool.

Before we begin, please remember that in order to use the Drop Down Tool properly, your Action Tool will also need to be setup to update the tool using the variables chosen in the Drop Down Tool. To learn more about the Action Tool, see here.

Configuration:

I have attached a workflow that contains a majority of the Drop Down Tool setups, but want to walk through a few specific setups. If you are interested in the other setups you can visit the help for this tool.

External Source - Must contain Name && Value Fields (can be relative path) If you want to use an external file with a Name field that will appear to a user that is associated with a value to update a tool this is a great option. For example, many users will take advantage of this option when they have employees names and employee IDs. The file can be updated with new employees or when employees leave. The user won't need to know the employee ID and just the name, while the workflow will use the employee ID as the variable to update a certain tool. This makes it easier for Alteryx to use an ID rather than using a name especially if people have the same name.

Important pieces to remember in this List Value:

1. The file used must contain the fields Name and Value

2. If the app is used on the Gallery, the source file will need to be included as an asset

Fields from Connected Tool - This is a configuration that many users have trouble with. The benefits of this List Value is that your Drop Down list can be pulled from your data within the workflow. The important piece of this is to remember that the data you want to be in the Drop Down needs to be in the Field Headers. The best way to accomplish this is to use a Cross Tab Tool.

I have data that looks like below. I want the names to be in my Drop Down:

I add a Cross Tab Tool and make the Names my New Column Headers and Value the Values for New Columns. I then need to choose a Method for Aggregating Values so I choose SUM.

I then connect my Cross Tab Tool to the TOP of the Drop Down Tool:

 

Manually Set Values -This List Value works very similarly to the External Source, but you can embed the Name:Value into the Drop Down Tool itself. This is a great option if you don't have that many options for the user to pick from and can be added from and removed quickly. The important part of the setup is the Name:Value combination. For example Dan:55, the name Dan will show in the Drop Down, but use 55 as the value to update the tool in the workflow:

File Browse in this Analytic App/Macro - This options is another great option when you have a different list of Names and Values to update an app. The other Inputs, like the External Source, will need to have the Name and Value as field names. The great option using the List Value is that you can give the user the option to choose different lists based on what they want to use in the Drop Down:

See the attached v10.6 workflow for the examples above.

By now, you should have expert-level proficiency with the Drop Down Tool! If you can think of a use case we left out, feel free to use the comments section below! Consider yourself a Tool Master already? Let us know at community@alteryx.com if you’d like your creative tool uses to be featured in the Tool Mastery Series.

Stay tuned with our latest posts every Tool Tuesday by following Alteryx on Twitter! If you want to master all the Designer tools, consider subscribing for email notifications.

[PhilipMannering]

Hi Dan. Very helpful blog post. I'm just struggling with using Fields from Connected Tool use case. You said yourself that the data needs to be in the file headers. I'm finding that for your example to work, the actual values (698, 465, etc) have to be the headers. Can you explain?

[DanM]

@PhilipMannering The setup is working correctly for the Cross Tab setup. My apologize for having the wrong screen shot in the post. I am updating that now. Connecting from a tool within the workflow, the Drop Down will read the header as the Name and Value as the same. Unlike the other options where you can designate the Name and Value as different variables.

[jonriddle]

Hi Dan,

I am trying to use the Drop Down interface tool, where the list values are "Fields from Connected Tool".  The difference is that my "connected tool" is a macro input.  For creating the macro, the input is just a simple text input with two fields (Id and Value).  I want the user to select the name of one of the fields that they are passing into the macro.  But, when I go to use this macro, I am prompted with the names of the fields I set up in the creation of the macro, not the fields that I have connected to the input of the macro.  Any thoughts?

Macro:

Use of Macro:

Macro Setup:

[DanM]

@jonriddle the "Field Connection" option does not work that way, as the only way that Alteryx will update that drop down is when the workflow is run. You would have to update the Drop Down before the macro gets run. So, you could create a chained workflow. The first workflow would update an output, which in turn you would use that output to update the Drop Down and then the user would see the updated data in that Drop Down.  Really that Field Connection option is there to build an initial Drop Down so user don't have to use external files if that Drop Down doesn't change. If you need to have that be dynamic and update, you will need to create a separate process to update that Drop Down tool.

[ColleenH218]

I have a question regarding using the 'manually set values' option. I have my list of values defined, but wonder if there's an option to show a blank value (or a "Select") by default, then require that a choice be made? I hesitate to set a default - the selection made by this box determines which container is enabled and therefore, which workflow is run. 

Thank you!

[DanM]

@ColleenH218  There isn't a way for you to add a selection that cannot be used. However, you could setup an Error Message tool to error if they chose the "blank" selection in the Drop Down tool. 

[ColleenH218]

Excellent Dan, thank you! That will work!

[ZoeyL1GL]

hi @DanM, 

Thanks for your post on this topic. I am having same trouble as @jonriddle.

Could you please give us a sample workflow for your suggestion of adding another workflow as an output to feed into the drop down interactive tool please? Or to be more specific on how to do it please?

Many thanks,

Zoey

[DanM]

@ZoeyL1GL,

The Interface tools cannot be updated with new data until the workflow is run. Therefore, if you want to update the Interface tool prior to run with new selections, you must create another workflow that would feed the Interface tool with output fields named  Name & Value and the selections and values in those columns. The Interface tool would then call that output from the first workflow and display the new selections. There is no way at this time to dynamically update the Interface tool selections without running the workflow first. The reason we have the connect from another tool available is so that users don't have to manually add selections or have files to reference.

Hope that help.

DanM

[ZoeyL1GL]

Thanks @DanM,

As you suggested, I created a chain workflow before the macro and load the output from the previous chain workflow to the macro. It now works.

many thanks!

Zoey 

[Srazvi90]

I'm not sure what is meant by the "name:value" pair and how to use it. I'm trying to use the "manually set values" option for the drop down. I have only two values that I want the user to select from. The value selected by the user should feed into the filter tool. See screenshots below for my configuration of the Dropdown tool as well as the formula I used in the Filter tool. What am I doing wrong?

I want these two values to show up in the drop down.I want the business field to be filtered for what the user selected. What am I doing wrong?

[Srazvi90]

Nevermind, I resolved it. The spelling of "Dropdown" in the formula tool needed to match the name of the tool.

[snamburu]

Hello Dan,

I am using an "External Source" option for the drop down it is a sql query from redshift

Select emp_name as Name, emp_name as Value from employee Order by emp_name

When I run the app on my desktop I can see the values in the drop down but when I publish the same to the gallery the values disappear and the drop down is not working.

I created the data connection in the server and that is what I am using to query.

Am I missing something here?

TIA!

- Sri

[DanM]

@snamburu ,

This is only for external source files. I don't think it will work with a database as it would have to be able to query the database before the run. It may be working locally since the time to get the data is much faster than the server. So if the meta data doesn't get updated almost immediately it won't display.

If you need to display options from a database, you may want to create a chained app or use the Events tab to kick-off a workflow that would update a file with the Drop Down content. You can create something as simple as a workflow that just had an Input to the database and an Output that creates the file with the Name&Value. The DropDown would then reference that file. The only interface tool that is database ready is the Tree Tool.

From Help Documentation

https://help.alteryx.com/2018.4/DropDown.htm

External Source
Populates the list with values from a specified file or database connection.
Properties
Specify the file location of the External Source File.
Format the file to contain the Name field in the first column and the Value field in the second column.
The end user selects a name from the list to generate the corresponding value. The file can be a relative path to the saved workflow, but the end user must have the external source file saved to the same path.

[snamburu]

Gotcha!

Thanks Dan, I will try to use other options as you described.

[DanM]

@snamburu,

Doesn't necessarily mean that it couldn't work. I would make sure that your SQL is working correctly by setting up an Input tool and Output tool and see if you get the Name&Value results. If you get results, it may be like I mentioned with the speed, but if you are not getting results on the server, it may be your database setup.

DanM

[snamburu]

@DanM,

I am seeing Name & Values when I use input & output tool.

For now I stored that the output as an external file and import that into the dropdown tool.

-Sri

[DataGrad]

I do not find the Fields from Connected Tool works as I expect, where i would take values from a field to use in the drop down.

I have downloaded the sample workflow and the names appear in the drop down. But if i change the names in the text input, the drop down does not update the values to choose from.

e.g if i take "Dan" out of the list and replace it with "Tom" and i click run as app, "Dan" is still in the drop down and "Tom" is not. I seem to have to run the workflow as a workflow for the drop down to update.

Does this mean i have to run the workflow on a regular basis to keep the drop down options up to date as it doesn't seem to work running only as an app? I would be looking to have this sort of function on the Alteryx Gallery so other users can use the app.

[dalimanovi]

Hello,

Does anyone know how to use the drop down tool to filter a ledger for unusual account combinations? For example, how would I filter out all credits to a revenue account with an unusual debit? I can perform this using multiple filter functions to extract (1) references for all credits to revenue and then (2) all debits to the unusual account. 

Thanks!

[JuanCalle]

Hi community!

I want to use the dropdown list to choose two fields that I want to concatenate. But when I select the fields that I want to concatenate my result is the name of the field and not the value of the field.

This is the macro I created:

And this is the output when I use the macro:

I want the result to be in the field Full Name:

James Rodriguez

Falcao Garcia

And not Name Last Name for both rows.

Any ideas of how I can do this?

[JuanCalle]

I answered my question. If I want just to use the values of the fields, I only have to configure the macro to show field Map. I do not need to use the drop-down list

[Carrefour]

Hello 

I download Drop Down Interface Samples.yxwz exemple because I have a similar problem.

When I test the flow External Source - Must contain Name && Value Fields, with value<=30 and name ="Criston", I have no records.

Do you have an explanation ?

Thank you in advance

Best regards

Sandrine

[CharltonFranklin]

@DanM I have the same issue as @snamburu . On Desktop & Server version 2019.4, my Snowflake SQL query worked fine. I had an issue with Oracle on-premise queries. I had to publish the report with credentials for it to work. That didn't make sense to me because I would imagine that the credentials in the database connection would be the requirement for the query to work. If it couldn't pass the query without proper access, then I would think that if I'm the one running the workflow, my credentials would be passed to allow the query to go to Snowflake - however, even though I had access to Snowflake personally, the query still required published credentials with the workflow. I didn't bother with it since I found a way to make it work.

Now I'm on version 2022.4 and the SQL query doesn't work at all. I always get a blank drop-down when using an external data source.

You say that you "don't think it will work", but I demonstrated that the process can work. You suggest that the query needs to be done "immediately", but even with low latency I didn't have issues on version 2019.4 where sometimes my drop down lists would populate and sometimes they wouldn't depending on latency. Also, I would expect that there would be a definable time limit programmed in Alteryx - at least then I can find ways to optimize a query to meet that timeout limit.

A chained analytic app may be a work around, but I think it's safe to say that Alteryx's external source using SQL is not working as intended and has a bug.