1 2 Previous Next 24 Replies Latest reply on Jul 14, 2012 9:44 PM by JimMac

    Serial Number field not sequencing correctly?

    Jenny_1

      Title

      Serial Number field not sequencing correctly?

      Post

      Why is the Serial Number field not sequencing correctly - using FP11 on Mac?

      I have created 3 student/contacts databases & added Student ID number to them all, the serial number appears correctly in each new record as it is added but the number sequencing jumps around  eg 1001, 1002, 1003 then straight to 1010, 1011 or straight to 1077.

      I have created the field with Auto-Enter Serial - via Serial Number, On creation with an increment of 1.  Can you tell me what I have missed?

      Thanks for all your assistance, much appreciated.

      Jenny

        • 1. Re: Serial Number field not sequencing correctly?
          hbrendel

          Maybe records were created en deleted again?

          • 2. Re: Serial Number field not sequencing correctly?
            Sorbsbuster

            3 databases, or 3 tables in one database?  (Why would you be creating Student Records 3 times, independently, in one database?  If it is 3 copies of the file, are all 3 files doing exactly the same thing?  Are they copies?)

            • 3. Re: Serial Number field not sequencing correctly?
              Jenny_1

              Sorry for not being clear, there are x3 different stand alone databases. 

              Two came from the Students Records template & one from Contact Management.  I did copy the Student Records template to make the second database.

              They are all making the same sequencing error. 

              I had thought it may have been because as I was creating each database, I was making new records to test the different reports I formatted however I did delete all records, closed the database & started again with new records & I still have the problem.

              Thanks for such a quick response, many thanks.

              Jenny

              • 4. Re: Serial Number field not sequencing correctly?
                Sorbsbuster

                hbrendel has it - if you create a record and delete it the ID will not be 're-used'.

                • 5. Re: Serial Number field not sequencing correctly?
                  Jenny_1

                  Thanks Sorbsbuster, but how would it get up to 77 records when the most I would have created would be up to 20 if that?

                  Also, as you say the ID will not be re-used, does that apply if you go back into the field options & re-set the begining number?

                  Thanks again

                  Jenny

                  • 6. Re: Serial Number field not sequencing correctly?
                    symbister

                    If you're OK with changing the existing serial numbers for your records, you'll need to 'reset' the serial number - sort the records into your preferred 'serial' order, select the Serial Number Field, go to Records>Replace Field Contents (use with care - it's not undoable - suggest you backup the file first) and select the Serial Number update option, and Update Serial Number in Entry options.

                    Of course, as Sorsbuster says, if you subsequently delete a record, you've 'lost' that serial number.

                     

                    • 7. Re: Serial Number field not sequencing correctly?
                      Sorbsbuster

                      I think you should describe to us what you've done in a wee bit more detail.  'Losing' serial numbers is not the issue that some people think it is.  RE-USING serial numbers wrongly is a Very Big Issue.  If you have any linked records they will use that key, and then the old records will be wrongly re-attributed to a different student altogether.

                      • 8. Re: Serial Number field not sequencing correctly?
                        Jenny_1

                        Hi again

                        I have really done something with the serial numbers & I can not work out what it is???

                        The Serial Number problem seems to be in the sequencing as described above & duplicating.

                        In the Student Records database -  In table view, I deleted records not wanted via 'Records' - I omitted certain records & then deleted those found records. I then went back to form view.  When I create a new record would you expect the record numbers to stay in sequence - i.e. If I have 3 records created as follows, Record #1 with serial number 1000, Record #2 with serial number 1001 & Record #3 with serial number 1002; the next new record would expect to be Record #4 with 1003.  However what happens is that all records are pushed back and Record #1 now has a new serial number 1003 & may even jump to Record #1 with 1006?

                        In the Contact Management database - In table view, I deleted records not wanted via 'Records' - I omitted certain records & then deleted those found records. I then went back to form view & added further infomation to the only x3 existing records I now had (I did search for any further records), as I added the new  information the system created new records (in the correct serial number sequence?) but it was a duplicate of the record I was just working on????

                        I now assume that when I last contacted you I had been deleting unwanted "test" recods incorrectly, is deleting via Records as I have above correct? I think I now have a Very Big Issue as Sorsbuster says.

                        Would it be easier if I just sent you a copy of the databases conerned?

                        I hope you can help, if this is still to vague please let me know.

                        Jenny

                        • 9. Re: Serial Number field not sequencing correctly?
                          Sorbsbuster

                          "Would it be easier if I just sent you a copy of the databases concerned?" - Probably.  Send it to a filesharing site, or Dropbox, and post the link.

                          " the next new record would expect to be Record #4 with 1003" - no, I would expect the next serial number to be the next after the highest one you've ever had in the database.  So if you had 20 records before, ending with serial number 1019, I would expect the next new record to be 1020.

                          Can I assume you used the Auto-enter serial number option in the field's definition?  It is possible to get serial number 1003 if you set up the auto-enter to be by calculation, but you wouldn't do that by accident.

                          "all records are pushed back and Record #1 now has a new serial number 1003 & may even jump to Record #1 with 1006?" - that is really strange behaviour.  You are saying that records with existing serial numbers have their serial numbers replaced, simply by adding another record?  Check if the list is sorted before you create the new record - from memory FM will maintain the sort order, so if you create a new record which would normally be at the top of the list by that sort order, it will pop it to the top of the list and appear to 'push' the others down.

                          "I now assume that when I last contacted you I had been deleting unwanted "test" records incorrectly," - all is not lost.  We need to see the file(s) and determine why you are seeing what you report.  (Before we tell you that, in fact, all is lost. Wink)

                          • 10. Re: Serial Number field not sequencing correctly?
                            philmodjunk

                            I would definitely check all the auto-enter options for the serial number field.

                            • 11. Re: Serial Number field not sequencing correctly?
                              Jenny_1

                              Once again many thanks for such a quick response.

                              Believe it or not I seem to have fixed the issues by "Show all records" & deleting correctly unwanted records & then via "Sort records"....so simple!  I found the Sort set was the same as a set for a report I had set up previously, I did not think to check the sort order for auto-entry serial number field??  So far all seems well with the serial numbers.

                              Also thank you for your offer to have a look at what I have done & especially for your patience with a beginner.

                              Jenny

                              • 12. Re: Serial Number field not sequencing correctly?
                                Jenny_1

                                Hi Sorbsbuster

                                I have tried to correct this issue without any success, I am now asking for your further assistance! 

                                As you suggested I have sent you a copy of the database via Mediafire in the hope you can tell me where I have gone wrong & hopefully correct this problem.  This is the link:

                                http://www.mediafire.com/?6szgy07xaq7dfhd

                                Thanks again.

                                Jenny

                                • 13. Re: Serial Number field not sequencing correctly?

                                  Jenny,

                                  You are aware that each time you create a new record then delete it the serial number is preserved so that the next record created will be the next sequential serial number.  This can make gaps in missing serial number which is to be expected.  Once you delete a record its  serial number will never be available to you again.  I hope this helps!

                                  Bill

                                  • 14. Re: Serial Number field not sequencing correctly?
                                    JimMac

                                    I run FMP11v4 on a MacOS leopard.

                                    I have used this Auto-Enter option on every database I have created as the first field [Number type] in a database to keep track of all records ever created by the database as a ID#.  It has never failed to sequence properly.  I use On Creation always. I have let the User us a Cancel button that acctually Deletes the record, thus a "gap" in numbers.

                                    Show All Records in a list type window sorted by your Sequenced Number should reveal the "gaps".

                                    My biggest goof on this was programming an update to an active database, that users were continually creating records.  I then took my updated programmed database, deleted all my test records and then Imported all Records from the Active data base to my newly programmed database.  Opppps... the sequence had changed from that active DB, but my new DB had the old sequence.  Thus, I overlapped so to speak.

                                    The "gaps" were always caused by Delete Reccord.

                                    Also i was testing a script with New Record/Request and the script testing advanced the Sequencer.  Finally on final version of script, there appears a "gap" in record count.

                                    Jim...

                                    1 2 Previous Next