Table algorithm required... complex spec...

[rjlohan]

I have a database table that contains a list of categories. The table schema is:

catID
catText
superCatID

Where catID is an AutoNumber field, and superCatID points to the parent of a category (this may be null, for a top level category)


And a possible table might be:

catID   catText   superCatID
1       A        
2       B
3       C
4       AA        1
5       AB        1
6       BA        2
7       BB        2
8       ABA       5
9       CA        3
10      ABAA      8

I want a function that will parse this table, or whatever, and build a structured list of the categories, to fill a combo. This is to simplify a UI, so that catIDs don't have to be remembered.

e.g, the above table should come out looking something like:

A        
-AA
-AB
--ABA
---ABAA
B
-BA
-BB
C
-CA

Now, who wants to be the genius that solves this one?

[honeybee]

Genius????? I already have written a procedure to do just the same for our app. Use recursion, if you know how to use it




.

[rjlohan]

Yes, I understand recursion...

However, my brain is blank as to what exactly I need to recurse...

[ashay]

hi,

i hv created a dynamic hierarchy that way....but my table structure is a bit different.....n i havent used recursion.....

i hv included 2 more fields into the table called "parentCatIds" and "parentCatNames"

the table like this
:
catID catText superCatID parentCatIDs parentCatNames

1 A
2 B
3 C
4 AA 1 1 A
5 AB 1 1 A
6 BA 2
7 BB 2
8 ABA 5 1\\5 A\\AB
9 CA 3
10 ABAA 8

i hv just shown u one example......for
A
-AA
--AB
-----ABA


does this makes sense......actually it shud....coz i have implemented in my code.......


regds,
ashay

[VictorB212]

I just showed a French guy how to do this in another thread, so I suggest doing a search on posts by me in the last month...

[honeybee]

Goes something like this:

The procedure will accept an argument, which is the SuperCat. The procedure exits when its private recordset reaches .EOF. This is the exit point for any recursions too.

The code will go something like this:

Private Sub Recurse(SuperCat As String)
    Dim rs As RecordSet, strSQL As String
    strSQL = "SELECT * FROM YourTable WHERE SuperCat"
    If SuperCat = "" Then
        'This is only because you are using nulls as the topmost SuperCat
        strSQL = strSQL & " IS NULL"
    Else
        strSQL = strSQL & " = '" & SuperCat & "'"
    End If
    Set rs = YourDB.OpenRecordset(strSQL)
    With rs
        Do While Not .EOF
            'pick up each record and process it
            Recurse(.Fields("CatID")) 'To process any records where 
                                                    'present CatID is a SuperCat
            .MoveNext
        Loop
        .Close
    End With
End Sub

Yeah, and pass an empty string to it the first time...
.