4 Replies Latest reply on Apr 21, 2014 11:03 AM by philmodjunk

    using an existing unique number as primary key.



      using an existing unique number as primary key.


           I am a doctor and have a list of patients with pre existing patient numbers which are important for anonymisation. I would like to use this number as the primary key to link demographic data on several tables. Searching it seems that FMP usualy likes to generate its own serial number as the primary key. Is there a way I can use the pre existing unique patient number ?.

        • 1. Re: using an existing unique number as primary key.

               If it is a unique number and set as a unique number in the field settings, yes you can use it as a primary key to relate records in another table.

               If you do not have a primary key set up currently for that table you can also create one separate for just the key and use the replace field contents while showing all records and replace using the serial numbers to set values to all of your records then use this for the key instead of the patient number. This would be an auto increment number and might be easier to keep your database working in case the patient number is not an auto increment.

          • 2. Re: using an existing unique number as primary key.

                 It's really not a good idea to use an ID generated from outside of a database--any database, not just FileMaker. Primary keys form the crucial "back bone" of your relationships and you really, really do not want to introduce possible issues where might be the slightest chance that you'll have to change the value of a primary key after it as been created and used to link a record to foreign keys in other tables.

                 By all means, put such a field in your database and enter or import the number. You can use it for searching, sorting, value lists, etc, but just don't use it as your primary key.

                 When you use a value generated externally as your primary key: (VINs, social security numbers, UPC codes, Patient IDs from another system, etc), the following "worst case scenarios" could create issues for your database:

                 The value could be input or imported incorrectly. This is a risk especially if a data entry person has to hand enter or select the value, but even scanning a bar code can input the wrong value and if imported from another database, an issue in the other database might generate an incorrect value.

                 The originating entity, could, at some point in the future choose to change this value.

                 The chances of any of these particular issues might be nearly zero, but why take any chances at all with one of the most crucial parts of your database design?

                 PS. sometimes we DO use such values in match fields in relationships, but the important detail is that such uses are for specialized "search" features that must use the field as a match field in the relationship--not as the Primary Key "back bone" of your data model. The key way to tell the difference is to ask yourself what you would have to do if you ever needed to change the value. For a "search tool" type use, changing the value should require no further action other than changing the value in one table. For a Primary Key, you'll have to change values in multiple records of one or more related tables in order to keep records correctly linked.

            • 3. Re: using an existing unique number as primary key.

                   Good stuff , many thanks guys. You have convinced me it is a bad idea.

                   I have created a new field in each table and the relationship between them. How do I get FMP to auto populate the field in the 32 records I currently have ?. Until I do that the relationship does't work.

              • 4. Re: using an existing unique number as primary key.

                     You can use Replace Field Contents with the serial number option to both insert a serial number and update your field options so that the next new record will auto-enter the next serial number in the series.