2 Replies Latest reply on Dec 13, 2013 7:41 AM by duncanbaker

    Creating a project number from year and last project number

    duncanbaker

      Hi folks

       

      Gave this a go and couldn't initially get it to work. I'm looking to create a project number (note this is not a primary key/serial as such, but should be unique). I'd like this to be auto calculated on the creation of a new record. The format is:

      Last 2 digits of current year

      Followed by a 3 digit number, starting from 001 and incrementing by 1 each time

       

      So, first record created today would be 13001, second would be 13002 and third 13003. Come Jan 1st when creating a new record it would be 14001 and then 14002. It is not expected that the number of records created in one year would ever get close to exceeding 999.

       

      Thoughts, ideas, pointers?

       

      Thanks all

        • 1. Re: Creating a project number from year and last project number
          Mike_Mitchell

          Duncan -

           

          There are a couple of ways to accomplish this:

           

          1) You can use a self-joining relationship based on a field that holds the prefix (in this case, the year) and use the Max function. Be careful with this one; it's vulnerable to creating duplicates if two users execute at exactly the same time, so be sure to validate the sequential number as unique and re-generate if you get a validation error.

           

          2) You can use FileMaker's intrinsic serial ID creation capabilities and just reset at the beginning of the year in your opening script, checking the year against the next value using GetNextSerialValue and then setting it, if necessary, using the Set Next Serial Value script step. The only thing you have to be careful of here is making sure you keep the serial number synchronized between copies of the database (if, for example, you have a development version and a production version).

           

          HTH

           

          Mike

          • 2. Re: Creating a project number from year and last project number
            duncanbaker

            Thanks as always Mike. Let me digest the options, give them a shot and I'll come back to you if I have any further questions. Much appreciated.