7 Replies Latest reply on Jan 12, 2016 1:43 PM by jdxxs

    Repeated instances of duplicated auto-enter serial values

    RyanM.

      Summary

      Repeated instances of duplicated auto-enter serial values

      Product

      FileMaker Server

      Version

      13.0.5.520

      Operating system version

      OSX 10.9.5

      Description of the issue

      Since we upgraded form FMS 11 to FMS 13 in January, we've seen a sharp increase of duplicated auto-enter serial values. Two concurrent users create a record in the same table simultaneously, and both records are assigned the same serial value. We don't want to set the field to require unique values because we don't want users to have to handle that error when it occurs, and we don't want to interrupt the complicated scripts surrounding creation of records in these tables. There is another serial field on this same table, also not requiring unique values, that never has this issue.

      Steps to reproduce the problem

      Two records are created simultaneously in a table with auto-enter serial values for a field.

      Expected result

      Two records created in this table concurrently will have different values for this field.

      Actual result

      Two records end up with the same value.

      Workaround

      One of the records must be created again, and one of the duplicates must be deleted.

        • 1. Re: Repeated instances of duplicated auto-enter serial values
          philmodjunk

          Have you recently had to import the data into this table from another copy of the file or other external source. Have you checked to be sure that the next serial value is correctly set on this field?

          • 2. Re: Repeated instances of duplicated auto-enter serial values
            RyanM.

            We hadn't done any recent imports on that table. The next serial value appears to be fine. We haven't had any instances of this since the end of April, but nothing has been updated, so it is likely just luck.

            • 3. Re: Repeated instances of duplicated auto-enter serial values
              RyanM.

              We've had a few more occurrences of this bug this week. The next serial value is fine, and most records that are being created (~150/day) in that table are fine. What could be causing this?

              Thank you!

              • 4. Re: Repeated instances of duplicated auto-enter serial values
                TSGal

                Ryan M.:

                Is this the same file that you originally reported the issue?  If so, the file may be damaged.  Either use an older copy of the file and import the most recent data, or if you have made frequent changes to the database file, run a Recover on the file and see if any errors are reported.

                TSGal
                FileMaker, Inc.

                • 5. Re: Repeated instances of duplicated auto-enter serial values
                  RyanM.

                  It is the same file. In June we made an empty clone of the file and imported all of the data. We had occurrences of this before the cloning and a few after. I ran a recovery on the file and, while it did say there were some problems found, it didn't specify what those problems were. All it did was modify a few calculation fields.

                  • 6. Re: Repeated instances of duplicated auto-enter serial values
                    TSGal

                    Ryan M.:

                    Thank you for the information.

                    A clone makes a copy of the file with no records.  If the damage is in the structure of the file and not the data, then cloning will not fix the file.  Recover does create a recover.log file that logs all actions performed during the Recover operation.  Look for error codes greater than 0 (zero).

                    TSGal
                    FileMaker, Inc.

                    • 7. Re: Repeated instances of duplicated auto-enter serial values
                      jdxxs

                      I have a possibly similar issue but with only one user and new, empty tables. The behavior is consistent and predictable.

                       

                      I have a Payments table that is related to a Supplier table - but not to Transactions. I have a PaymentDetails table that is related to the Payments table and contains one record for each transaction that is to be paid. The layout is based on Suppliers. Related Transactions, Payments and PaymentDetails are listed in portals.

                       

                      When a Transaction is selected for payment, a script looks through Payment records to find one that has not been processed (indicated by having a blank check number field). If it finds one, the record is updated with the current date and the record ID is saved in a variable and used to creates a new PaymentDetails record with details of the transaction. The Payment record automatically updates the amount to be paid via a sum(PaymentDetails::amount) calculation field.

                       

                      If an existing payment record is not found, one is created with the Supplier ID being added as well as the current date and the PaymentDetail record added as above.

                       

                      The PaymentsID field is set to auto-enter starting at 1.

                       

                      When first tested, no errors were reported but two records were added to the Payments table. Both had an ID of 1. The first to be created had the Supplier ID and the current date as expected. The second had no Supplier iD and no date but the same ID. The PaymentDetail record was created correctly and was not duplicated.

                       

                      When a second transaction was chosen for payment, the script found the existing open Payment record and updated it with a new date and correctly created a PaymentDetail record. But a third payment - containing no date except the same ID of 1 - was created.

                       

                      Here is the (simplified) script steps after collecting data from the Transactions record and moving to the Payments portal related to Suppliers (and not to Transactions):

                       

                      Go to Portal Row [ Select ; First ]

                      Loop

                        Exit Loop If [ Payments::checkRef = ""

                        Go to Portal Row [ Select ; Next ; Exit after last ]

                      End Loop

                      If [ Payments::id = "" ]

                        Set field [ Payments::supplierID ; suppliers::id ]

                      End If

                      Set field [ Payments::date ; Get(CurrentDate) ]

                      Set variable [ $id ; Value ; Payments::id ]

                       

                      the script then creates the PaymentDetail record.

                       

                      I have also tried searching for an unprocessed Payment record using Find but the result is the same. I have tried Recovering the database but no errors were found and the behavior occurred using the recovered database.

                       

                      I don't have enough hair left to be pulling more out. But I am baffled. Any thoughts, anyone?

                       

                      Jeff