3 Replies Latest reply on Nov 7, 2012 4:29 PM by comment

    Multiple Layered Parent Record

    carlo.m

      I have a table in which I'm clasifying expenses.

       

      ID Name parentID

       

       

      And I have for example the following records:

      id Name parentID

      1 Marketting

      2 Print 1

      3 Radio 1

      4 Sponsored Mention 3

      5 30 Second Spot 3

       

       

      In the example listed above, records where created sequentially and simply sorting by creation order would give a true sort of the data but what happens when I add a 6th record (Flyers) that is a child of print (id=2).

       

      I want to be able to display a report that shows all records nested within its parent, even when there are 2 or more layers of parentIDs to a record.

       

      What is the simplest way to achieve this?

      The only thing that has come to mind is a recursive function with executesql:

      if(

      parentid="";

      if(result≠"";result&":")&ID;

      if( executesql("Select parentid from TableOccurrence where id=?";"";"";TableOcurrence::parentID)="";

      Recursive( ParentID_1&":"&ThisRecordID ; ParentID_2)

      )

       

       

      The recursive function would have two parameters:

      1) the result that will be compiling as each iteration runs seperating ids by a delimiter (":")

      2) the next id to run the test on

       

      If you have ever used quickbooks, I'm looking for something similar than to how they nest accounts and display them on reports like a P&L.