how to write a recursive function to create a treeview from data in datawindow?

Question

I have a parent-child data in a datawindow from a table mytab(id, pid....) Then I want to use to data to create a tree in treeview.

I try to use recursive function, but have problem with datawindow as I use filter to change the data in datawindow.

here is my code:

of_addnode(treeviewitem node, rootrow):

int li_rows,li_newitem, i
treeviewitem li_tvitem

dw_1.SetFilter("pid = " + string(node.data))
dw_1.Filter( )

li_rows =  dw_1.RowCount()

if li_rows = 0 then
    return
end if

for i = 1 to li_rows
    li_tvitem.level = node.level +1
    li_tvitem.data = dw_1.GetItemNumber(i,"id") 
    li_tvitem.label = dw_1.GetItemString(i,"description") 
    li_tvitem.pictureindex = 1 
    li_tvitem.selectedpictureindex = 2
    li_newitem = tv_1.insertitemsort (rootrow, li_tvitem)  // insert a new node 
    of_addnode(li_tvitem,li_newitem)

    dw_1.SetFilter("pid = " + string(node.data))  //restore data back to filter, problem here. tree will have duplicate item
        dw_1.Filter( )
next

how to create a recursive function from one datasource datawindow for this case?

Answer 1

Here are some ideas... it sounds like you want to use the treeview control and I don't blame you as the treeview dataobject is not very refined because it shows the expand button even if there aren't any children and I haven't found an elegant way to deal with this.

One thing to consider, will manipulating the data on back-end help you? This works for using the treeview dataobject type or might help in making the recursion logic more simple.

This database view is in MySQL but could be developed for other databases. I snipped it at three levels of recursion and yes it was hard coded for six levels of recursion the max supported by this application. This works great if using a treeview dataobject control but you are stuck with the expand button problems when there are no children. This can also be useful for making recursion logic simpler by effectively giving you "path" to the item.

**DATABASE VIEW TO FLATTEN PARENT AND CHILD (HIERARCHY) RELATIONSHIPS **

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`%` 
    SQL SECURITY DEFINER
VIEW `v_category` AS
    select 
        `t1`.`parent_id` AS `parent1`,
        `t1`.`id` AS `id1`,
        `t1`.`title` AS `title1`,
        `t1`.`sort_order` AS `sort1`,
        `t2`.`parent_id` AS `parent2`,
        `t2`.`id` AS `id2`,
        `t2`.`title` AS `title2`,
        `t2`.`sort_order` AS `sort2`,
        `t3`.`parent_id` AS `parent3`,
        `t3`.`id` AS `id3`,
        `t3`.`title` AS `title3`,
        `t3`.`sort_order` AS `sort3`
from
    (((((`ld_category` `t1`
    left join `ld_category` `t2` ON ((`t2`.`parent_id` = `t1`.`id`)))
    left join `ld_category` `t3` ON ((`t3`.`parent_id` = `t2`.`id`)))
    left join `ld_category` `t4` ON ((`t4`.`parent_id` = `t3`.`id`)))

BUILDING TREEVIEW USING RECURSION

You are on the right track for populating the treeview control but without seeing your dataobject it is impossible to know if your setfilter and filter are working properly and what is going on. The key is to get the level from the insertitemXXX function and pass that into the first argument of the next insertitemXXX function. This simple example of the PB help always gets me on the right track. I see you are using treeviewitems but that doesn't really change things. Hope this helps.

long ll_lev1, ll_lev2, ll_lev3, ll_lev4
int  index

ll_lev1 = tv_list.InsertItemLast(0,"Composers",1)
ll_lev2 = tv_list.InsertItemLast(ll_lev1, "Beethoven",2)
ll_lev3 = tv_list.InsertItemLast(ll_lev2, "Symphonies",3)
FOR index = 1 to 9
    ll_lev4 = tv_list.InsertItemSort(ll_lev3, "Symphony # " + String(index), 4)
NEXT