serial number duplication
I created a DB that our company uses to generate quotes. There are 8-10 users that generate quotes and it is hosted on the server. I have a Quotenumber field defined as a text calculation that is based on date and current record number so that it yields something like 1312011-162 (if the date is 1/31/2011 and that quote is the 162 of 2011). The validation for the field is set to be a unique value.
The problem is that so far this year we have had half a dozen duplicate quote numbers. Since the calculation is in the field definition I assumed as soon as a record was created by a user that the number would be taken and the next record created by any user would have to be the next incremental value. But this is apparently not true. All I can figure is that Filemaker is not counting the record in the current record number until some point and that another record is being created before that point is reached.
I have found that the unique value validation is overridden by the calculation in the field definition, but no solutions to the problem. Is there any simple way to have the new record set immediately so that a second new record created sees it and counts it in the current record count? Or would it be easier to write a final validation script that double checks for the duplicate value and increments up 1 if it is?