8 Replies Latest reply on Oct 21, 2014 5:11 AM by disabled_xaviervb

    Duplicate custom serial number

      Dear All,

       

      I have a field that creates a specific serial number. It's composed by the date and a number :

      2014.10.00001

      2014.10.00002

      2014.10.00003

      ...

       

      The field is a text field with as options auto-enter, calculated value :

      ------------------------------

      Year & "." &

      Right ( "0" & Month ; 2 )

      & "." & If ( Count ( DOS_DOS::__id ) > 0 ;

      Right ( "00000" & GetAsNumber ( Right ( DOS_DOS::number ; 5 ) ) + 1; 5 ) ; "00001")

      -------------------------------

       

      The relationship DOS_DOS looks if it finds records with the same Month and Year and sorts them by number.

       

      This worked fine for months now but suddenly today my customer had a duplicate... The same number.

       

      The 2 records have been created with 8 seconds inbetween.

       

      Any solutions how to avoid duplicates here?

       

      Thanks

        • 1. Re: Duplicate custom serial number
          DamianKelly

          Use field validation? Not empty and force unique?

          1 of 1 people found this helpful
          • 2. Re: Duplicate custom serial number
            DavidJondreau

            Duplicates can happen this way if two users are creating records at the same time. The counter won't increment until the record is committed. So, one user (well, actually I think this could be the same user if creating related records!) creates a record, but doesn't commit it for 8 seconds because they're editing it. Another user creates a record in that time, and the calc will return a duplicate.

             

            If you're creating related records, it gets even trickier, since one user can create multiple new child records before a commit. I suppose one user could create multiple parents if using multiple windows too.

             

            You can force a commit on creation (by using a custom menu for the New Record command). You could also use FileMaker's auto-enter serial number to increment. You'll need to reset that number every month with a scripted process. Ideally a server-side script, though possibly an "on open" script.  Or a script tied to the new record command that checks to see if the number has been reset this month in a another table.

             

            As Damian said, you could also use field validation, along with a New Record scripted process. You'd capture the validation error and increment until you found a unique number.

             

            I wish I could say there was a simpler solution.

            • 3. Re: Duplicate custom serial number
              user19752

              If the number isn't used as portal key, calculate on commit may help.

               

              make a autonumber field as 'on Commit'

              change calc of number as

               

              Case ( not IsEmpty ( autonumber ) ;

                  Case ( IsEmpty ( DOS_DOS::number ) ; year & "." & Right ( "0" & Month ; 2 ) & ".00001" ;

                      SerialIncrement ( DOS_DOS::number ; 1 )

                  )

              )

               

              This may be used for decreasing chance of duplicate if you commit in scripted record creation.

               

              If used as portal key ...

              first set the number to Get(UUID) on record creation and re-culc it on commit ???

              1 of 1 people found this helpful
              • 4. Re: Duplicate custom serial number

                So I decided the do the following :

                - force a commit on creation (by using a custom menu for the New Record command)

                - use field validation (unique)

                 

                Now comes the tricky part as I can't test my script ...

                 

                Set Error Capture On

                Loop

                     New Record/Request

                     Commit Records / Requests (No Dialog ; Force Commit )

                     If (Get ( LastError ) ≠ 0

                          Delete Record / Request

                          Pause / Resume Script (Duration (seconds) : 5 )

                     Else

                          Exit Loop If (1)

                     End If

                End Loop

                 

                Is this the right procedure? Will it delete the newly created record if validation fails and create a new record?

                • 5. Re: Duplicate custom serial number
                  user19752

                  I think it is right.

                   

                  Thinking around

                  1) if 'New Record/Request' results some error, 'Delete Record/Request' will perform on unwanted record.

                  2) if another user create record between 'New Record' and 'Commit Records', duplicate will happen.

                  I think my calculation wrote in previous post works for this. (Creation can be occur on every client simultaniously, but Commit is occur on server sequential. Is this true ?)

                  3) Do you use ESS ? 'Force Commit' has no effect if not.

                  4) avoid 'If' changing

                  Exit Loop If [Get (LastError)=0]

                  //do error trap

                  This writing affect only error/or not case. Need 'If' for branching by error codes.

                  • 6. Re: Duplicate custom serial number

                    Thanks !

                     

                    If the calc of the field "number" happens sequentialy on the server after a commit of the record :-)

                     

                    I used your calculation.

                     

                    Xavier

                    • 7. Re: Duplicate custom serial number
                      DavidJondreau

                      New Record and Commit should work 99.9% of the time. It'd be tough for two users to make the same record at the exact same time. But if you want to be safe...Instead of Delete[] how about just setting the key field?

                       

                       

                      New Record/Request

                      Loop

                      Exit Loop If [ $pass ]

                      Commit[]

                      If [ Get ( lastError ) ]

                      Pause [ 1 ]

                      Set Field [ table::serialNumber ; your.calc ]

                      Else

                      Set Variable[ $pass ; 1 ]

                      End If

                      End Loop

                       

                      That way you're not mucking about with multiple new records and deletes, just updating the field.

                      • 8. Re: Duplicate custom serial number

                        Thanks David, this looks like a solution I would understand 5 years from now. It feels simple and robust.