11 Replies Latest reply on Jan 30, 2017 9:22 AM by philmodjunk

    syncing parent/child records options

    ericjlindholm

      I am optimizing my sync scripts that update all child records.

       

      I am testing a large sync script that runs on the server and is triggered by any major edits to a parent record.

      while looping through the child records:

       

      do you prefer to script a list of Set Field As

      or

      auto enter calc with a Let([x = GetField("")];  ). to trigger the updates when record is modified.  I would just set a field child::updateTrig to 1 to trigger the re-evaluation of all the auto calc of child records that need to remain synced but can't be calculations. 

       

      basic example.  I want a text field (eventItems::eventName) that contains the current value of a field in the parent record event::name. 

       

      In theory, I do not need to update these fields while editing the child records because the values would not change unless I edit the parent record so maybe I am adding a lot of overhead while make edits to the child records with the Let option.  but if I am updating eventItem::eventName when I call this script for any other edits, I would be adding the overhead to the update script.

        • 1. Re: syncing parent/child records options
          keywords

          To my mind it seems a bit like you may be messing around with what is in fact a many-to-many relationship here. If that is so, you should really look at a different approach than what you are calling syncing. Can you explain the set up in more detail?

          • 2. Re: syncing parent/child records options
            ericjlindholm

            these are all one to many relationships.  event----<eventItems.  by sync, I just mean update a field in a child record when the parent is edited without making the child record field an unstored calculation.

            • 3. Re: syncing parent/child records options
              philmodjunk

              But why the sync?

               

              Data does not need to be copied from parent to child in most cases. Any data in the parent is easily accessible to a child record via the relationship without having to copy the data to the child.

              • 4. Re: syncing parent/child records options
                ericjlindholm

                There are items that I need for TO relationships.  Status, start/end time stamps event manager ids and a few others.

                • 5. Re: syncing parent/child records options
                  philmodjunk

                  Without looking at your data model, the need to copy so much data into the child records suggests that you have some issues with that data model.

                  • 6. Re: syncing parent/child records options
                    ericjlindholm

                    i've had my data model looked at by developers from large filemaker firms when they added some features that were beyond my skills at the time.  The functionality they added require some of these fields as well.  Im not saying that its perfect but lets assume I have a legitimate need for the request i made.

                    • 7. Re: syncing parent/child records options
                      philmodjunk

                      All such large "batch updates" have a potential problem: Unless you can guarantee that no users will be editing records at the time--or just left the changes uncommitted on a record and never closed the file, specific records might get locked out of the update. You may need to test for locked records (if you loop through them) or do a find for non-updated records (if you do a "batch update" via replace field contents).

                       

                      You might consider making the updates of this data "event driven" where a trigger performed script updates the child records for a single parent at the time that the parent data is updated.

                      • 8. Re: syncing parent/child records options
                        ericjlindholm

                        That has been a long standing concern.  My scripts loop through portal rows so that i can revert all changes and notify the user that one of the child records is locked and that no changed were made to any records.  I believe this method has been referred to as "transactions" by some developers.

                         

                        Most of my child records are drop downs and check boxes.  with the precise addition of some commit steps, we rarely end up with any record locks. 

                        • 9. Re: syncing parent/child records options
                          philmodjunk

                          I do not recommend looping through portal rows to do this. Scripts that have to interact with portals  to get the job done represent what I consider to be an example of "brittle" database design as they are quite vulnerable to failing to work after (sometimes very minor) changes are made to the layout where the portal is located.

                           

                          You can get a transactional update without looping through portals using another approach such as a global match field that you update to match to a different record with each iteration of your loop.

                           

                           

                           

                          • 10. Re: syncing parent/child records options
                            ericjlindholm

                            Phil,  that sounds awesome.  Do know of any links to any demo files of that? or any other resources?  that would be much appreciated.

                            • 11. Re: syncing parent/child records options
                              philmodjunk

                              It's not that complicated.

                               

                              The fact that you are using a portal means that you already have a relationship that can get you a list of primary keys from the related table.

                               

                              So if you have this relationship for your portal:

                               

                              Parent----<Child

                               

                              Then you can add this relationship:

                              Parent---Child|Selected

                              Parent::gSelectedChildID = Child|Selected::PrimaryKey

                               

                              A rough outline of the script looks like this:

                               

                              Set Variable [$IDList ; value: List ( Child::PrimaryKey ) ]
                              Loop    Set Variable [ $K ; Value: $K + 1]

                                 Exit Loop if [$K > valueCount ( $IDList ) ]

                                 Set Field [Parent::gSelectedChildID ; GetValue ( $IDList ; $K ) ]

                                 Put code to update the related record here...

                                 Set Field [ChildSelected::FieldNameHere ; ....

                               

                               

                              End Loop

                               

                              Note that this is very similar to the relationship used for MagicKey type updates except that we are just updating existing records in this case.