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

    Update related records

    msbehavinmaiden

      Title

      Update related records

      Post

      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
          davidhead
            

          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
            msbehavinmaiden
              

            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?

             

            Thanks.

            • 3. Re: Update related records
              TSGal

              msbehavinmaiden:

               

              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.

               

              TSGal

              FileMaker, Inc.