2 Replies Latest reply on Jul 29, 2014 3:47 PM by calebmiranda

    How to create a specializing formatted ID key



      How to create a specializing formatted ID key


           Is there a way to create an ID key that auto-generates and has the following format:

           patient001 (where there are leading zeros), and end at patient999?



        • 1. Re: How to create a specializing formatted ID key

               What happens when 'patient999' is used?  

               You can set up AUTO-ENTER to enter the "patient001" incrementing by 1

               I was able to VALIDATION test for a length ( ID ) = 10.  


               The big question is what happens after the 999 is reached.  There are other ways to achieve this.  The addition of a new record would definitely need to be scripted with the proper test for the conditions.

          • 2. Re: How to create a specializing formatted ID key

                 Allow me to think with you:

            if you have a table that just have patients:

                 1) First sugestion: set the name of the ID field as PatientID_pk and the ID as just the number (without the leading word "patient")

                 2) if you have some good reason for not doing that (for, in theory, it would be the best practice), you could have the a schema that is something like this:

                 - a field in this table called g0 (as in global zero), with global value of 0

                 - another table occurrence called max Patients, that is related to the main T.O. by the g0 field

                 - a field for the patientNumber, which will contain the number of the patient

                 - the primary key field as a Calcultated value that has the following calculation (Evaluated from the main table's context): "patient" & Right ( "000"&Max ( max Patients::patientNumber ) + 1;3)

                 - the patientNumber field would have the following Calculated value:  Max ( max Patients::patientNumber ) + 1


            if you have a table that have more than just patients (like doctors or employees, whatever):

                 it seems as a better practice to have separated tables for each kind of person. Alternatively, you could:

                 - have one field to define the PersonID and another field to define the TypeOfPerson

                 - make several TOs like explained above, with different relationships based on TypeOfPerson, which would only consider the number for that kind of person (but this is shit)


                 My humble suggestion is that you make a numeric fixed ID for all the records in your table, the easiest way possible, which will be the most reliable and easy to mantain. Like PersonID as a Serial number, generate on creation. If you need this kind of id (as "patient00X"), you could use a field just for the calculated value