AnsweredAssumed Answered

Updating nested parent-child relationships?

Question asked by tkessler45 on Nov 14, 2016
Latest reply on Nov 14, 2016 by erolst

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.