how to create a alphanumerical unique ID? I...

(Shivaprakash TS) #1

how to create a alphanumerical unique ID?

I have 3 columns

[Customer Name] [Town] [Unique ID]

I want to generate a unique ID for each customer in such a way that, if the customer in a city “ROME” is 1st then unique ID should be “ROM-001”

(Philip Garrett) #2


See topic “Sequential Keys” in this article - What is a Key?

What is a Key?

(Shivaprakash TS) #3

@Philip_Garrett_Appsh Thanks. But in sequential keys it will generate only the sequence which we define. what if the town change . Means If the Town is “Rome” then the ID should be ROM-001 for the first customer and in the same town there is second customer it should be RAM-002 when the town is “Paris” it should be PAR-001

(Philip Garrett) #4


I recommend against the approach you described for the reasons explained in the article I cited above.

You might consider including a “DateTimeCreated” field of type DateTime in your table. Give that field an initial value of NOW().

Then make the table’s key the “Town” and “DateTimeCreated” fields. That should ensure that every record has a unique key that reflects the town name and the order the records were added.

(Shivaprakash TS) #5

@Philip_Garrett_Appsh Thanks Philip. Thats a good idea for generating a app unique ID. But my requirement is specific, I want to create customer unique ID/Customer ID which should be like how I explained previously ROM-001

(Eloi George) #6

@Shivaprakash_TS as explained in the article, generating sequential keys like that will cause problems unless you can guarantee that your app will never be used by more than 1 person at a time and never offline.

If you really need that ID column, I would suggest that you think of it more as a “fake customer id” and also do Phillip’s suggestion for your real Key column.

That way when you run into problems, you can more easily separate your merged customer records.

(Shivaprakash TS) #7

@Eloi_George Dear Eloi thanks for the information. The customer ID will be for office reference, it will not be key column. Its mandate to have the customer ID based on the city. We have around 2000 customers and many a there will be lot of difficulty in segregation due to name similarity.