Custom query function

Suite and Optimus Suite 5
#1

I’m using the following statement to get data from a db into a dhtmlx tree:

<cfset request.tree.render_sql(“processID,processUID,processLabel,processParentID,processSortOrder,processLevel,processVersion FROM ao_process WHERE CompanyID=‘#session.user.companyID#’ AND processStatus = #status# ORDER BY processSortOrder ASC”,“processID”,“processLabel”,“processSortOrder”,“processParentID”, “processUID”, “processVersion”)>

the issue is that i have multiple records with the same processid to show up in the tree under the same processparentid. this shows as in the attachment. the childs of a processID are showing three times (i have three items with the same processID) under one parent, and the duplicates don’t have an ID when i click on the item in the tree. so they are not working. they give me an “undefined”.

See attached marked with “X”

i would like to :

  • have the second and third item in the tree to be clickable
  • have the child items to show up under the second and third item

See attached image “2.png”

How can i achieve this?

Can i use another/custom query? Or do i need to modidy the query? Or do i use an beforerender to do this?



#2

Tree will not work correctly if you have non-unique ids for items
This is a basic rule of component, and there is no way to override it.

#3

Ah!

Is there a way where i can filter out those non-unique items using a sql statement?

#4

and is it possible to load from an XML file with multiple non-unique ids ?

#5

file with multiple non-unique ids ?
Nope, this is limitation of tree component, not connector.

#6

How could i achieve to filter these items out with sql?

If I do a group by statement on the processID it still gets/shows the records in the tree.

#7

i tried this with a query, on the DB itself the query works, but in the tree i get an XML error.

Error type: DataStructure
Description: XML refers to not existing parent

#8

Can you provide the result xml of the query.

Such error occurs if top level id in xml doesn’t match to top level id in the tree

For static loading your data must look as

// 0 - top level id

for dynamic loading - it must contain id of requested branch