14 Replies Latest reply on Jan 30, 2013 7:13 AM by DrewTenenholz

    Student ID

    samjs

      I have created a Student Database for a music teacher. How would I best go about creating the "Student ID" number field that will create a unique number and yet give me the capability to import records from a previous version and then sequentially when creating a new record pick up from the last ID number imported? I've tried "Get ( RecordID )" but that does work when importing "Student ID's".

       

      Thanks for your help!

        • 1. Re: Student ID
          Malcolm

          How would I best go about creating the "Student ID" number field that will create a unique number and yet give me the capability to import records from a previous version and then sequentially when creating a new record pick up from the last ID number imported?

           

          import records

          go to last record

          set next serial number to  record ID number + one

           

          Malcolm

          • 2. Re: Student ID
            FM-K12

            If you are using an auto-enter on your field and then set he next number to be next number in sequence (once you have the first set of records serialized) and set the appropriate set of validations options then every subsequent import of a different set of students should get the next possible set of ID numbers as a standard practice - nothing fancy required.

             

            Bill~

            • 3. Re: Student ID
              christinesephton

              If you are using FileMaker 12, I would recommend using the ExecuteSQL command.

               

              Example:

               

              If you do not use a auto serial number, then set the Auto Calculation to:

               

              ExecuteSQL ("select max(\"serial number field\") from \"table name\"";"";"")+1

               

              If you do want to use auto serial number, then once the import is done, go to any layout association with the table and perform the following:

               

              Set Next Serial Number To

               

              ExecuteSQL ("select max(\"serial number field\") from \"table name\"";"";"")+1

               

              Either way always get you the next possible number.  If you are constantly doing imports I recommend the Auto Calculation, otherwise the Set Next Serial Number will do the trick.

               

              Hope This Helps,

              Christine

              • 4. Re: Student ID
                PSI

                Max ( serialnumber ) will return duplicate serial #’s if 2 users fire it off at the same time. There will be a time lag between the capture of the Max serial number, increment set field and commit. It might only be a millisecond but it will happen…

                 

                 

                 

                John Morina

                 

                Pueblo Systems, Inc.

                 

                CCQ-FM Inc.

                 

                O: 631.549.0969

                 

                C: 516.443.0966

                 

                john@pueblo-systems.com

                • 5. Re: Student ID
                  AlanStirling

                  Hi John

                   

                  I was worried at first, as I can see you are ... 

                   

                  But this is a 'SetNextSerialNo' script step, being run automatically after an import script.

                   

                  This script is unlikely to clash with another instance being run and if it does, it will probably produce the same result!

                   

                  Best wishes - Alan Stirling, London, UK.

                   

                   

                  Alan Stirling Technology Ltd, 135 Lisson Grove, London NW1 6UP

                  +44 (0) 20 7724 2456 - alan@ast.fm - www.ast.fm.

                  FileMaker Certified Developer for versions 7, 8, 9, 10, 11 and 12.

                  • 6. Re: Student ID
                    christinesephton

                    The reason I gave him the Max solution is becausee he explained the import was only done by him.

                     

                    Christine

                    • 7. Re: Student ID
                      PSI

                      Sorry I guess I didn’t read it that carefully. I thought you were suggesting that as a way to assign the student ID during use.

                       

                       

                       

                      John Morina

                       

                      Pueblo Systems, Inc.

                       

                      CCQ-FM Inc.

                       

                      O: 631.549.0969

                       

                      C: 516.443.0966

                       

                      john@pueblo-systems.com

                      • 8. Re: Student ID
                        christinesephton

                        No need to be sorry, you were 100 percent correct in your suggestion, and I agree that the Auto Serial is much better if you are using it to generate a serial number entered..especially by several people at a time.  You know there is always more than one way to approach things.  It just how bloated, simplied or specific you want to get.

                        • 9. Re: Student ID
                          Oliver_Reid

                          If this import happens regularly try using Get(UUID) to genrate ids. It gauarantees that two ids will never, ever ever ever, be duplicates across all past, current, and futere databases in the unverse.

                          • 10. Re: Student ID
                            samjs

                            So if I use Get(UUID) as the student ID, I can create an updated database and import the Student ID from the old database and I will be gauranteed unique numbers for new data entered?  Does it evaluate the all the IDs currently in the database before it creates a new one?

                            • 11. Re: Student ID
                              Oliver_Reid

                              "So if I use Get(UUID) as the student ID, I can create an updated database and import the Student ID from the old database and I will be gauranteed unique numbers for new data entered? "

                               

                              Yes, exactly.

                               

                              "Does it evaluate the all the IDs currently in the database before it creates a new one?"

                               

                              No. It creates an ID like: 4D61F292-0128-4E84-9251-4483F01D2293  . Not certain, but I believe the current time stamp and Machine unique ID are incorporated in the algorithm , still leaving several billion variations from which creates the ID.

                              • 12. Re: Student ID
                                BruceHerbach

                                Hi,

                                 

                                If you want to keep and extend the current numeric sequence.   Use a script to determne the next ID value and store it in a global variable.  Then go to the import layout.  omit all and import your new record set.   Sort the records so they are in the order you want. Then Do a replace field contents with serial numbers starting at the next ID value and check the box "Update serial number in Entry Options?". 

                                 

                                This should do a clean import and keep the same numeric sequence.  If there are other related tables using the ID then this may not work or you will have to come up with a method of updating the foreign key in the other tables.

                                 

                                ----

                                Using UUIDs avoids a lot of issues but isn't helpful when you want to see the records in a sequence based on when they were created. 

                                 

                                Another option is to use the UUID as the primary key and add a sequence key.  This would be a new field that is a numeric serial number.  Add the field and fill in the value for all existing records using Replace field contents.  Since this field isn't part of the import it will automaticly populate when you import records with a new value.  FileMaker should keep the values for you so if some one else creates a record during the import it should get it's own value and leave a one record gap in your import sequence.

                                 

                                Bruce

                                • 13. Re: Student ID
                                  s

                                  It has been reported that there are cases where get(UUID) is not unique for some older models of computers:

                                  https://fmdev.filemaker.com/message/105238

                                   

                                  oops, my bad. that post is about get(persistentid)

                                   

                                  Message was edited by: s

                                  • 14. Re: Student ID
                                    DrewTenenholz

                                    S --

                                     

                                    I think you have confused 'PersistentID' with UUID.  The thread you referenced is about trying to reliably and uniquely identify a specific machine accessing a FileMaker system.  UUID is about consistently and uniquely identifying a record within a file. 

                                     

                                    From what I can tell, FIleMaker is using a 'version 4' UUID function (completely random), which divorces the ID from the machine which generated it and the date/time the ID was created (see more at http://en.wikipedia.org/wiki/UUID#Version_4_.28random.29 )

                                     

                                    I would encourage our original poster (and everyone) to use the UUID function as one record identifier in every table in every file they create.  You don't need to use it in relationships or in any way right now, serial IDs are a lot easier to understand and debug.  Eventually, issues of synchronization and restoration from backups will make you (or the person who follows you on the project) very glad they are there.

                                     

                                    -- Drew Tenenholz