2 Replies Latest reply on Apr 24, 2012 5:44 AM by LightningAd

    generating specific unique alphanumeric codes, and checking for duplicates?



      generating specific unique alphanumeric codes, and checking for duplicates?


      I tried this around four years ago in version 8,and the results were average to disappointing, but i have stuck with it because my brain was starting to rebel!

      However, I have just got version 12 and thought it a good time to try re-write my database in a more useable format.

      The big problem i have always had is generating and then checking for duplicates of a large 13 character code that i need to associate with each entry. The code is a "clock number" and is used in the broadcast industry to identify tv ads.

      The code needs to have a very specific format  XXX/YYYY123/999 . The first 3 XXX = my agency code usually COL, YYYY is a four character product descriptor, 123 is the unique 3 digit serial number, 999 is a three digit number which provides the duration in seconds of the video.

      I figured out a way to generate the overall number using a combination of value lists for XXX & 999 and a serial number for the 3 digits.  However, i could not find a way to then combine those disparate elements into a single file against which the database could check for duplicates.

      I also found that once the serial generator moved past 999, it would then display a single 1 - i need it display 001. 


      Please - if anyone can offer a solution to my problems i would be very grateful.  Oh, i won't be offended if you treat me like a novice...in fact i would welcome it because thats exactly what i am.


      thanks adam