3 Replies Latest reply on Feb 15, 2012 11:43 AM by philmodjunk

    Nested loops on same table

    lannycook

      Title

      Nested loops on same table

      Post

      HI all,

      I am trying to loop through parent records in a table that finds the childred for each parent and then loops through those.  What I have tried results in losing the first cursor on the parent records and dropping out after the first parent.  I am assuming that the cause is that both cursors are based on the same table.

      Go to Layout ["Item_List" (Item)]

      Perform Find [ Specified Find Requests: Find Records; Criteria: Item:: Default_Flag: "Yes"] [Restore]  {returns 9 rows}

      Go to Record/Request/Page [First]

      Loop

           Go to Related Record [ From Table: "Item"; Using Layout: "Item_Children" (Item) ] [Show only related records]  {returns 2 rows}

           Loop

                 Go to Record/Request/Page [Next; Exit after last]

           End Loop

           Go to Layout [ "Item_List" (Item) ]  {show only the 2 rows from Item_Children layout}

           Go to Record/Request/Page [Next; Exit after last]  {since above cursor was already on the last row this exits loop}

      End Loop

       

      What is a good way to do this? 

      Thanks for your help.  Lanny

       

        • 1. Re: Nested loops on same table
          philmodjunk

          Yes, you are correct that the fact that both layouts are based on the same table occurrence (Item) is the source of your trouble. For a given window, each table occurrence has it's own found set, current record (your cursor) and sort order. Different layouts based on the same table occurrence thus share the same found set etc.

          There are two options:

          Go to Manage | Database | Relationships and use the duplicate button (two green plus signs) to make a new occurrence of Item, then base the "chlid" layout on this occurrence instead of the original.

          Open a new window with the go to related record step so that you have one found set in the opened window separate from the found set of records in the original window. close the window when done.

          And one final issue. I don't think your current go to related record is going to work because a relationship requires two table occurrences and you are using the same table occurrence for both the starting layout and the occurrence specified in the go to related records step. Doing this, if I remember correctly will just pull up the same found set on layout 2 as is already present in layout 1, it won't pull up a list of child records via a relationship.

          • 2. Re: Nested loops on same table
            lannycook

            PhilModJunk,

            Thanks for your prompt answer.  Your method did indeed work.  I actually had tried most of that, including the second table occurrence, with the exception of opening a new window.  That was the trick!

            Thanks Again.

             

            • 3. Re: Nested loops on same table
              philmodjunk

              I do this all the time without opening a new window--it can cause issues on windows systems if the windows are maximized. Simply using a different layout basd on the new occurrence should work here.