4 Replies Latest reply on Feb 26, 2016 1:21 PM by mardikennedy

    Entries out of sync...Filemaker Pro 13


      I have FileMaker Pro 13 and I created a database of Mineral Specimens and presented a form in the program not long after I bought the program a year or two ago. I filled in each entry in the form I created and eventually this reached 2387 records [02387 on my specimen number field on each form]. I should add I am no expert but I was very happy with the program and was using it within my knowledge and skills and found it excellent.


      I can remember creating each entry…..as I added a new entry by clicking on the “New” field the record number on FileMaker Pro 13 always created the file that was the same number as the record. I had not looked at the program for a while and then recently I decided to add more records but was surprised that when I went to create the next record after 02387 by clicking on “New” it jumped to 02389 and seemed to omit 02388 for some reason. Unfortunately in my attempts to sort it I have made things worse by deleting some blank records between 02389 and now 02395?


      This never happened before and each time I clicked “New” the program created the next number that complied with the number on the form. I am now in a situation that I have 2388 records but this specimen form is numbered 2395?


      What have I done wrong and how can I correct it? I can keep changing the number when I create a record but this will be OK till I “forget” to do it and then everything will be out of sync.


      I look forward to any help with this problem that I presume I have created for myself.


      David Anderson

        • 1. Re: Entries out of sync...Filemaker Pro 13

          It appears you created a auto-enter serial field for numbering your records.  When you delete a record FM does not reset the serial number, FM just deletes that record.  It does not appear that there is a problem.     You can use get(RecordNumber) to get the record number and the record number would change when a record is deleted.

          • 2. Re: Entries out of sync...Filemaker Pro 13

            The record number will also change if you sort the records...be aware of this.  Record Number is the number of record in the current found set, sorted by however you have it sorted at the time...


            Depending on how you are using the number (hopefully not as a linking field to another table!)  You can also reset the autoenter counter to the number you want to be on.


            This may make sequential entry "sync"...but opens the risk of duplicating a number...

            • 3. Re: Entries out of sync...Filemaker Pro 13

              Of course this field should not be used in a relationship. 


              Resetting the counter would not help because you would still have holes in your count and possible have duplicate numbers.  If you had 100 record and deleted records 6-10 (5 records)  then reset your counter by subtracting 5 (back to 96)  you would end up with 2 records numbered 96,97,98,99,100 when new records are added, with records  6-10 still missing.  If the user delete more records before adding 5 new records then you could end up with 3 or 4 duplicate numbers.


              I gave one option that might work for the OP. Enough details were not giving to state if would it work for them are not.  

              • 4. Re: Entries out of sync...Filemaker Pro 13

                Two options:

                1. Does it really matter to have 'number jumps'?  For many of us, the neat freak streak wants them, because it looks 'tidier', but actually they are just an abstraction anyway so does it really matter?  (It's the actual description that matters.)

                2. It's possible to script serial number updates but is this a level of intricacy that you'd like to go to?  (You'll probably get lots of alternative strategies on how to accomplish this; FMP can do it in multiple ways.)