?
Solved

auto generating Customer ID

Posted on 2003-02-27
14
Medium Priority
?
264 Views
Last Modified: 2010-04-17
Hi,

I'm working on a VB project and need to generate Customer ID based on the Customer last name and position(record position). But I don't want the Customer ID to go beyond 10 characters. It should permanantly be within 10 characters. How can I do this?


Thanx.
0
Comment
Question by:soccerl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +2
14 Comments
 
LVL 3

Expert Comment

by:coolsumu
ID: 8032856
You can have a PARAM (a parameter) table having a single row only, where you can store incremented nos or autonumbers and on runtime club this autonumber(some fixed no. of digits) with fixed no. of characters to form the CustomerID.

If you are using Oracle you can create a sequence and use it as above.
0
 
LVL 9

Accepted Solution

by:
bhagyesht earned 100 total points
ID: 8032939
u can use the first 3 or 4 characters of the customer's last name and add to it the number of customers having the last 3 or 4 characters same. the rest of the charactes could be a serial number.
your query could be

SELECT Count(*)+1 AS newSrNo FROM <TableName> WHERE <lastname> Like "<first3/4characters>* "

like if your last name is johnson then your query could be
SELECT Count(*)+1 AS newSrNo FROM <TableName> WHERE <lastname> Like "joh*"
or
SELECT Count(*)+1 AS newSrNo FROM <TableName> WHERE <lastname> Like "john*

what ever value u get just add it to "joh" or "john" and that will be ur unique key
0
 
LVL 2

Assisted Solution

by:IeuanJ
IeuanJ earned 100 total points
ID: 8032968
assuming you have already extracted the name into a variable calles strName and the position into a variable called intPosition ....

dim intTemp as integer
intTemp = Len(CStr(intPosition))
if len(strName) > (10 - intTemp)
  strName=left(strName,(10 - intTemp))
End If
strCustomerId = strName & CStr(intPosition)

strCustomerId will now contain a 10 charachter or less string with the last x digits being the position and the rest filled out by as much of the name a possible.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:soccerl
ID: 8034464
Thanx 4 all ur responses. But what my client wants is a CustomerID of this nature: <First 3 characters of Customer last name> + <position> eg: If the customer is John and at third position then, his ID  would be: JOH3.

I've already done this this, and works fine. But, my worry is that as the number of customers keep  growing, the ID will finally go beyond 10 characters.

For bhagyesht and IeuanJ, u're ideas so good but the ID will keep growing to even beyond characters b'se of Count(*) or intPos.

What I need is a Unique Customer ID  that will never go beyond 10 characters.I hope this can be achieved if I ignore Count(*) or record counting.

I need ur advice.

Thanx.
0
 
LVL 2

Expert Comment

by:IeuanJ
ID: 8035013
I would add a date field to your database to record when the customer has registered, then do the following (The SQL below is pseudocode, im sure you can work out the specifics).
--------------
SELECT max(dateregistered) from customers

select position from customers where dateregistered = max(dateregistered)

if position >= 9999999 Then
    newPosition = 1
else
    newPosition = position + 1
End If
--------------
That position will loop back around to 1 after reaching 9999999 therefor never becoming longer than 7 digits.  Customer 1 will probably be long gon by now unless this is for a bank or gas board type thing.

Now all you need to do to create the ID is :
--------------
customerID = left(strName,3) & CStr(newPosition)
--------------
0
 
LVL 9

Expert Comment

by:bhagyesht
ID: 8039424
well IeuanJ thats not gonna be unique if it loops back.

Anyway what is 9999999 persons having the starting 3 characters of the last name same? if you are designing applications for such huge volume of data then y are you restricting to 10 characters?

if you still want to do that then one more way is to write your own function which will take a number and return you a character like say
A for 1
B for 2
and so on till
Z for 26
once u have done that then u can have the number of character as 9999999*2.6

still want more?
a for 27
b for 28
c for 29
so on till
z for 52
once u have done that then u can have the number of character as 9999999*5.2
still need more?
use the other signs as well, but still I would say its better to use 11 or 12 digits  and so on.
0
 
LVL 2

Expert Comment

by:IeuanJ
ID: 8040424
bhagyest there is no such thinig as a unique id, especially if you are going to limit the number of digits involved.  though after 10 million customers I think its reasonable to overwrite from the beginning dont you ?

Lets face it an application for more customers than that would not have a 10 digit restriction.
0
 
LVL 9

Expert Comment

by:bhagyesht
ID: 8040566
sorry leuanJ the message's first line was for you the rest was for our friend who asked the question. I have no objections execpt that the fact that you in the code wrote that it should loop back which would lead to problems so the message was just to increase the number of alailable records. Not for you.
0
 

Author Comment

by:soccerl
ID: 8046774
Thanx guyz. U're ideas are so good, but I have to absorb my client's requirements too.

I have this idea: Suppose, I include the Year, and Month in Customer ID like this: <First 3 chars of Lastname>+<2 year's digits>+<1 month's digit>+<k> ;k is the record position/number. eg: for John registered in March 2003 would have ID: JOH033k (k = 1,2...n)

To restrict the above ID from growing, I would reset k =1 whenever it turns 1st of the next month. so that k would always reflect the number of customers in a month. I hope this would always generate a Unique ID and I believe my client wouldn't mind atmost 15 characters for cases when customers' number increase for some month.

But, I haven't yet come up with afunction that accomplishes the this. I need ur assistance.

Thanx for your continued support.
0
 
LVL 2

Expert Comment

by:IeuanJ
ID: 8070424
cant you just use year (y) month (m) day (d) count (c) as follows YYYYMMDDCC, thats 10 digits and unles you get 100 customers a day should work out, even if you do you can use letters as well in the count to incerase the number to 36*36 (cant be bothered to work it out).  Your method would work but only if you use two digits for the month, or perhaps use 123456789ABC with ABC being months 10,11 & 12.
0
 

Author Comment

by:soccerl
ID: 8142857
Thanx IeuanJ , but how can I make CC (counter) always start from 1 whenever a month ends.
0
 

Expert Comment

by:CleanupPing
ID: 9447467
soccerl:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 9

Expert Comment

by:bhagyesht
ID: 9449227
Split between IeuanJ  and bhagyesht
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Progress

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question