3 Replies Latest reply on Jun 23, 2009 12:55 PM by Socio.t

    Calculation of serial with date (restarting every year)



      Calculation of serial with date (restarting every year)


      Hello everyone,


      I'm a new user of FileMaker Pro and I'm trying to create this database to register my company projects but I'm starting to have some trouble generating this project code automaticaly.


      What I want to do is create a serial number that combines the two digits of the year with a 3 digit project code. Something like this:


      09001 (09 is the year, and 001 would be the serial number for project code).


      I tried to use this formula, but happens that it dosen't restarts the counting if I change the year.


      "Right( Year ( ProjectDate ); 2) & ProjectCode"


      Is there a way to restart serial number generation accourding to the year of the date we set? Can you advise me on something?


      Thank you very much.





        • 1. Re: Calculation of serial with date (restarting every year)

          The simplest solution is not to do it and keep your serial numbers as simple serial numbers.


          If you must have this, then the approach I can think of is to create a script that runs when the file opens. It can compare a date stored in a field against the current date to determine if the year has changed. If so, then it can use Set Next Serial Value to reset the serial number field back to one.

          • 2. Re: Calculation of serial with date (restarting every year)

            Howdy socio.t,


            I do the same thing for uniqueID's on raw material tracking.  As I thought about how to do it, I realized that it was just easier to include the update in year end maintenance of the Dbase than to automate it.


            On the first day back of the new calendar year, I have a "to do" list which includes going into the UniqueID serial and changing the "WS-2008xxxxxx" to "WS-2009xxxxxxx".  It takes less than a minute a year so I didn't bother trying to automate it further.  If you have enough digits, compared to the number of projects you do in a year, you won't have to worry about overwriting the year holder digits.


            Phil is correct, the whole approach probably isn't needed.  I've used it when fishing for records to archive, but I'm sure there would be another way to do that step.  For myself, I leave the following "xxxx"'s the same so that I have an incremented ID# as well as a clue when the material came into the building...but then again, I could find that other ways as well.

            • 3. Re: Calculation of serial with date (restarting every year)

              Thank you very much. This really helped me. I found a great script that helped me.


              Best regards,