2 Replies Latest reply on Aug 23, 2009 9:25 AM by Vernassoc

    Adding serial number to lookup field



      Adding serial number to lookup field


      I am setting up a basic db to record/organize my business's projects over the past 15 years. I need to auto enter unique project IDs by combining the contents of a field that holds the year the project originated, a text character, and a serial number. In other words, the project ID for the first project we did in 2005 would be: "2005-1," the second "2005-2,"and the fifth 2007 project would be "2007-5," etc. Therefore, not only does the ID need to refer to another field and include an additional text character, the serial number component needs to adjust (or reset) each time the year changes.


      I have tried a number of the calculations, but they either have simply entered the year, with no text character or serial number, or they increased the year by 1. (E.g., in the example above, the first project was "2005," the second "2006," etc.)


      Maybe I should just stick to auto entering a simple serial number, but it would be a big help if I could refer to the project ID to tell how many projects we initiated in a year and in what sequence they arrived.


      Many thanks for any help offered.

        • 1. Re: Adding serial number to lookup field

          A simple serial number is by far the "best practice" for uniquely identifying each record and for use as a primary key.


          If you want this type of label for reporting purposes:


          Define an auto-enter serial number field. (This field CANNOT be used as primary key as it will not be unique.)

          Define a text field that auto-enters: Year ( get ( CurrentDate ) ) & "-" & SerialIDField


          The catch here is that you need to include a script that checks the current date and uses Set Next Serial value to reset this serial number field back to one the first time the file is opened at the start of a new year.


          On the other hand, if each record has a date created field and you define a relationship that self-joins all records of the same year:


          Year (datecreatedfield) & "-" & (PrimaryKeyField - min(selfjoin:: PrimaryKeyField) + 1)


          Though the result cannot be stored and computing the value may take longer than desirable if you have large numbers of records created each year.

          • 2. Re: Adding serial number to lookup field

            Thank you! I thought as much--that I should stick with a simple serial number in order for it to be used as a primary key. This particular d.b. doesn't really need anything fancy, and it already has the publication-date field defined, so sorting and finding is straightforward. And we only have on the order of six or seven projects in any given year, so counting them will be simple enough.


            Thanks for saving me more searching, head-scratching, and self-recrimination.