5 Replies Latest reply on Dec 7, 2011 9:43 AM by MichaelPapenburg

    Record Number Renumbering Problem



      Record Number Renumbering Problem


      My company is currently running FileMaker Pro 8.0v2. We have a docket database that uses unique consecutive record numbers. Every several months, we will open up the database to find that the records have been renumbered. 

      For example, lets say that you have existing records that range from 1 to 3000. What happens is that records 1 to 2000 will be renumbered as 3001 to 5000, but records 2001 to 3000 will be unaltered, and the system does not acknowledge that numbers 3001 to 5000 have been used. In other words, manually creating a new record would generate #3001 which effectively creates multiple records with the same number (which is a major problem).

      I have been with the company for a year now and have seen this happen 3 or 4 times. I am convinced that this is a random problem with our database rather than operator error but I am very open to suggestions. The only random connection that I can see with the problem is that it almost always happens when a new user is set up on our FileMaker server. 

      Could the program itself be corrupted? Any suggestions would be greatly appreciated. I'll gladly answer any questions if my post is not clear. 

        • 1. Re: Record Number Renumbering Problem

          A problem with a corrupted file usually isn't this "graceful". It's more likely to crash your file than to mysteriously renumber it, though a damaged index isn't something we can rule out at this point.

          You'll need to look at the design of your database first. What kind of field is this? Is it an auto-entered serial number field (the simplest, safest option for uniquely numbering records)?

          Are these records a simple arbitrary sequence with the oldest records having the smallest values and the most recently created record the largest value or is there some additional "meaning" attached to these numbers. (Such as starting over with 1 on january first, or numbers in a given range represent a given category.)

          Best guess is, that if large numbers of records are being mysteriosly renumbered and the field in question is a simple number or text field, either a script that is modifying large numbers of records is be running amok or an import records operation is copying the data into a new copy of the file and the renumbering is happening during the import operation.

          • 2. Re: Record Number Renumbering Problem

            Thank you for your response. I just checked the field and found that it is an auto entered number field. There is nothing fancy about the number system - the oldest records have the smallest number, etc. We never import records into the database and there are no scripts that fit your description. 

            I am seriously stumped with the problem. The database has existed for many years before my time here so I'm not 100% sure that there aren't any unusual scripts but I'm not sure where to begin checking for that sort of problem. Any suggestions would be greatly appreciated. 

            • 3. Re: Record Number Renumbering Problem

              I suspect that either there is indeed such a script or that there is something to the layout where you see this that is creating the illusion that the Serial numbers are changing.

              Open Manage | database | Fields, double click this field, click the validation tab and specify: Unique values, validate always. Also click the auto-enter tab and select "prohibit modification during data entry".

              This at least, should prevent the field from being changed by a script or a user. If you start seeing error messages pop up that a field cannot be modified after you do this or a user complains that they can't do what they want to do, you'll get a clue as to what is changing the numbers.

              • 4. Re: Record Number Renumbering Problem

                BTW, the most typical case of a script "running amok" is a script that uses Go To Related Record, but doesn't correctly check to see if there are any related records to "go to". In those cases, the script can end up running the rest of its steps on the wrong layout/table with potentially disasterous results.

                • 5. Re: Record Number Renumbering Problem

                  Thank you for your suggestions. It turned out that the "validate always" option was not selected so hopefully this will help to prevent the problem in the future. I also double checked that the "prohibit modification" option was selected. 

                  Thanks again.