7 Replies Latest reply on Jun 28, 2010 11:56 PM by cuitw_1

    Unique Record Number



      Unique Record Number


      Mac OS System

      FMP 8.5

      Level Beginner


      For each new entry we create is there a unique record number that is known to FMP?  Does FMP keep track of Creation and Modification Dates?  I am very interested because in retrospect we realize this is all important information, but we did not make a conscious decision to include these fields, now thousands of records later, we would like to resurrect this information if possible.  Can this be done? How?

        • 2. Re: Unique Record Number

          As David suggested, you can use Get (RecordID) to identify the relative age of a record. Records created first have smaller record ID's than those created later provided they have not been imported from a different copy of filemaker. (After import, they reflect the order in which they were imported.)


          As far as I know, there is no way to get creation and modification date information on these records if you have not previously defined date fields to auto-enter this information.


          For more on Get ( RecordID): http://forum-en.filemaker.com/t5/Using-FileMaker-Pro/Why-Record-ID-s-should-not-be-used-as-Primary-Keys-in-Filemaker/td-p/69610

          • 3. Re: Unique Record Number

            Okay, I am reviewing all the Get(**) options and there is fantastic potential.  But I am still stuck on actually viewing the Get(RecordID) results.  I am creating a field for this and plugging in the actual words Get(RecordID) and hoping that this will present the unique ID of each record, but this is not happening...

            Can you help me get the Get(RecordID) into the record in a way that it will reveal the unique ID number please?

            • 4. Re: Unique Record Number

              You do not want to use Get ( RecordID) at all (did you read the link Phil referenced?).  The number isn't even sequential and will jump from 1 to 36 to 3244 etc  with no apparent logic.  It is a behind-the-scenes, internal tracker.  Drop RecordID from your radar.


              You want to use a standard number field and in field Options Auto-Enter, set it as serial number incrementing by 1.  If you have existing records (and after you set your ID field to auto-enter serial), back up your file for protection, show all records then place your cursor in the field and run Replace Field Contents and specify 'by serial'.  Note that there is a checkbox which when checked will reset your auto-enter serial in definitions to the next serial after you run your process (and you will want this checked).


              Now each new record will get the auto-enter incrementing serial.  This is your table's unique ID and every table should have one.   They are called primary keys and are usually CustomerID, InvoiceID etc (describing the table they are the primary for) or some use pk_customer_ID (indicating it is the primary key) or fk_customer_ID (indicating it is the customer's primary key used in an Invoice file, for example).


              UPDATE:  As an aside, Get(RecordID) probably wasn't working for you (and thank goodness it wasn't or you would have gone on your merry way with a very bad solution) but ... it should have been a calculation and it needed to be set in Storage Options (in calculation dialog box) to 'unstored'.  So for future with OTHER Get() functions, remember that most are dynamic and for their values to change, they must have UNCHECKED 'do not store calculation results' to update properly.



              • 5. Re: Unique Record Number



                With due respect for LaRetta (whose points are quite valid and should be listened to in your Dbase structure).  Your original post was asking about how close you could come to 'resurrecting' data that you wish you had captured in the first place.


                Step 1: Put in the autoenter serial and/or timestamp fields so you start capturing this info moving forward.

                Step 2: Use Get(RecordID) as a tool to determine the relative order in which those old records were created.  That's about as close as you're likely to get to when they were created...no dates, just "who came first, second, third etc. ..."


                When you get that info (if it's useful in any way to what you are trying to achieve), I would kill the field to avoid the temptation of using it in your structure moving forward.  "Best  way to block a punch is to not be there..."  It has limited (if any) use as an information tool, and is dangerously tempting in your structure.  An autoenter serial and/or timestamp is the far more reliable way to go.

                • 6. Re: Unique Record Number

                  Thanks, Ninja, I did not read the post in that light.  And now record IDs come in sequence instead of jumping dramatically between numbers. Regardless, real answer is simple "No, that information cannot be resurrected."


                  "That's about as close as you're likely to get to when they were created...no dates, just "who came first, second, third etc. ...""


                  Since the record ID might be days or weeks between one and another and since records can be deleted (record ID lost as well), then recordID means no more than simpler way of placing records in unsorted state which is their natural creation order in the table, at least in my opinion.

                  • 7. Re: Unique Record Number

                    We normally use the primary key to uniquely identify a record. The record ID, however, is good for using API for PHP as there is a getrecordbyid function that allow us to get a record without the trouble of performing a find.