Parent-Child tree problem

[phoenix_me]

I'am struggling with simple Parent-Child problem.
I have two columns like below

ID       ParentID
MAIN 
12       MAIN
07       MAIN
04       07
08       07
91       12
06       04
02       04
11       02

Now I need to achieve a correct order (Tree), unfortunatelly I am going into endless loop .


Anyone with resolution of this problem?

 

[RKR]

What is it exactly that you wish to achieve. Is it only ordering and what is the correct order. Maybe you can put a code sample as well.

 

[phoenix_me]

What is it exactly that you wish to achieve. Is it only ordering and what is the correct order. Maybe you can put a code sample as well.

Please imagine a family (the rows where ParentID = NULL), each family can have a members which can be other family, brotheres, sisters etc...

When you put my example into tree you will have

MAIN
 |-12
 |  |-91
 |-07
    |-04
    |  |-06
    |  |-02
    |     |-11
    |-08

It will be enough to put data in correct order, but more usefull it will be to create also additional column with information about how deep in structure is each member, so I will be able to move data on report from left margin.

I was starting witch such code:

DEFINE TEMP-TABLE tmpMenu
    FIELD tmpCounter AS INTEGER
    FIELD tmpMenuID AS CHARACTER
    FIELD tmpParentMenuID AS CHARACTER
    FIELD tmpMenuDesc AS CHARACTER
    FIELD tmpSequence AS CHARACTER.

DEFINE VARIABLE cParentID AS CHARACTER NO-UNDO.
DEFINE VARIABLE iCounter AS INTEGER INITIAL 0 NO-UNDO.

FOR EACH Menu:
    iCounter = iCounter + 1.
END.

FOR EACH Menu WHERE MenuID = 'MAINMENU':
    /*DISPLAY MenuID ParentMenuID MenuDesc.*/
    cParentID = Menu.MenuID.    
    CREATE tmpMenu.
    ASSIGN
        tmpMenu.tmpCounter = iCounter
        tmpMenu.tmpMenuID = Menu.MenuID
        tmpMenu.tmpParentMenuID = Menu.ParentMenuID
        tmpMenu.tmpMenuDesc = Menu.MenuDesc.
        /*tmpMenu.tmpSequence = Menu.Sequence.*/
END.

REPEAT WHILE iCounter > 1:
    FOR EACH Menu WHERE ParentMenuID = cParentID:
        iCounter = iCounter - 1.
        CREATE tmpMenu.
        ASSIGN
            tmpMenu.tmpCounter = iCounter   
            tmpMenu.tmpParentMenuID = Menu.ParentMenuID
            tmpMenu.tmpMenuID = Menu.MenuID
            tmpMenu.tmpMenuDesc = Menu.MenuDesc.
            /*tmpMenu.tmpSequence = Menu.Sequence.*/
    END.
    cParentID = tmpMenu.tmpMenuID.
END.

FOR EACH tmpMenu BY tmpCounter DESCENDING:
    DISPLAY tmpCounter tmpMenuID tmpParentMenuID tmpMenuDesc tmpSequence.
END.

DELETE tmpMenu.

But don't suggest this code as it's not working , need to revew the my initial idea.

BTW I have problem with line

tmpMenu.tmpSequence = Menu.Sequence.

when uncommented the progress is complaining about wrong syntax. I know that there is a problem with word sequence but using "sequence" or 'sequence' is not resolving a problem.

 

[RKR]

Maybe this example will get you going. The most important thing is that you have to call the procedure maketree recursively.

DEFINE TEMP-TABLE ttTabel
    FIELD ID        AS CHARACTER
    FIELD ParentID  AS CHARACTER.

CREATE ttTabel.
ASSIGN ID       = "MAIN"
       ParentID = "".
       
CREATE ttTabel.
ASSIGN ID       = "12"
       ParentID = "MAIN".
CREATE ttTabel.
ASSIGN ID       = "07"
       ParentID = "MAIN".
CREATE ttTabel.
ASSIGN ID       = "04"
       ParentID = "07".
CREATE ttTabel.
ASSIGN ID       = "08"
       ParentID = "07".
CREATE ttTabel.
ASSIGN ID       = "91"
       ParentID = "12".
CREATE ttTabel.
ASSIGN ID       = "06"
       ParentID = "04".
CREATE ttTabel.
ASSIGN ID       = "02"
       ParentID = "04".
CREATE ttTabel.
ASSIGN ID       = "11"
       ParentID = "02".
DEFINE VARIABLE iLevel  AS INTEGER  NO-UNDO.
RUN MakeTree("").
PROCEDURE MakeTree:
    DEFINE INPUT PARAMETER icParentID       AS CHARACTER    NO-UNDO.
    
    FOR EACH ttTabel WHERE ParentID = icParentID:
        DISPLAY ttTabel.ID ttTabel.ParentID iLevel.
        RUN MakeTree (ttTabel.ID).        
    END.
    
END PROCEDURE.

 

[phoenix_me]

Maybe this example will get you going. The most important thing is that you have to call the procedure maketree recursively.

DEFINE TEMP-TABLE ttTabel
    FIELD ID        AS CHARACTER
    FIELD ParentID  AS CHARACTER.

CREATE ttTabel.
ASSIGN ID       = "MAIN"
       ParentID = "".
       
CREATE ttTabel.
ASSIGN ID       = "12"
       ParentID = "MAIN".
CREATE ttTabel.
ASSIGN ID       = "07"
       ParentID = "MAIN".
CREATE ttTabel.
ASSIGN ID       = "04"
       ParentID = "07".
CREATE ttTabel.
ASSIGN ID       = "08"
       ParentID = "07".
CREATE ttTabel.
ASSIGN ID       = "91"
       ParentID = "12".
CREATE ttTabel.
ASSIGN ID       = "06"
       ParentID = "04".
CREATE ttTabel.
ASSIGN ID       = "02"
       ParentID = "04".
CREATE ttTabel.
ASSIGN ID       = "11"
       ParentID = "02".
DEFINE VARIABLE iLevel  AS INTEGER  NO-UNDO.
RUN MakeTree("").
PROCEDURE MakeTree:
    DEFINE INPUT PARAMETER icParentID       AS CHARACTER    NO-UNDO.
    
    FOR EACH ttTabel WHERE ParentID = icParentID:
        DISPLAY ttTabel.ID ttTabel.ParentID iLevel.
        RUN MakeTree (ttTabel.ID).        
    END.
    
END PROCEDURE.

In your example you are creating tables and you need to know the number and name of tables before running your code.

What if I don't know how many records initially I have in my source database?

Thank you very much for help, let me show you mine resolution which I devolped few minutes ago. (maybe someone else will find it usefull).

I used a procedure which calls itself (recursively). The whole code is like below:

  DEFINE TEMP-TABLE tmpMenu
    FIELD tmpMenuID AS CHARACTER
    FIELD tmpParentMenuID AS CHARACTER
    FIELD tmpMenuDesc AS CHARACTER
    FIELD tmpSequence AS CHARACTER.

DEFINE VARIABLE cParentID AS CHARACTER NO-UNDO.

FIND Menu WHERE MenuID = 'MAINMENU'.
    cParentID = Menu.MenuID.    
    CREATE tmpMenu.
    ASSIGN
        tmpMenu.tmpMenuID = Menu.MenuID
        tmpMenu.tmpParentMenuID = Menu.ParentMenuID
        tmpMenu.tmpMenuDesc = Menu.MenuDesc.
        /*tmpMenu.tmpSequence = Menu.Sequence.*/

PROCEDURE Drzewo:
    DEFINE INPUT PARAMETER cParentID AS CHARACTER.
    FOR EACH Menu WHERE ParentMenuID = cParentID:
        IF AVAILABLE Menu THEN 
        DO:
            CREATE tmpMenu.
            ASSIGN
                tmpMenu.tmpParentMenuID = Menu.ParentMenuID
                tmpMenu.tmpMenuID = Menu.MenuID
                tmpMenu.tmpMenuDesc = Menu.MenuDesc.
                /*tmpMenu.tmpSequence = Menu.Sequence.*/
            RUN Drzewo (INPUT tmpMenu.tmpMenuID).
        END.   
    END.
END PROCEDURE.

RUN Drzewo (INPUT cParentID).

FOR EACH tmpMenu:
    DISPLAY tmpMenuID tmpParentMenuID tmpMenuDesc tmpSequence.
END.

DELETE tmpMenu.

Do you know what I can do with word 'sequence' which I can't use?? (Explanation in my previous post)