1 Reply Latest reply on May 19, 2014 4:42 PM by philmodjunk

    Reseting key fields



      Reseting key fields


           Hi everybody, 

           I have a solution with 12 or more tables, the thing is that I was tought to set my key fields with three letters at the begining like "MCD0000001...MCD0000002... and so on... But I was wondering if there is a way to rename all key fields with just numbers and also for the related tables..... I know it sounds like a lot of work and risky but I realized that the letters in the kyfield are going to be a problem later with a script I just wrote to import data and to reset the next key field values...

           Thanks in advance



        • 1. Re: Reseting key fields

               The ideal primary key is:

          1.           Unique
          3.           Never ever changed once assigned to a new record
          5.           Devoid of any "encoded meaning"
          7.           Uses the most simple and bullet proof method for assigning values possible.

               Your current primary key violates rules 3 and 4 so I agree that making this change would be an improvement. 95% of the time, primary keys should be simple auto-entered serial numbers in FileMaker Pro systems. almost all of the other times, Get ( UUID ) should be auto-entered.

               But this IS risky. I strongly recommend that you freeze data entry on your database, make a back up copy and then update a copy with the needed changes. That way, if your changes don't work as expected, you can revert to the back up copy and be none the worse for wear.

               Here's a simple manual method for updating this field in a single table. You will just need to repeat this process for every table where this format of a primary key or linked foreign key field is used:

               Go to a layout based on that table.

               Show All Records

               Put the cursor in the field and select Replace Field Contents

               Select the calculation option for this tool and use this type of expression:

               GetAsNumber ( KeyFieldReferenceGoesHere )

               This can also be scripted in order to make the changeover more quickly and with less chance of user error so that one script updates all the tables.