Cool Tree View Code

I know that it has been a while since we published a Blog entry but we have been very busy. Leilani is growing quickly and doing very well overall. As you can see below she is hoping that the Red Wings win the cup again this year.

The following segment of code is something that I wrote a while ago when implementing a tree view in a form I was working on. I wanted to try something new to detect if a node needed the plus sign, indicating children were available. I was tired of always seeing the plus and having it expand to nothing. At the same time, I didn't want to load the tree with all of the children at startup either. The following is what I came up with.

SELECT
    CategoryID,
    Name,
    (SELECT TOP 1 CategoryID
     FROM Category SubCategory
     WHERE ParentID = Category.CategoryID) AS Child
FROM
    Category
WHERE
    ParentID IS NULL
ORDER BY Name

This code grabs all parent nodes (ParentID IS NULL) and the first child to that parent. The second block of code is how I populate the tree. If the Child field IS NULL, there are no children. If the child field has a value, there is at least one. As you may be able to tell by the synax, this is SQL Server code. The good news is that this also work with VFP code.

SCAN ALL
    This.oTree.Nodes.Add(,1, 'KEY_' + TRANSFORM(CategoryID), Name)

    IF NOT ISNULL(Child)
        This.oTree.Nodes.Add('KEY_' + TRANSFORM(CategoryID), 4, 'CHILD_' + TRANSFORM(CategoryID), "D")  
    ENDIF
ENDSCAN