1 of 1 people found this helpful
Use field validation? Not empty and force unique?
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.
1 of 1 people found this helpful
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 ???
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
Commit Records / Requests (No Dialog ; Force Commit )
If (Get ( LastError ) ≠ 0
Delete Record / Request
Pause / Resume Script (Duration (seconds) : 5 )
Exit Loop If (1)
Is this the right procedure? Will it delete the newly created record if validation fails and create a new record?
I think it is right.
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.
If the calc of the field "number" happens sequentialy on the server after a commit of the record :-)
I used your calculation.
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?
If [ Get ( lastError ) ]
Pause [ 1 ]
Set Field [ table::serialNumber ; your.calc ]
That way you're not mucking about with multiple new records and deletes, just updating the field.
Thanks David, this looks like a solution I would understand 5 years from now. It feels simple and robust.