applies to version: 8.2 to 2017, does not apply to version 2019 and higher, author: Kamil Nędza
The Choice tree form field is used for displaying data on the SharePoint form as an expanding tree structure. The form field is a “Picker” type, designed for selecting a single value from the tree structure. Data presented this way can be very clear and easy to understand. The tree structure is excellent for presenting complex organization structures, as the transparent parent/child connections between nodes convincingly represent superior/subordinate relationships. It is also useful for presenting data where multiple leaves (i.e. end nodes – nodes with no children) have the same or similar names but different IDs and different paths that lead to those leaves.
Configuration – how to build the choice tree
Correctly handling the data returned form the data source is the key to properly configuring the Choice tree form field. There are 2 ways of configuring the data source so that it displays its contents in the form of a Choice tree:
- Configuration using ParentID – The data source must contain 3 columns. The first will serve as the Id, the second will contain the display name, and the third column whose values will point to values from the first column. This third column is used to build the hierarchy (levels) of the choice tree – The values correspond to the IDs of records from the first column, defining the parent.
Note: The third column has to be named ParentID
For example: if the data source row has “1” in the ParentID column, that row will be placed one level below the row with an ID value of “1”, and will become one of its children. If the row has an empty ParentID column, that record will be highest on the hierarchy – it will be the tree root node.
- Configuration using paths – The data source must return two columns. The first containing the ID, and the second a correctly formatted path of display names. The display name should be arranged in the following pattern: “<1st level display name>/<2nd level display name>/<3rd level display name>/etc.”. The tree will be constructed based on these 2 columns.
For example: If the vale column contains: “1/2”, the record named “2” will be placed on the 2nd level of the tree hierarchy, as a child of the “1” node.
Note: For this mode, it is impossible to select intermediate values (nodes), only leaves (end values) can be selected.
The Choice tree form field will detect the presence of a column named ParentID, and will use the correct mode automatically.
Configuration – side by side comparison
To better illustrate how the hierarchy of the nodes is defined, and how the Choice tree is built, we designed the following example:
A is the tree root. Nodes B and C are siblings, children of A. Node C also has its own child: D. Node A and C are intermediate nodes while B and D are leaves – end nodes with no children of their own.
|Config. Type:||With ParentID||With paths|
|Form field config.:|
|Choice tree in SharePoint form:|
On the workflow form, the trees look identical regardless of which method was used. However, it is impossible to choose intermediate values in the tree on the right, only leaves (values B and D) can be selected. This is why it’s better to know beforehand if you want the intermediate nodes to be selectable options too – if yes, then it is necessary to build the Choice tree via ParentID.
Example – AD structure in a Choice tree
The Choice tree form field is a great choice for presenting an AD structure. For example, it can be used to show all the subordinates of a certain employee. In this demonstration, in the configuration we used a recursive query, and we decided to use the first method of tree construction– with ParentID. The following query was used for the data source:
This query returns the user that is currently logged in as the tree root, highest in the tree’s hierarchy, and then the recursive query returns the user’s subordinates. In other words, the Choice tree will display all employees that are below the current user in the AD structure, and are his/her subordinates.
NOTE: If the query happens to return an error when attempting to enter the advanced configuration screen, substitute it with simple placeholder SQL query (the simplest select possible) to return identical columns. Enter the advanced configuration screen, configure the form field, then exit and replace the placeholder query with the correct one.
The final effect, as seen by the current user: