2 Replies Latest reply on Aug 18, 2015 4:41 PM by danjamins

    Updating Primary Keys

    danjamins

      Title

      Updating Primary Keys

      Post

      Hi All,

      I'm building out a new CRM for a company, they are using a web based solution now.

       

      What I'm trying to do is update the primary keys in the system to just a serialized number in fmp. 

       

      How would I go about updating the key in the parent table (table A) and updating the foreign key of it in the child table (Table B) across all records? 

       

      I've never had to do this before, but the data structure of the database they have is all funky and I really don't want to have to manually clean this up as there are 11K or more records in this database under Table A.

      I figured I could write a script with a loop that uses the replace field contents somehow, but I can't quite wrap my head around this. 

      Any help is much appreciated. 

        • 1. Re: Updating Primary Keys
          GuyStevens

          I would create a new ID Field and keep the old ID's.

          Then you can use "Replace Field Contents" once, not in a loop. To fill the new ID field for all records with a serial number.

          Then you need to create a relationship between the table and the related table based on the Old ID so they are still correctly related.

          In the related table you also make a new field for the new Foreign ID.

          Then you use a Replace Field Contents step again (not a loop) to fill in the new ID from the main table.

          When you are certain that the new ID's have been filled in correctly you can change the relationship to base it on the New ID fields. 

          • 2. Re: Updating Primary Keys
            danjamins

            Thank you Guy, I'll give this a try, I appreciate the help.