14 Replies Latest reply on Nov 14, 2016 1:56 PM by erolst

    Updating nested parent-child relationships?

    tkessler45

      I have a table of records that each have an ID, some record-specific attributes (like "ItemName") and then a "ParentID" field who's relationship is a self-join to the same table's ID. With this I can create a virtual tree of nested parent-child records.

       

      What I would like to do with this setup is be able to select any record in the tree and then update the same attribute for all of that item's children, grandchildren, and any that have a similar relation, so that from that point down they match. Doing this for all immediate child nodes of a selected record is easy with SQL such as "UPDATE table SET ItemName=<new_value> WHERE ParentID=<current_record_id>"; however, this will not address grandchild nodes and great-grandchildren, etc.

       

      Performing a find/replace approach in FileMaker is similar to the above, since a find for all records that have a given value for "ParentID" will only show the direct children of that parent, and not include all children, grandchildren, etc.

       

      My approach so far is to script a recursive search for children and update, but its somewhat clunky. If anyone has ideas on a better way to approach this, I'd love to see it.

       

      Thanks!

        • 1. Re: Updating nested parent-child relationships?
          philmodjunk

          A looping (recursive is a kind of looping) algorithm is probably your only option.

           

          But I'd also take a careful look at why you have to update the data in this manner in the first place. It sounds like you may be storing multiple copies of the same data in different records when you might not need to do that in the first place...

          • 2. Re: Updating nested parent-child relationships?
            erolst

            If each generation is related to its progenitor by the same relationship, you could

             

            Go to Layout [ LayoutForParent ( Parent ) ]

            # [ Find records, update, etc. ]

            Loop

              Go to Related Record [ Child ( LayoutForChildren ) ; matching records only ; all related of found set ]

              Exit Loop if [ Get ( Last Error ) // or if you want it specifically, the error for 'No Related Records' ]

              # [ no children of that generation? You're done ]

              Replace Field Contents [ Table::fieldYouWantToUpdate ; ParentGeneration::fieldYouWantToUpdate ]

              Go to Related Record [ Child ( LayoutForParent ) ; matching records only ; all related of found set ]

              # [ this last step brings the found set into a parent context, so they can find their children ]

              # [ this should work with an implicit self-join where the different layout switches TOs ]

            End Loop

             

            assuming you have the relationship

            Parent::id = Child::id_parent

             

            Haven't tested this, but it seems plausible.

             

            btw, why copy over data when you can see and use that data via the relationships?

            • 3. Re: Updating nested parent-child relationships?
              tkessler45

              The main reason for this is association of devices to owners. I have a series of devices that I need to assign to people, which themselves may be part of groupings (one device having child devices such as a computer having an associated keyboard and mouse). With each of these grouping, I'd like to select the parent device and assign it to a person, and then have all child devices be updated with that assignment regardless of whether they are child, grandchild, or further down the chain.

               

              I do not want to flatten this hierarchical approach, because I need to both group devices for deployment as well as then assign that group to the end user. The point of nesting is so that a sub-group could then be moved to a second parent and then have all of its children be updated. For instance, if a workstation box has a laptop (with a keyboard and mouse for that laptop), and an ipad (with an Apple Pencil for the iPad), then the laptop and ipad will be children of the box, the pencil a child of the ipad, and the peripherals children of the laptop. When the box is assigned to a person, then that person is responsible for all items and I want each items' record (or records in a separate assignments table) to reflect this. I'd like to then be able to move the laptop to a new box, and with a reassignment done on the laptop's record only, have the associated box also be updated for the laptop's peripherals.

              • 4. Re: Updating nested parent-child relationships?
                tkessler45

                I can use the relationship, but im still stuck with finding it through an arbitrary number of nested records. If it were just a single order parent-child relationship then that would work, but I may have some relationships two levels deep, and some 3+ levels deep. I'd still have to search up the relationships using some recursion / loop.

                • 5. Re: Updating nested parent-child relationships?
                  erolst

                  tkessler45 wrote:

                  but I may have some relationships two levels deep, and some 3+ levels deep

                   

                  That doesn't matter because each generation takes care of its own “data needs” – and as long as any current level has children of its own, you continue downwards ‘till there were none'.

                  • 6. Re: Updating nested parent-child relationships?
                    philmodjunk

                    I do not want to flatten this hierarchical approach, because I need to both group devices for deployment as well as then assign that group to the end user.

                    Well, one you do this:

                     

                    With each of these grouping, I'd like to select the parent device and assign it to a person, and then have all child devices be updated with that assignment regardless of whether they are child, grandchild, or further down the chain.

                    You did, in a way "flatten" the data by copying this value to every record.

                     

                    In theory, this info need only be stored in the "root" item and left empty for all "children" of it. If you assign everything to a different user, you just update the "root". If you take some of the items away and assign them elsewhere, you'd traverse the structure to find the items and link them to a new "tree" with, again, a single root record to update to show the "owner". That might be a very limited update ( to disconnect a single item and either link all of it's associated items to a different tree or make it a new tree with it as the new root), or very extensive if you have remove different items from different branches of that tree.

                     

                    Either way, it's hard to beat recursion for making it happen.

                    • 7. Re: Updating nested parent-child relationships?
                      tkessler45

                      erolst wrote:

                       

                      tkessler45 wrote:

                      but I may have some relationships two levels deep, and some 3+ levels deep

                       

                      That doesn't matter because each generation takes care of its own “data needs” – and as long as any current level has children of its own, you continue downwards ‘till there were none'.

                      Right, but I'm gathering there's no way to do this without recursion through the relationship to either update data in a particular field for all records, or locate a specific value in a child relationship.

                      • 8. Re: Updating nested parent-child relationships?
                        David Moyer

                        Hi,

                        I was playing with the concept that Phil mentioned - that the attribute data only need to be stored in the most senior record.  Unfortunately, I can't get this calculation to work for anything below the first child record.

                        TheAttributeToUse = If ( IsEmpty(Attribute); MyParent::TheAttributeToUse; Attribute )

                        • 9. Re: Updating nested parent-child relationships?
                          philmodjunk

                          That's an interesting Idea David, but I hadn't actually thought in terms of a calculation at all, just discussed the theory of tree data structures. I was thinking in terms of directly accessing/finding the root record anytime you need this data. In many contexts, accessing that first record should be pretty easy and fast to get to.

                          • 10. Re: Updating nested parent-child relationships?
                            erolst

                            tkessler45 wrote:

                            Right, but I'm gathering there's no way to do this without recursion through the relationship to either update data in a particular field for all records, or locate a specific value in a child relationship.

                            “Gathered” as in "listened to my gut feeling", "theoretically contradicted" or "empirically tested and failed"?

                             

                            Haven't thought about the latter task, but the former is what we were talking about all along, right? If you abstract the references, I gather this could work by simply clicking into a field and launching the script to write this down into all children.

                             

                            What philmodjunk suggested would work in the same way, but you'd need a user interface to configure the intended changes.

                            • 11. Re: Updating nested parent-child relationships?
                              erolst

                              David Moyer wrote:

                              TheAttributeToUse = If ( IsEmpty(Attribute); MyParent::TheAttributeToUse; Attribute )

                              How about

                               

                              cAttributeToUse =

                              Case ( IsEmpty ( Parent::ID ) ; attribute ; Parent::cAttributeToUse )

                               

                              If there is no parent, you're the matriarch and need to use your own value; otherwise, inherit it.

                              • 12. Re: Updating nested parent-child relationships?
                                David Moyer

                                Here's a recursive script for creating a list of child IDs:

                                Capture.PNG

                                This script would be called from a second script:

                                Capture.PNG

                                • 13. Re: Updating nested parent-child relationships?
                                  Malcolm

                                  When I learnt how to handle this situation it was described as a "hamburger join" and it's useful for any type of "recipe."

                                   

                                  In the hamburger model, a customer wants a hamburger but the delivery dock doesn't receive hamburgers. It receives buns, meat patties, vegetables, sauces, etc. The kitchen needs to know how to put those pieces together to make a hamburger. That information is called a recipe. In your case, a person wants a workstation and you're in the workshop putting it together with boxes, boards, RAM and peripheral accessories.

                                   

                                  At present your model is to link the ingredients table directly to the ingredients table. That works but it is made much easier when you have a join table that sits between the two instances of the ingredients table. it allows you to re-use components. You can create standard recipes, and combine recipes to create combo-packs, meal deals, and so on. In your case it will be things like the workstation bundle you described.

                                   

                                  The other thing you appear to be doing is using the recipe book as the record of sales and ownership. The recipe book belongs to the cook. The person at the till keeps a record of sales. They are different things.

                                   

                                  When you have a standard recipe for a workstation bundle you can easily manage the acquisition of the materials and the provisioning of the equipment to a person. In the workshop you have people putting together workshop bundles according to your recipe. Everything that goes into the box has an equipment ID and the box has its own ID. That becomes a trackable product. When you receive a order for a workstation bundle, you haul a box off the shelf, scan the equipment ID on the box and input the person's staff ID or whatever. That is now a fulfilled order.

                                   

                                  In your case, you probably keep a table of ownership. That is simply a matter of keeping a join table which contains the order ID and the Staff ID and a date field. When the equipment changes hands, all you need to do is add a new line to the ownership portal with the staff id and date. That gives you a historical record.

                                  1 of 1 people found this helpful
                                  • 14. Re: Updating nested parent-child relationships?
                                    erolst

                                    David Moyer wrote:

                                    Here's a recursive script for creating a list of child IDs:

                                    Or create a recursive calculation:

                                     

                                    cChildIDList =

                                    List (

                                      id ;

                                      Case (

                                        not IsEmpty ( Child::id ) ;

                                        List ( Child::cChildIDList )

                                      )

                                    )