3 Replies Latest reply on Nov 12, 2008 1:32 PM by TSGal

    Update related records



      Update related records


      I am working on a database that I use to keep track of any jobs we work on, equipment that's used, etc.  I assign a job # that is related to fields in other tables.  If I change the job # is one table, the records do not update in the other tables.


      How can I get the records to update if the job # is changed?

        • 1. Re: Update related records

          This sounds like a structural issue. You have fallen into the trap of using meaningful data as a primary key. 


          What you need to do is to have a serial number field in your Jobs table that is used as the primary key for links to related records. This field is not be to used for anything else! Also, you can have a serial number field that will be the Job Number.


          If you do this, the Job Number can potentially be changed without affecting links to related records.


          Make sense? 

          • 2. Re: Update related records

            I currently have a job ID field that is the primary key.  I use this to link to details regarding the job.  If I need to change that number, there is not a way I can do that and have al details linked to the job change as well to remain linked?


            I understand what you're saying about a serial number, like an autonumber.  Is that the only way to allow for changes to the Job ID?



            • 3. Re: Update related records



              Thank you for your post.


              If you change your primary key, then the link to the other table is changed, and you will either not see any data, or the data of another related record(s).  What you may want to do is change the related key first, and then switch back to the original layout and change the key.



              FileMaker, Inc.