2 Replies Latest reply on Jan 20, 2017 3:21 PM by tods

    Changing table relationships.

    tods

      In the solution i am working on, I have a service record that is associated to an equipment table by phone number (this is bad).  The problem is that sometimes the phone number changes, so before things get too out of whack I need a way to go through all of the records in the equipment table, find the corresponding information in the service record table by phone number, get the primary key from the service record and put that into the foreign key field in the equipment table.

       

      What is the proper way to do this?  I can't seem to wrap my head around it.

       

      Thank you,

      Tod

        • 1. Re: Changing table relationships.
          philmodjunk

          Define a new field in your service record to serve as the _fk field of the (new?) primary key in equipment. If the primary key field is a new addition, make sure that all equipment have the needed new and unique value (auto-entered serial number or UUID, not a value entered by a user such as a serial number label on the equipment).

          Go to a layout based on the service record table. Put the new _fk field on this layout. Show All Records.

          Use Replace Field Contents with the calculation option to set the _fk field to the value of the related equipment record's primary key.

           

          Once you have used your current (bad) relationship to copy over the needed ID's, update your relationship to use the ID fields instead of the phone number.

          1 of 1 people found this helpful
          • 2. Re: Changing table relationships.
            tods

            Thank you,

            That worked perfectly!

            1 of 1 people found this helpful