Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Nominations are open for the Alteryx Excellence Awards through March 26! We want to celebrate the impact you've had and give you the visibility you deserve! Make your submission here.

Configure a Custom File Tree Data Source for a Tree Interface tool



The Tree Interface can be a useful way to allow a user to select input values for an app.  Since sometimes setting up the Tree File Data Source can be a little tricky to those new to this interface, this example will step through the creation of the Data Source for a simple Tree Interface.


To use a Custom File or Database as the source for the Tree Interface tool, the source needs to be in a specific format: a table with a Description and a Key field.  It will look something like this:





And result in an interface that looks like this:





Let’s start with our original data, some very pretend pet statistics:






The first step is to determine the fields that will be used in the interface; in this case, we want to be able to select the fields Pet, Breed, and Year in the Tree Interface. These will become the different levels in the interface.







Next, we need to determine the level in which our fields of interest will appear in the tree -- the Pet field being the most general and Year being the most detailed. Sorting in this order will make sure all the data is aligned properly. All the Cat records will be together, then all the Breed records will be together, and finally all year records.





Once the data is sorted, unique identifiers need to be created for each record to establish the hierarchy. 


The first level in the hierarchy is the Pet field. To create an identifier for each unique value in the Pet field, the Tile tool can be configured to look for Unique Values on the Pet field.  The unique value for the Pet field will be in the Tile_Num field.





This process needs to be repeated for the Breed and Year fields.  The Tile tool for the Breed field will again be configured to Unique, but this time under Unique Fields Pet and Breed will be selected.






Finally, Year.





For times when there are 10 or more levels in a Tree Tool, please see the Spoiler below:

If there are 10 or more levels that need to be created for the Tree one more step will need to be done in order for the Tree Tool to differentiate each level.  For 10 or more levels you will want to apply the PadLeft() function to each level to give a buffer to each value for each level.  After the Tile tools used to create the key for each level use a Select tool to change the values to a string, then add a Multi-Field Formula tool to apply the PadLeft() function to each Tile_Num field:

10 or more leves.jpg

Adding the padding to the front of each level number will allow the concatenated values of all levels to be unique and should not cause issues when working with 10 or more levels in a Tree Interface.

An additional workflow is attached that demonstrates adding the Padleft() portion, Tree Data Source 10 or more levels.yxmd (version 2018.4).



A little cleanup was done using a Select Tool. Three things were done: all the Tile_Sequence fields were removed, the Tile_Num fields were renamed to the appropriate Level number to make things a little easier in subsequent steps, and each Level field was changed to a string so they could be concatenated in the next step.


Now we have all the information we need to build a key for each level of our data for each record.  A Formula tool is used to create 3 new fields, one for a key at each level. Our Level 1 Key will just be the value from the Level 1 field.  The Level 2 Key will be the Level 1 Key concatenated with the Level 2 field, and the Level 3 Key will be the Level 2 Key concatenated with the Level 3 field. 






Now that there is a key for each level we can construct a table that has the necessary Description and Key fields. Using a set of Select tools, select each Level Key field with its corresponding description field; Pet and Level 1 Key, Breed and Level 2 key, Year and Level 3 Key.










Now Union the three Select tools together by position.  Add a summarize tool and group by Pet and Level 1 Key.  Grouping by these fields will remove any duplicate records. The resulting fields were also renamed to Description and Key in the Summarize tool




Finally, a Sort tool is added to sort the Key field in ascending order.  The results from the sort can then be saved to a file or loaded into a database and used in the Tree Interface tool.






The entire workflow:






Attached is the workflow used to build the Key list, as well as a very simple app that demonstrates the resulting tree created from the key values.


See how to put this into action in Part 2

6 - Meteoroid

I appreciate the effort put forth to document this in great detail.  Very useful guide.

8 - Asteroid


Using the Tile tool is clever, but I'm finding that if my hierarchies have more than 9 elements (especially the first level), there seems to be no way to differentiate within the Tree tool whether '10' belongs under the '1' hierarchy, or should come after '9' in the first level.

Any thoughts?


Appending to this comment...

I think I have figured it out. What I did was determine the maximum length of each level's key and PadLeft a '0' to each applicable level. This creates a key that then will have leading zeros so you don't have the case of '10' belonging to '1' in the hierarchy since it becomes '01'.



Hi @RodLight,

Yes, the 'padleft' would resolve that issue.  That is actually something that would need to be done in the workflow when there are 10 or more levels to the tree. The PadLeft function would be used in a Formula tool after the Tile tools in the above example for each of the keys (level 1, level 2, level 3...) created Thanks for posting your solution back to the post for others to reference!  Have a great weekend!

5 - Atom

@lightalytics please share with us if you find the way to solve that, I have a data sets of hundreds and I need to solve that  issue too

9 - Comet

Hey @PeterS & @RodLight ! Thanks for sharing this detailed Use Case! I was able to use it for a project of mine. Came across the issue of having more than 9 elements too and padding the string helped solve my issue. This should be in Tool Mastery if its not already!





@RodLight , @alonso , @WilliamChan, for your future reference have have added a section above that describes how to handle situations where there are 10 or more levels, it is under the 'Spoiler' section  Hope this helps. 



11 - Bolide

I was looking at the help for Tree tool



v: Element. Required.

n: Name that will be displayed. Required.

k: Value of the element. Required if the node is selectable.

l: Set to "F" to make element unselectable with a red X.

Any value assignment other than k or l will disable the selection with a grey X.


Can I add red X/grey X functionality using an odbc connection rather than a custom xml file??




7 - Meteor

Is there a limit to what the interface can handle? Say there are 1000 unique values that can be included in a drop down. What's the limit?

5 - Atom

SQL to populate the tree interface on the fly:


WITH BasicQuery (Col1, Col2)
AS (
--Update here with your query, my tree just has two levels, follow the pattern to add more levels if needed
select distinct e.EmailDomain as Col1,
e.UserEmailID as Col2

from [jen_UserEmail] e

where e.IsVoid = 0



AS (
select distinct Col1,
RIGHT(CONCAT('00',ISNULL(DENSE_RANK() over (order by Col1),'')),2) as Level_1Key,
RIGHT(CONCAT('00',ISNULL(DENSE_RANK() over (order by Col1),'')),2)
+RIGHT(CONCAT('0000',ISNULL(row_number() over (Partition by Col1 order by Col1, Col2),'')),4) as Level_2Key

from BasicQuery

Select X.*
from (
select Col1 as [Description]
, [Level_1Key] as [Key]
from TreeWithKeys


select Col2 as [Description]
, [Level_2Key] as [Key]
from TreeWithKeys
) X
ORDER by 2





With this you won't need a chained app or run a workflow to populate the .yxdb before using the interface.  This Tree is only two levels but should be easy enough to expand.  Peter, this is based off of your work, my interpretation of it in SQL while trying to make it as modular as I could.  It would be nice if the interface database connections allowed for a Pre SQL statement


@DataDynamite , this is great! Would certainly be worth adding the the Pre SQL Statement thought to the Ideas Page.