AnsweredAssumed Answered

generating specific unique alphanumeric codes, and checking for duplicates?

Question asked by LightningAd on Apr 23, 2012
Latest reply on Apr 24, 2012 by LightningAd


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 fact i would welcome it because thats exactly what i am.


thanks adam