Doing some folder path reconciliation on a NAS drive via Ignition. I wanted to first scan all the folders to see where everything was at so I could display them in a tree view in a Vision window. Here’s a sample of how the data is stored -
I just used os.walk and saved each folder to the fullPath column of my table pathreconciliation.
Here’s my attempt at the SQL query for the Items of the tree
SELECT REPLACE(fullPath,SUBSTRING_INDEX(fullPath, '\\', -1),'') as 'path',REPLACE(fullPath,'\\','//') as 'fullPath',
fullPath as 'text'
FROM pathreconciliation
WHERE depth <= 3
ORDER BY fullPath
and here is what I am seeing -
It is almost there but as you can see children are duplicated to be their own parent item as well and I am not sure how to fix this.
Any ideas how to fix this? I can edit the table if need be, I have full control over it if there’s way to manipulate this data into multiple columns if that helps somehow.
Do you just need to update the ‘Separation Character’ property to a backslash? Or possibly replace the values in your query; the double backslashes at the root may make things weird.
Adding on what PGriffith said, It may be possible the slashing of network mounted drives. It could be that ignition is using a slash as a special escape character. IE: To display “\\” as a string in ignition scripting, you may have to concatenate two additional “\\”'s like so:
Another possible avenue (I am unsure if this is desired), but what if you adjusted your query different like…
For child directories (or, sub directories) change the “isDir” value to “2” indicate it is not a directory but a child of the parent. Then add an additional where condition of to say where it is not a child directory
WHERE depth <= 3 and isDir not > 1
OR
Added another column with a name of “isSub” with a value of 1 to indicate that it this data row is a Child. To indicate it is the parent the value of “isSub” would be 0. Again adding a where statement adjusted to account for that
How is he populating the treeview with the paths... I can write a query to retrieve the paths, but I can't script or bind a query so that the treeview becomes populated by the paths.
Tree Views require recursive items, so no dataset can possibly provide it. But you can transform a dataset that contains a flattened tree to construct the recursive tree the component needs.
