Hi all,
I am new to ignition and need help is arranging the navigation tree.
I would like to arrange the parent nodes and child nodes in a particular order, how do i accomplish this?
At first glance it appears that every entry is automatically sorted based on the string used for nodes. I would like to set my own order sequence.
Thanks
Is there a way to tie a database table to a navigation tree ?
yes. just bind the items dataset to a sql query.
I tied a query to show all the nodes but all i see is a generic item1, item2 etc in the tree. How do i make the results of the query show up as the nodes?
Thanks
Maybe this will help a bit.
Let’s say you have a table (called TreeTest for this example) of departments, positions, and employees that looks like the following.
You can write a query as such, to add a / between the Department and Position fields, which the Tree View component needs to determine the level hierarchy. You call this the Path. Then you select the EmployeeName field twice, naming it Text and Selected Text. The query in Microsoft SQL looks like the following.
SELECT
Department + ‘/’ + Position AS ‘Path’,
EmployeeName AS ‘Text’,
EmployeeName AS ‘SelectedText’
FROM
TreeTest
Now, you bind the Tree View’s Items property to that query and you get a TreeView that looks like the following when fully expanded.
I didn’t answer the first question in this topic. It appears that the developers have coded the Tree View component to internally sort alphabetically by the Path parts and then the Text. The only way to order the path parts and the text that I know of is to trick it by prepending a character in front of the Path part and/or the Text as shown below. It’s not pretty at all, but it will work.
I had to break up the one table into three tables. The Employees table has an EmployeeSortOrder field added and a PositionID foreign key field for joining to the Positions table. The Positions table has a PositionSortOrder field and a DepartmentID foreign key field to join with the Departments table.
Here are the three tables.
I have set the sort order field values to make it place everything in the Tree View in reverse alphabetical order for this example.
The new query is much more complex now with joins and conversions of integers to varchars. Here it is.
SELECT
CONVERT(VARCHAR, d.DepartmentSortOrder) + ’ - ’ + d.Department + ‘/’ + CONVERT(VARCHAR, p.PositionSortOrder) + ’ - ’ + p.Position AS ‘Path’,
CONVERT(VARCHAR, e.EmployeeSortOrder) + ’ - ’ + e.EmployeeLastName + ', ’ + e.EmployeeFirstName AS ‘Text’,
CONVERT(VARCHAR, e.EmployeeSortOrder) + ’ - ’ + e.EmployeeLastName + ', ’ + e.EmployeeFirstName AS ‘SelectedText’
FROM
Employees AS e
INNER JOIN
Positions AS p ON e.PositionID = p.PositionID
INNER JOIN
Departments AS d ON p.DepartmentID = d.DepartmentID
It produces an ugly, but functional Tree View that looks like this.
Notice that the departments and positions and employees are all in reverse alphabetical order now due to the prepended sort character in front of them. Let’s hope they will change the Tree View coding soon so we don’t have to do ugly things like this.
Another workaround for the order issue: use html in text property.
For example:
mantain the desired order and don’t show the numbers.
This works only for the item’s (not for the path’s, because another id in the path is interpreted as other path and make a new leaf, with the same text)
Regards,
I upgraded to Ignition version 7.6 since the last post here. It appears something is hosed in the Tree View now. When you drag a new Tree View component onto a window, it has the test dataset in it and works fine with that.
If you select binding for the Items property and enter a SQL Query binding, then enter your query, it does not like it and gives the following error message.
Notice it has the Dataset[10R x 4C] which is not from the test dataset, which was 10 x 13. If you look at the Items dataset, it still has the test data in it. You can select the X to Delete all rows, and it will delete the rows, but the column headers stay. I don’t know what the error really means as it is cryptic to me, but I’m thinking it has something to do with the dataset expecting 13 columns instead of the 4 I’m trying to give it.
I even built a table with the headers and data from the dataset and tried to query that and bind it to Items, and it wouldn’t even accept that. Am I missing something here? I re-read the docs for the Meter Tree and don’t see anything that I’m doing wrong.
A few more brain cells kicked in after the last post and I realized I could delete the columns in the dataset while viewing the dataset, so I deleted all but the ones needed and then my SQL query was accepted with no problem. But I don’t remember having to do this on version 7.5.
I also noticed that there is a property in Tree View that I don’t recall seeing on version 7.5 named Auto Sort. I tried it out and all it does is change the sort on the Text fields within the paths, but not the paths themselves.
I modified the query above to just select the departments and positions in the order in which they are in the SQL table and purposedly reverse sort the employees for testing the Auto Sort property. I also removed the EmployeeSortOrder field from the Employees table and also the extra Jack Black employee that I accidently added. Here is the new Employee table.
And here is the new query.
SELECT
d.Department + ‘/’ + p.Position AS ‘Path’,
e.EmployeeLastName + ', ’ + e.EmployeeFirstName AS ‘Text’,
e.EmployeeLastName + ', ’ + e.EmployeeFirstName AS ‘SelectedText’
FROM
Employees AS e
INNER JOIN
Positions AS p ON e.PositionID = p.PositionID
INNER JOIN
Departments AS d ON p.DepartmentID = d.DepartmentID
ORDER BY
e.EmployeeLastName DESC, e.EmployeeFirstName DESC
Here is the way it queries in SQL Server.
And here is the way the Tree View looks with Auto Sort turned off.
Notice that the Departments and Positions are sorted alphabetically still, but the employees aren’t. Next, I turned on Auto Sort and here is what the Tree View looks like.
So I tried the trick pdibenedetto suggested, and thanks for that, by the way. With the html code, I was able to sort the paths as desired. Using the example above, I modified the query to use the html trick and here is what it looks like.
SELECT
‘
The query results in SQL look like this.
(continued in next message)
Now you can order the Departments and Positions according to their respective sort order fields by using this trick and it looks like this. Remember, I set the department and position sort order fields to purposedly reverse sort them, and that is what you see below.
So, in summary, you can write a query to order the Text fields whichever way you want, or just use Auto Sort if you want them in alphabetical order and the Tree View will take care of it for you.
If you want the Paths to be in alphabetical order, you need do nothing , as the Tree View will take care of that for you by default. But if you want to order the Paths in a particular order, you will need to use the html trick with a sort order field for each path, as shown above.
It’s not that easy, but it can be done. Thanks again for that trick, pdibenedetto. I will be using it next week.
RRRancher, I'm glad you have been useful. 
For me, that's the beauty of Ignition... have a lot of features that are easy to use, and if not, it's so open, that always are some workaround. 
Regards,
Thanks a lot. The solution worked great !!!
how can I link the returned values of a python script to display in the tree view? Any ideas
Just populate the ‘Items’ dataset from your script. Something likeevent.source...getComponent('Tree View').data = mydatasetYou’ll have to use the system.dataset.toDataSet() function to get the right type of dataset.