9 Replies Latest reply on Jun 14, 2012 6:57 AM by BruceHerbach

    Cascade Key Update

    psuchad

      FileMaker has a cascade delete option, but where is the cascade update? I would be nice if changing the primary key value could update key values in related tables.

        • 1. Re: Cascade Key Update
          TimAnderson

          Script Triggers?

          • 2. Re: Cascade Key Update
            psuchad

            That would require a script trigger on every layout in the database that has the key field placed on it.  It would also require a pretty intricate script to go to a layout for every related table and an update loop to update every record with that key.  This seems like a lot of work to maintain.  I may be wrong since I haven't used it in some time, but I do believe this was a feature of Microsoft Access 10 years ago.

            • 3. Re: Cascade Key Update
              beverly

              not to throw water on this, but I'm pretty much into NOT allowing cascade anything (including delete). Script it all. I have had to "update" key fields (when importing/syncing, for example), but then I use a tempKey field to hold the relationship, Go To Related (passing new key via script) and setting the new key with the Script Parameter. for "Delete" I try to set a field ("markToDelete") and change relationships, if needed, rather than actually delete. If an admin needs to "purge" those "markToDelete" records (any file), there's a script for that and likely just an "archive" rather than delete.

               

              Just MHO,

              Beverly

              • 4. Re: Cascade Key Update
                TimAnderson

                Agree Beverly. Every record has a 'deleted' key field that gets set to 1 if a user 'deletes' that record, it is never deleted - just never shows up in searches and never used in any reports.

                 

                I have forgotten how many times I have had to 'undelete' a record because a user ignored the warning and cliked the 'Delete' button rather than the default 'VCancel' button!

                • 5. Re: Cascade Key Update
                  comment

                  psuchad wrote:


                  I would be nice if changing the primary key value could update key values in related tables.

                   

                  I think it's pretty safe to say that if you need this, then something is wrong, with the possible exception of data migration - but migrations are rare and you wouldn't want regular features to be based upon migration requirements.

                  • 6. Re: Cascade Key Update
                    TimAnderson

                    Yup, scary what could happen, and easily implemented if required

                    • 7. Re: Cascade Key Update
                      BruceHerbach

                      What is it you are trying to do?  Changing the primary key for a record or the foreign key in a related record has the potential of breaking the relationship. 

                       

                      Ok that stated, I had a database that needed to import template record sets into a main set of tables.  The relationship was essentially a hierarchy so all of the new records had to point to the correct parent record.   In this setup,  the records were exported and then imported.  Primary and foreign keys were not included in the export.  When the parent records were imported/created,  the primary key was put into a variable.  Then when the related records were imported the Foreign key was setup as an auto enter/do not replace field with the value being the variable holding the parent record foreign key.  End result is the new record set had the correct keys and were correctly related.

                       

                      This could be done using a script with variables, finds and replace field contents.  As a script trigger it would probably have to be on the parent/top level record only.

                      • 8. Re: Cascade Key Update
                        psuchad

                        We have a very large client services system that tracks across various service types and agencies.  The database consists of about 15 files, all related to the main client master file.  The client master file alone contains 155 related tables (granted they are not all related via the client ID).  The issue that we have is when a person comes into the intake process they are not always initially found in the system due to various name spellings and nicknames used.  If they are not found they are added as a new client.  Weeks, months, sometimes years later we find that we have a duplicate person in the system.  The current process is to manually merge the client ID across all related tables.  This is more and more complex the further down the wormhole the client goes in the interconnected agencies and services.

                         

                        I understand the underlying risk of overwriting keys and it is not something that is taken lightly.  But in my opinion, I would rather have something that is setup on the relationship level rather than rely on a scripted solution to be maintained across the hundreds of tables.

                        • 9. Re: Cascade Key Update
                          BruceHerbach

                          You have a difficult setup.  If possible,  I would see if there is a way to do a more thourough search up front.  I know you have already tried and they still sneak through...:(

                           

                          In thinking about this,  the best method I can come up with is a scripted appraoch.  In psudo code:

                           

                          Update Primary record,  store both Old ID and New ID in variables.

                               Go to each table

                               Find all records with old key

                               Replace with new key

                          Done

                           

                          I would include a number of Do you really, really really want to do this steps.

                           

                          Good luck