3 Replies Latest reply on Jun 26, 2013 10:03 AM by philmodjunk

    Serial Number help



      Serial Number help



           I work for a small charity and I only started using filemaker in February.  I love it, and the databases that we have created to replace old ones so far have worked an absolute dream.

           However,  I am changing over files from an old database which registers official correspondence, and I am having difficulty with serial number creation which follows the same rules as the other database.  It is a database that will register previous correspondence too, so the serial number has already been printed on the letters that have been sent out.  We would like to continue to keep the same structure of serial number for ease of archive management, and to ensure that electronic and paper archives show the same values.

           The old serial number had the structure 3.g. 1/12; 2/12, 3/12.......1/13; 2/13; 3;13 where the first digit is a sequential numbering for each correspondence and the second part is the year (two digit).

           I know that Filemaker as a database sort of hates two digit years, but it would be a great help to find a calculation that would generate a serial number with this structure


           If anyone could help I would really appreciate it


        • 1. Re: Serial Number help

               Shouldn't be any trouble with two digit years, but the serial number scheme--what I sometimes call "secret decoder ring serial numbers" is far from ideal for use in any database system.

               Nevertheless legacy system compatibility often requires that you set up such a system. I suggest that you add a text field in your main table (where you have one record for each piece of correspondence) to use for supporting this labeling method, but use a straight forward auto-entered, never ever modififed serial number field for linking the table to any other tables that need to be linked by correspondance ID. This allows you to have your label so that your users can see and use it, but keep it from being an actual match field in your relationships.

               To implement your two part system, I'd define two fields, twoDigYear, YearSerial. Define twoDigYear to auto-enter: Right ( Year ( Get ( CurrentDate ) ) ; 2 ). Define YearSerial as an auto-entered serial number and reset the Next serial value setting on this field back to 1 at the start of each new year. This "reset" can be scripted to take place automatically the first time the file is opened in January.

               To show the combined label, you can either set up a text field with an auto-enter calculation: YearSerial & "/" & twoDigYear (set a unique values validation on this field for additional data integrity) or you can simply place the two fields on a layout as merge fields with the / character in between them.

               Then use a completely different auto-entered serial number in your relationships.

          • 2. Re: Serial Number help

                 Thanks Phil,

                 The 2 year worked a dream... I thought it should be simple, and I suppose when you know how it is!  You mentioned about getting the serial number to reset at year end by script.  I've looked and I'm just not sure what script would do that.  Any ideas (sorry for bothering you about it!)

                 Another question:  When I set a serial number with the calculation for a two digit year, will that reamain the same on the records already committed when the year changes?  How could I test that?  I'm using filemaker pro 11, and a mac if that might help to work out a solution!

                 Thanks again,


            • 3. Re: Serial Number help

                   The trick is two fold: 1) You need a script that runs at least once a day. This can be a script run from a server schedule, a "robot" FileMaker file or one that runs each time you open the file via a setting in File Option. 2) you need a way to record the year the last time that the serial value was reset. This typically is done by setting up a field in some sort of utility table. You script compares the year in this field to the year returned by Year ( Get (CurrentDate ) ) and uses Set Next Serial Value to reset it to 1 if the values don't match and then it updates this same field with the current year. If the script finds that the values match, it does nothing. You may need to set this script to "run with full access privileges".

                   Resetting the next serial value does not change the value of any field of any record in your database. It affects what value appears in the serial number field of the next new record to be created.