4 Replies Latest reply on Feb 11, 2013 12:22 PM by brian.curran

    Change an ID field to UUID without disconnecting relationships...

    brian.curran

      Hi all,

      Can anyone advise me on the best way to change serial numbers to UUID's without breaking any existing relationships?

       

      I originally designed the database using auto-enter serial numbers but changed to text based UUID's when we implemented GoZync for syncing local copies on iPads etc. As the original ID fields were changed to text fields, 'finds' are unreliable as searching for 1 will find record ID 1 and 10 etc. etc.

       

      So, I now need to change all the old ID's to UUID's and would appreciate any comments or suggestions.

       

      Thanks

      Brian.

       

      Using:

      FM Pro 12 Advanced

      FM Go

        • 1. Re: Change an ID field to UUID without disconnecting relationships...
          BruceHerbach

          Hi Brian,

           

          I just did the same thing for a large solution and Mirrorsync.  As long as the current IDs are unique,  you don't have to update/change the current records to a UUID value.  They can keep the current value.  Change the field to be type text in both the Primary and Foriegn keys.  At this point do a check,  but your relationships should still be intact.

           

          Next change the Auto enter from Serial number to get(UUID). Make sure the "Do Not replate exisitng value" check box is checked.

           

          You should check your scripts if you use script steps like Duplicate record, you will have to manually set the new record's primary key with set field[get(uuid)].  When you have the Serial Number setting,  the new record will automaticly get a new ID. With the "Do Not replace..." checked,  the new record will have the same UUID as the original.  If you are duplicating records using an export/import script step,  You may have to do the same thing.  In this case you may be able to get away with exporting the record with out the Primary key field and then import.  Since the primary key field is empty it should populate with a new/ unique UUID.

           

          If you need any help with this feel free to contact me. 

          Bruce

          • 2. Re: Change an ID field to UUID without disconnecting relationships...
            BruceRobertson

            Here is one technique to consider.

             

            Assumes relation is based on parent ID in parent table = field fkParentID in child table.

             

            Go to record[ first]

             

            Loop

            Set variable [ $newID; get( uuid)]

            Loop

            Exit loop if[ isEmpty( child::fkParentID)]

            Set field [ child::fkParentID; $newID]

            // now the child record is no longer related

            End loop

            // now set parent ID

            Set field [ parentID; $newID]

            // now this parent is related again, by the new ID

            Go to record [ next; exit after last]

            End Loop

            • 3. Re: Change an ID field to UUID without disconnecting relationships...
              DavidJondreau

              There shouldn't be a need for a user to do a find on a key field. For scripted finds, you may need to wrap the key with an equal sign and quotes, like  ="key"

              • 4. Re: Change an ID field to UUID without disconnecting relationships...
                brian.curran

                Thanks all, I really would like all ID's to be converted to UUID's somehow but for now, I've added "==" to the scripted find...