3 Replies Latest reply on May 7, 2014 11:16 AM by philmodjunk

    5+ Self Joins or?

    ryanserra

      Title

      5+ Self Joins or?

      Post

                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                          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]
                          
                               Loop
                          
                                   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.

        • 1. Re: 5+ Self Joins or?
          philmodjunk

               You may find this demo file to be of interest: https://dl.dropboxusercontent.com/u/78737945/RecursiveBOMDemo.fmp12

          • 2. Re: 5+ Self Joins or?
            ryanserra

                 Thanks for the quick response.  The document's layout is really confusing at the moment.  Honestly have no idea what is going on and where the nested lookup (if any) is taking place.  For instance, when I select the "Basic Red Wagon" and get the items "Tow Bar Assembly" "Wheel"... how would I show the components that make up the wheel?

                 For example

                 Basic Red Wagon
                 "Wheel"
                       Bolt
                       Bearing
                       Tire
                            Rim

                 The Bolt, Bearing, Tire, and Rim would all be part of the same product table because they could be sold separately.  The Basic Red Wagon would be the finished kit but all the subcomponents can also be sold individually and I would like to display them all when The Basic Red Wagon is looked up.  It would be even better to show the subcomponents nested (indented form elements) to show they are children.

            • 3. Re: 5+ Self Joins or?
              philmodjunk

                   The example lists the components of that assembly along with the rest of the parts. It loops through the same self join repeatedly to generate a list of all basic parts listed both in the top level BOM and the BOM's specified for each assembled component. In theory, there's no limit built in to this approach as it can keep recursing (looping) through succesive BOMs until it reaches a part that is not an assembly.

                   (BOM = "Bill of Materials")