# How to populate a Tree display from a query

**URL:** <https://forum.inductiveautomation.com/t/how-to-populate-a-tree-display-from-a-query/96176>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [September 19, 2024, 9:52am UTC](https://forum.inductiveautomation.com/t/how-to-populate-a-tree-display-from-a-query/96176 "2024-09-19T09:52:22Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [September 19, 2024, 9:52am UTC](https://forum.inductiveautomation.com/t/how-to-populate-a-tree-display-from-a-query/96176/1 "2024-09-19T09:52:22Z")

</div>

Hello!

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/3/23631055844fd73b795d6b7c24aba311b39a9d3f.png)

I do have a query that gives me data like this:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/4/c4662a0e488e74c04fa732afbdf6456676faac94.png)

I would like if possible to bind this data to my tree view but I don´t know where to bind it. Should I use a change script?  
SOLICITANTE would be the first folder level,  
DESCRIPCION should be the child level, and  
NUMERO PETICION the grandchild level

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/1/71cb89d3b98e29bd6f05d86835a6db93b48be9f6.png)

---

<div class="post-metadata">

**Author:** ![Steve\_Laubach](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steve_laubach/32/60900_2.png) [@Steve\_Laubach](https://forum.inductiveautomation.com/u/Steve_Laubach)\
**Post date:** [September 19, 2024, 10:07am UTC](https://forum.inductiveautomation.com/t/how-to-populate-a-tree-display-from-a-query/96176/2 "2024-09-19T10:07:58Z")

</div>

Tree data doesn't necessarily display well in a database format.

You can populate most if not all controls by building the json in a script transform. You could put a binding on your the "items" property that triggers when your data changes. Then you put a script transform on that that builds the json for your tree.

You can copy the items property and paste it into a text editor to get an idea what the json looks like. That's usually my first step if I'm building the json for something in a script transform because it's quicker than looking up documentation (for me).

You may want to consider organizing your data based on the order it is used. If you're in MS SQL, consider using a recursive CTE and consider putting it in a stored procedure to compile the execution plan. I'm told that named queries also compile the execution plan but I haven't benchmarked that to know if it's the same thing that happens when you declare a stored procedure. I typically use stored procedures for anything that could have a significant execution time.

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [September 19, 2024, 10:35am UTC](https://forum.inductiveautomation.com/t/how-to-populate-a-tree-display-from-a-query/96176/3 "2024-09-19T10:35:56Z")

</div>

Thank you Steve, that´s the point, convert the data into a Json.

Bind the query to the props.items of the Tree

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/a/ca4be9da6dd19a32c243a0aeccfd06d7e51e74ec.png)

Then used Transform Script using Jython on the query and organizing the hierarchy like:

```python
def transform(self, value, quality, timestamp):
  
    raw_data = value
    

    if raw_data is None or raw_data.getRowCount() == 0:
        return []  

    # Initialize a dictionary to hold the hierarchical data by SOLICITANTE
    hierarchy = {}

    # Iterate through each row in the dataset
    for row in range(raw_data.getRowCount()):
        solicitante = raw_data.getValueAt(row, 2) # SOLICITANTE at index 2
        descripcion = raw_data.getValueAt(row, 0) # DESCRIPCION at index 0
        numero_peticiones = raw_data.getValueAt(row, 1) # NUMERO_PETICIONES at index 1

        # If the SOLICITANTE (parent) is not in the hierarchy yet, add it
        if solicitante not in hierarchy:
            hierarchy[solicitante] = {
                "label": solicitante,
                "expanded": False,
                "items": [] # This will hold DESCRIPCION children
            }

        # Add DESCRIPCION (child) with NUMERO_PETICIONES (grandchild)
        descripcion_item = {
            "label": descripcion,
            "expanded": False,
            "items": [
                {
                    "label": "NUMERO PETICIONES: {}".format(numero_peticiones),
                    "expanded": False
                }
            ]
        }

        # Append the DESCRIPCION item to the SOLICITANTE's items
        hierarchy[solicitante]["items"].append(descripcion_item)

    # Convert the hierarchy dictionary to a list of dictionaries
    json_list = list(hierarchy.values())

   
    return json_list

```

Result:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/c/9ce56371c2e99ecc42f19644bf8262759a058c03.png)

I guess would be similar aproach to populate bar charts from querys.

---

<div class="post-metadata">

**Author:** ![Steve\_Laubach](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steve_laubach/32/60900_2.png) [@Steve\_Laubach](https://forum.inductiveautomation.com/u/Steve_Laubach)\
**Post date:** [September 19, 2024, 12:50pm UTC](https://forum.inductiveautomation.com/t/how-to-populate-a-tree-display-from-a-query/96176/4 "2024-09-19T12:50:31Z")

</div>

A lot of times you can set the return format to json. Play around with return format and see if it's what you are looking for.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/b/cb0ab8089b90b3748612f537c547d4f12e8934e6.png)

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [September 19, 2024, 2:36pm UTC](https://forum.inductiveautomation.com/t/how-to-populate-a-tree-display-from-a-query/96176/5 "2024-09-19T14:36:09Z")

</div>

Thanks Steve, I trying this out for a charts and is working great! I was using datasets and going crazy because didn´t work.  
Just what i was looking for!

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/e/fe5bad73ea0a9d7263cdca36e7edf78ad486fb30.png)  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/a/0a5a0ce532f3bc79262e1976f5e5b8b838295763.png)a

---

<div class="post-metadata">

**Author:** ![Steve\_Laubach](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steve_laubach/32/60900_2.png) [@Steve\_Laubach](https://forum.inductiveautomation.com/u/Steve_Laubach)\
**Post date:** [September 19, 2024, 2:36pm UTC](https://forum.inductiveautomation.com/t/how-to-populate-a-tree-display-from-a-query/96176/6 "2024-09-19T14:36:43Z")

</div>

I assure you I have had the same problem in the past. Glad I could help.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [September 19, 2024, 2:52pm UTC](https://forum.inductiveautomation.com/t/how-to-populate-a-tree-display-from-a-query/96176/7 "2024-09-19T14:52:27Z")

</div>

I use something like this to build this kind of tree:

```python
tree = []
for row in data:
	folder = tree
	for val in [row['SOLICITANTE'], row['DESCRIPCION']]:
		try:
			folder = next(f['items'] for f in folder if f['label'] == val)
		except StopIteration:
			folder.append(
				{
					'label': val,
					'expanded': False,
					'items': []
				}
			)
			folder = folder[-1]['items']
	folder.append(
		{
			'label': "NUMERO PETICIONES: {}".format(row['NUMERO PETICION']),
			'expanded': False
		}
	)

```

Maybe it helps, maybe it doesn't. Do with that what you want.

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [September 19, 2024, 2:59pm UTC](https://forum.inductiveautomation.com/t/how-to-populate-a-tree-display-from-a-query/96176/8 "2024-09-19T14:59:10Z")

</div>

Yes, this code to build trees works great, it´s dynamic no matter the deep of the hierarchy and sure faster.  
Thanks Pascal!

Your code:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/4/44dbf044e4a2d90337ae5aa0abed42a4e2ce4226.png)

the code I was using:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/5/4598e5592cfc640bfa95f74353434642646b7a40.png)

Now I will have to sum them and show one unique result, but that´s another story.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [September 19, 2024, 3:13pm UTC](https://forum.inductiveautomation.com/t/how-to-populate-a-tree-display-from-a-query/96176/9 "2024-09-19T15:13:17Z")

</div>

What do you mean, sum them ?  
Do you want to sum the "numero peticiones" ?

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [September 19, 2024, 3:15pm UTC](https://forum.inductiveautomation.com/t/how-to-populate-a-tree-display-from-a-query/96176/10 "2024-09-19T15:15:06Z")

</div>

yep, I think I will have to do that to show the totals

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [September 19, 2024, 3:20pm UTC](https://forum.inductiveautomation.com/t/how-to-populate-a-tree-display-from-a-query/96176/11 "2024-09-19T15:20:01Z")

</div>

For example, here:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/e/8e3c6a8384bce933759e6eae0118f9e8afa54476.png)

You'd want to have

```python
AREAS
|-- CON_ASTRAS
    |-- NUMERO PETICIONES: 3

```

That's it ?

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [September 19, 2024, 3:22pm UTC](https://forum.inductiveautomation.com/t/how-to-populate-a-tree-display-from-a-query/96176/12 "2024-09-19T15:22:35Z")

</div>

Yes

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [September 19, 2024, 3:28pm UTC](https://forum.inductiveautomation.com/t/how-to-populate-a-tree-display-from-a-query/96176/13 "2024-09-19T15:28:18Z")

</div>

Do that in the query then.  
group by `SOLICITANTE` and `DESCRIPCION` and select `sum(numero peticion)`

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [September 19, 2024, 3:33pm UTC](https://forum.inductiveautomation.com/t/how-to-populate-a-tree-display-from-a-query/96176/14 "2024-09-19T15:33:26Z")

</div>

Ok, got a solution, thanks Pascal

```python
def transform(self, value, quality, timestamp):

   
    raw_data = value

   
    if raw_data is None or raw_data.getRowCount() == 0:
        return [] # Return an empty list if no data is available

 
    tree = []

    for row in range(raw_data.getRowCount()):
        solicitante = raw_data.getValueAt(row, 2)  
        descripcion = raw_data.getValueAt(row, 0)  
        numero_peticiones = raw_data.getValueAt(row, 1) 

 
        folder = tree
        for val in [solicitante, descripcion]:
            try:
            
                folder = next(f['items'] for f in folder if f['label'] == val)
            except StopIteration:

                folder.append(
                    {
                        'label': val,
                        'expanded': False,
                        'items': []
                    }
                )
                folder = folder[-1]['items']

        try:
            existing_peticion = next(item for item in folder if "NUMERO PETICIONES" in item['label'])
            existing_peticion['label'] = "NUMERO PETICIONES: {}".format(
                float(existing_peticion['label'].split(": ")[1]) + numero_peticiones
            )
        except StopIteration:
         
            folder.append(
                {
                    'label': "NUMERO PETICIONES: {}".format(numero_peticiones),
                    'expanded': False
                }
            )

    return tree

```

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [September 19, 2024, 3:42pm UTC](https://forum.inductiveautomation.com/t/how-to-populate-a-tree-display-from-a-query/96176/15 "2024-09-19T15:42:21Z")

</div>

Why floats ? Do you ever get fractional "peticiones" ? Didn't look like it.

Also, I'll insist: Do it in the query.