14 Replies Latest reply on Jan 31, 2011 12:48 PM by mgores

    serial number duplication

    mgores

      Title

      serial number duplication

      Post

      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?

        • 1. Re: serial number duplication
          philmodjunk

          Since validation takes place before the record is comitted, but the value being checked for uniqueness is a committed value in another record, two or more users can generate the same value if they committ their changes within fractions of a second of one another.

          The simplest, safest solution, is to not use a numbering scheme like this that requires an auto-entered calculation with a uniqueness validation. Simply use an auto-entered serial number for your Quotenumber instead.

          If you want to continue using your current Quote Number format, you'll need to carefully craft a script that commits the record, then uses a self-join relationship to check uniqueness. You'll have to be careful how you set this up to solve the issue of near simultaneous commits and also to avoid an endless looping script that can occur if you use OnRecordCommit and then commit the record--triggering yet another call to the same script...

          • 2. Re: serial number duplication
            mgores

            How/when is the record committed?  Is there a way to script it in.

            Currently when the user click on the "create a new quote" it actually pulls up the contact DB in the find mode to find or create the contact to send a quote to.  Once the contact is found a button is clicked to generate a quote for that contact.  Which goes back to the Quote DB and a new record is created in the Quote DB, the contact's email address is set from the contact DB, all other contact info fieds are entered via lookup based on the email address.  The user is then prompted to enter the rest of the info for the quote, then eventually sends the quote out as a PDF attachment to an email.

            I was thinking that if I could commit the record in the script that creates the new record it would then take that number, and the next new record would be able to count it even if it was a fraction of a second later.

            • 3. Re: serial number duplication
              philmodjunk

              A variety of events can commit the record. You can script it explicitly with the commit record step. A script or user action that goes to a different record or layout will automatically commit the record. So will simply clicking the layout background.

              Thus, in your script, you can include commit record immediately after creating it, then check it for uniqueness to make sure another user didn't do the same thing in the same "blink of an eye" on another work station. Come to think of it, there's a chance that this script will detect the duplicate value and then your script might result in both getting new values--with a resultant skipped serial value.

              Here's another way that both generates the serial number "on command" and avoids the duplicate creation issue:

              Define an auto-entered serial number field, QuoteID, in your quotes table. (Using this auto-entered serial number for your database's relationships to other tables would be a very good idea).

              Define a new table: QuoteIDLabels with two fields:
              QuoteID (number)
              QuoteSerialLabel ( Auto-entered serial number )

              Define this relationship and enable "allow creation of records via this relationship" for QuoteIDLabels:

              QuotesTable::QuoteID = QuoteIDLabels::QuoteID

              Now this script can generate the new QuoteIDLabel and it will be unique even if all your users try to generate a new Quote at the same exact time:

              Set Field [QuoteIDLabels::QuoteID ; QuotesTable::QuoteID]
              Set Field [QuotesTable::QuoteIDLabel ; Substitute ( Get ( CurrentDate ) ; "/" ; "" ) & "-" & QuoteIDLabels::QuoteSerialLabel]

              The first step generates a new record with serial number in QuoteIDLabels if such a record does not already exist and the second combines it with the current date and stores it in a field in your Quotes table.

              PS. you would need to reset the next serial value for QuoteSerialLable on the first of each new year--something that can be scripted to occur automatically.

              • 4. Re: serial number duplication
                mgores

                Thanks Phil.  I didn't realize there was a commit record script step.  I've been just winging it with Filemaker for years now and never stumbled across that one. I think that writing that one in right after the script creates the new record should take care of it, but will keep an eye out for the skipped number issue.

                My main issue with the duplicate numbers is that each department has a tracking DB for jobs that come in.  As a job is entered a portal shows quotes related to that customer where the person can click on the applicable quote to place the quote number from the portal into the quote number field of the job record for the tracking DB.  When there are dupilcate quote numbers the wrong quote can get associated with the job.

                • 5. Re: serial number duplication
                  philmodjunk

                  If I were doing that, I'd base the relationship on a simple auto-entered serial number, not this calculated value that combines year with a serial number. I'd put something like that only if I couldn't convince my client/employer not to and then I'd use it as a label field--not a key field for relationships--relying on the auto-entered serial number for such relationships instead. (This serial number need not ever be even visible on any reports/layouts that the user sees.)

                  • 6. Re: serial number duplication
                    mgores

                    wouldn't the simple auto-entered serial number be just as susceptible to duplication as the calculated field.  Or is serial number different than a Get(currentrecordnumber) in that it will be "taken" before a record is commited?

                    • 7. Re: serial number duplication
                      philmodjunk

                      Don't use get ( recordnumber ) under any circumstances. Record number is just the current records location in the current found set. It changes with every find and sort you do.

                      auto-entered serial numbers have been implemented in such a way that this approach will not allow duplicate values whether or not you are in a single user or multi user environment. The only way you can get duplicate serial numbers with this auto-enter setting is if you make changes to its next serial value setting or you import records with serial numbers from another source (such as during an upgrade or restoring from a back up copy). That makes using serial number fields as your primary key the simplest, safest option.

                      • 8. Re: serial number duplication
                        mgores

                        Ah, I see now that there is the option in selecting serial # to have it on record creation or on record commit.  Therefor having it set on record creation will have it taken and force it to be unique even if some else creates one a fraction of a second later.

                        So given that we are already over a hundred records into the yead, if I set the start to the next number and have it increment by one, I should be able to change my Quote_Number field to the get date(currentdate) & "-" & Quote_serialnumber

                        and have it continue on with the numbers already in place with the old numbering calculation.

                        Correct?

                        • 9. Re: serial number duplication
                          philmodjunk

                          Ah, I see now that there is the option in selecting serial # to have it on record creation or on record commit.  Therefor having it set on record creation will have it taken and force it to be unique even if some else creates one a fraction of a second later.

                          It also works correctly if you use on Commit option. (The serial number assignement action is handled by the host computer to prevent simultaneous commits from getting the same serial number.)

                          Other than that, I think you've got it.

                          • 10. Re: serial number duplication
                            mgores

                            hmmm Just had a thought.  If I change the Quote_number field definition calculation from

                            Month(Get(CurrentDate)) & Day(Get(CurrentDate)) & Year(Get(CurrentDate)) & "-" & Get(RecordNumber)

                            to

                            Month(Get(CurrentDate)) & Day(Get(CurrentDate)) & Year(Get(CurrentDate)) & "-" & Quotes:quoteID

                            Will that affect the records that are already in there? or am I safe if I check the "do not replace existing values (if any)" box

                            • 11. Re: serial number duplication
                              philmodjunk

                              Your existing records may not have a value in QuoteID or it may be a different value than the record number that was current at the time your record was created. You'll have to check and see if the values match. I'd create a new field definition for this so that I can place the fields side by side and check the values.

                              • 12. Re: serial number duplication
                                mgores

                                Yes, since the QuoteID is a newly added field, all of the current records do not have a value in there.  I am guessing I will have to go back and enter those manually if I want that data in those.  I was more worried about changing the field definition for the Quote_number field from the old calculation to the new  changing the values that are in the records that already exist. 

                                Since those quotes are already out there and either already have jobs related to them or may yet have jobs associated with them, the current numbers must stay what they are.  Any new records will be calculated usinig the newly created QuoteID based on the serial #.

                                • 13. Re: serial number duplication
                                  philmodjunk

                                  You can use Replace Field Contents to copy data from the original field into the new serial number field so that you do not have to do this all by hand one record at a time. (When using Replace Field contents, it's good practice to make a copy of your field first, and then do this when no other users are accessing the file. The copy let's you start over if you don't get the results you expected. Doing this when there are no other users (or on an isolated copy) keeps them from accidentally locking Replace Fields out of a record by putting the cursor into a field of one of these records.

                                  • 14. Re: serial number duplication
                                    mgores

                                    I did that and got lucky, well I waited for everyone to go to lunch.  So they all look good now, all have a QuoteID and the old Quote_numbers are intact.  The new Quote_numbers will be generated from the "date + QuoteID" calculation which should always be a unique number regardless of two users pushing the button at the same time or not.

                                    Thanks again Phil.

                                    Now to start another thread with my next issue  Smile