AnsweredAssumed Answered

5+ Self Joins or?

Question asked by ryanserra on May 7, 2014
Latest reply on May 7, 2014 by philmodjunk


5+ Self Joins or?


                    Part #                     Component Part #                     Count
                    A                     B                     1
                    A                     C                     1
                    A                     D                     2
                    B                     F                     1
                    C                     X                     1

     I have a spreadsheet document like the above table.  The Part # column contains many duplicate values.  The component column has many values that are contained in the Part # column and also have their own components (this relationship can be nested 3+ deep).  My question is, how can I display the data in a format like the following.

Part A
     - B, 1
     -- F, 1
     - C, 1
     -- X, 1
     - D, 2

                    Part A
                    B                     1
                    F                     1
                    C                     1
                    X                     1
                    D                     2


     I have taken the initial table and self joined it on the part number and then created another self join on the component.  This can give me somewhat of a desired result but to go further, I would have to create more self joins.

     I was playing around with the following code but I don't know how to take the $component value and find the matching value in the Part # column... and then do it again for for any components of that component... and so on.

                    Set Variable [$cnt; Value:Count (Parts::Component)]                     
                         Set Variable [$i; Value: 1]
                             Set Variable [$component; Value: GetNthRecord(Parts::Component; $i)]
                             Exit Loop If [$i >= #cnt]
                             Set Variable [$i; Value: $i + 1]
                         End Loop

      I can sort what I require in outside languages but I am not sure how to do this in Filemaker and have it display cleanly and organized.  Again, adding repeated self joins to get the desired result just doesn't feel right.