Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

Dynamically Create List Box Values From Input File

fadib
Alteryx Alumni (Retired)
Created
ListBox.png

This article will show three scenarios in which the list of selections in the List Box tool can be generated dynamically based on:

  1. the column names from an input file
  2. the values in one of the columns of the input file
  3. the values in one of the columns of a user-selected input file

You can find the workflows used in the article attached to this post. They were built in AlteryxDesigner 10.1.

So let'sget started.

1. Show the selection options based on the columns of an input file

In the first scenario we want to load the column names from the fields in the input file. We can directly connect the Input Data tool to the “Q” connector of the List Box tool. This is a very simple workflow that updates a Select tool but of course this is applicable to more complex workflows. The List Box will read the names of the fields from the Input tool and show them to the user.

1.png

The names of the columns from the input file (CustomerID, FirstName, …) are showing on the left. By connecting the output of the Input Data tool to the List Box tool, the List Box tool is automatically set to read the ”Fields from Connected tool”. The result is that the user of the Analytic App will see the fields whenever the App runs as shown on the right. If the fields change in the input file, the List Box tool will get the new column names from the file. In the next scenario, this same feature is used but with a trick to load values from a column into the List Box tool.

2. Show the selection options from the values in one of the columns of the input file

In the second scenario, the user selection values should come from one of the columns, say the CustomerSegment column. In this example, there are 4 customer segments: Consumer, Corporate, Home Office, and Small Business. Rather than enter these values manually in the List Box (and have to again edit them if they change in the file), the trick is to create a temporary table with these values in the Field Names and then link the temp table to the List Box tool.

2.png

The list of segments (values) is created by using the Summarize tool on the field of interest. A count on the field is also calculated to have the data needed for the next step.

3.png

The trick in this workflow is using the CrossTab tool to create the temporary table and set the field names from the values of the segments. This is done by setting the Header Field to the field that has the values to be shown in the List Box, CustomerSegment in this case, and the Data Field to Count.

4.png

The output of the CrossTab tool will have the Segment values in the Field Names and the Counts as the first record. The Field Names have “_” instead of “ “ and so the Dynamic Rename is used to modify the names.

5.png

From this point on, the workflow is similar to the first one where the output of the Dynamic Rename tool is connected to the List Box tool to flow the values. The result is that the user sees the actual values coming from the input file every time the Analytic App is run.

6.png

3. Show the selection options from one of the columns of a user-selected input file

In both previous scenarios the input file was pre-defined in the workflow. In this third scenario, the user is given the option to first select a file and then select one of the values coming from one of the columns of this file. The file that the user selects must have the same columns (but not the data, for example a file that is updated monthly with new data) as the file configured in the workflow. This exampleis somewhat more complicated because by giving the user of the App the option to select the file, the file information is not available at the time when the List Boxvalues are created (Alteryx doesn'tyet know which file to use). The way around this is to use chained Analytic Apps. In this case, the first workflow offers the user the option to select a file and then saves two temporary outputs: the values to be shown in the List Box and the data from the input file. Before finishing, the first workflow launches the second workflow. The second workflow showsthe user the dynamically read values from the target column. Make sure you save these workflows as Analytic Apps.

7.png

The first workflow uses what was done in the workflow of the second scenario but outputs the results into a yxdb file. The hidden step in this workflow is to set the second workflow to start when the first one finishes execution. This is done by naming the second workflow in the Interface Designer(Alt-Ctrl-d) under Properties: "On Success – Run Another Analytic App". Also the "On Success - Show Results to User" shouldbe deselected.

8.png

Once the first workflow completes the run, it launches the second workflow. The second workflow implements the remaining steps to show the List Box to the user and update the filter and the output with the user'sselection.

9.png

You can find the workflows used in the article attached to this post. It was built in Alteryx Designer 10.1 (10.1.7.12188). To test the workflow please use the packaged yxdb file from_externals\1\CustomerFile4.yxdb or create your own but it must have a field named CustomerSegment.

Please see part 2 where we’ll show you how you can add more flexibility by allowing the user to select a file that doesn’t have the same fields as the one in the App configuration and then select from the values of one of the columns of this file/field.

Happy App-Building!

Attachments
Comments
1270
5 - Atom

This article helps me great deal, but I could not see the page 2 for more information.  

How can I get the privilege to see the page 2?

 

Thank you,

 

J. Kim

nanosift@gmail.com

 

It direct me to following message;

"You do not have sufficient privileges for this resource or its parent to perform this action.

Click your browser's Back button to continue.

Kate
7 - Meteor

This is really useful.  I have used the set up shown above for selecting options from a column for a user selected input file.

 Is it possible for this to run within a macro?  I need to filter the data in turn using each option from one of the variables, and create a dropdown for the resulting options and then create an output for each iteration of the macro.   Currently my macro creates the first dropdown  and its output, and then stops

 

Thank you

Colgan
ACE Emeritus
ACE Emeritus

ListFromValues.yxzp does not seem to work out of the box.  I have updated it, but I could someone show me how to do it with the simple filter rather than the custom filter using IN?  

 

See attached.... hrm, how do i attach a file?  (embarrassing)

 

Regards,

John

John Colgan

 

 

milesac
6 - Meteoroid

I have used the example above and managed to create a really useful workflow, only difference being is that I'm using a DB connector rather than an excel document initially.

The only issue I'm having is the lists are not updating when new data is available, ie the list should now be showing prior week which the dataset holds but the list in the Gallery does not.

Workflow.jpg

 

Regards,


Miles

Kate
7 - Meteor
Hi Mike

Just had a quick look and I think you are missing the 2nd workflow from step 3 in the detail above. This is the part that should update the list with what you have set out in the first workflow..
(sorry this is brief, I hope its useful!)

Best,
Kate
nbsmit01
6 - Meteoroid

The ListFromValues.yxzp does not seem to work.

 

This is exactly the function that I am needing.  Please respond with advice on how to get it going as a filter.

Colgan
ACE Emeritus
ACE Emeritus


interface... where is upload/attach Alteryx Workflows?interface... where is upload/attach Alteryx Workflows?

 

do i need to be a higher community user to post Alteryx modules?  could someone please teach me how to use this site?  ugh.  embarrassing.

 

 

milesac
6 - Meteoroid

Hi,

 

I found using a chained application to be a better solution, I couldn't get the above to work on the Alteryx Gallery due to a timing out issue.

 

Simply put you would create several separate workflows, below is an example where 1 workflow creates the temporary datasets which populates the lists on the filter workflow.

 

To link the applications, navigate to designer interface - settings (spanner) - tick on success run (your second workflow)

 

here are some links which I found useful

https://community.alteryx.com/t5/Live-Training/Live-Training-Introduction-to-Applications/td-p/45406 and https://community.alteryx.com/t5/Live-Training/Live-Training-Intermediate-App-Development/td-p/43870

 

 

Workflow 1

Workflow 1.png

 

Workflow 2

Workflow 2.png

Hopefully this is of some use!

Ralf
5 - Atom

Hello,

like already posted by nbsmit01 the workflow ListFromValues.yxzp is not running as described.

I am struggling on this issue for some time now and really got frustrated.

 

Has anyone got this sample running?

 

Help is highly appreciated. 

Colgan
ACE Emeritus
ACE Emeritus

I just created a new post with a working version:  Located at

 

https://community.alteryx.com/t5/Publishing-Gallery/Populate-Listbox-Dynamically-through-Connected-T...

 

Regards,

John

fadib
Alteryx Alumni (Retired)

 Hi everyone,

 

First, apologies for the lack of response from my side; I wasn't getting the notifications. 

 

Second, a big thank you to @Colgan for posting a link to his solution. Indeed, the Filter and ListBox tools in attached workflow are miss-configured. Unfortunately, I don't have access to edit the post either. I'll fix the issue and add a few clarifications based on your questions as soon as I can. Meanwhile, Colgan's post here has the fixed version. 

 

Here's a link to the second part of this article: https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Dynamically-Create-List-Box-Values-From-Inpu.... Do let me know if you still have issues with the link and I'll post the explanation here.

 

 

@milesac, the post wasn't clear enough in explaining how the ListBox tool fills the list values, especially for Example 2. Alteryx creates the values for the interface tools before the workflow is run. Therefore, it's not possible to read the data and update the options in the ListBox at run-time. The setup in Example 2 requires the users to run the workflow with the new data at least once in the designer. It is a way to avoid typing a list of for example 40 values manually in the list box. However, It is not a way to update the list dynamically at run-time. The way to do that is to split the workflow into two, one for reading the values and another for showing the list of options to the user. Alternatively, if you can store the data in DB, the ListBox can read the Name:Value pair directly from a database table. As a third option, you can load and edit the XML of the second workflow to change the ListBox configuration. This was the topic of the second article which unfortunately wasn't accessible. It's great that you found the solution and I'm adding this explanation to clarify the issue.

 

 

Fadi 

 

Fgill
7 - Meteor

Is there anyway for this to work with Macro-Inputs ?  A input box works I guess but i'm trying to make a reusable macro and only the test fields in the macro input are showing up, which is counter-intuitive to the purpose of the macro-input tool.

Trich
7 - Meteor

I downloaded list from value. I hit the want to run the app, and choose a segment, but It doesn't change anything. I look at the update value and it still says "test" after I select the consumer_segments and click finish. Any help would greatly be appreciated.

KMiller
8 - Asteroid

hi @fadib 

 

Re your comment on 07-21-2017 12:14 PM above, the last paragraph. Could you explain or do your have an example of the following options you mentioned:

 

  • "The way to do that is to split the workflow into two, one for reading the values and another for showing the list of options to the user."
  • "As a third option, you can load and edit the XML of the second workflow to change the ListBox configuration. This was the topic of the second article which unfortunately wasn't accessible."

Thanks

 

Kevin

 

 

ChristianCartagena_
6 - Meteoroid

Thanks the one selection out of the list box works, is there a way to be able to select multiple options in the list box?