4 Replies Latest reply on Aug 7, 2014 4:51 PM by taylorsharpe

    Serial numbers

    davialan

      In my Contacts table i have a ContactsID field specified as a serial field.

      If I add a record it increases the contactID by 1 as expected.

      If I now delete the record and add another record the contactID increases by 2

      Can this be avioded ?

      Please note I am a novice

        • 1. Re: Serial numbers
          erolst

          davialan wrote:

          If I now delete the record and add another record the contactID increases by 2

           

          And this is a problem because …?

          • 2. Re: Serial numbers

            Why should it be avoided?

             

            In fact, it does not increase by 2, it takes the next higher number, regardless whether you have deleted any records in between.

             

            It looks like you use the field “ContactsID” as a key field.

            If this is the case please read <fmdiff.com/fm/serialnumber.html>

             

            Winfried

            • 3. Re: Serial numbers
              keywords

              It is not actually increasing the serial by 2, it is simply setting the serial to the next one in its sequence but obviously skipping the one already allocated to the record you deleted. To reset the sequence so as to reallocate the one you deleted, you would have to go into the field definition for the serial number field, click on Options… at bottom right and reset the serial number. Depending on the actual circumstance you face and what your rewuirements are you could also reset the serial for the entire file using Records > Replace Field Contents; this has the option to Update the serial number Entry Option to make sure the next record created will continue the sequence. But of course you wouldn't use the replace option if the serial number is meant to be a unique identifier as that action would mess up relationships depending on that field. And this raises a question: if the purpose of the serial is to be a unique identifier then gaps created by deleted records don't matter, do they?

              • 4. Re: Serial numbers
                taylorsharpe

                As noted by others above, the serial number just increments each time.  Personally, I have stopped using serial numbers except for fields a customer wants to see, I instead use UUIDs.  I do this so that I can create remote records and sync later on and not worry about accidentally duplicating serial numbers. 

                 

                That aside, if your goal is to have the next higher number and not the next serial increment, you can make a calculation that increments the maximum of that field by 1.  I often do this with a SQL Select such as:

                 

                Let ( [

                 

                F1 = "SELECT

                MAX ( SerialFieldName )

                FROM

                CurrentTableName" ;

                F2 = F1 + 1

                 

                ] ;

                 

                F2

                 

                )

                 

                 

                This will always give you the next higher number.  The issue with doing this instead of serial increments is if two people are creating a record at the same time and haven't committed it.  That is the benefit of using serial increments to make sure that such a situation doesn't happen.