Customer Code

[Leather Knight]

I have these fields:

-----------

Customer ID

Name

City

State

------

I need the Customer ID to automatically populate with the first 3 letters of the name, first 3 letters of the city and the 2 state code(MT,ID,WA) so Walmart in Boise Idaho would show up as "WALBOIID" in the Customer ID after entered. And advice?

[Leather Knight]

I Just tried this field as a calculated field with this in it and it did not work.

LEFT(NAME,3) + LEFT(CITY,3) + LEFT(STATE,2)

[Flynn]

Use the & Symbol instead of +

Left ( Name ; 3 ) & Left ( City ; 3 ) & Left ( State ; 2 )

[Leather Knight]

Cool. Fixed and working. Thanks.

[Leather Knight]

I just noticed a new issue with this. The code worked on all of my layouts, but in some it does not show up until I click on the field, but not all. I don't see any differences... :

Edited January 4, 2006 by Guest

[Leather Knight]

Found bug in my coding. You need to make sure the number format is set to general format or it will blank out if calculation is text and not numeric.

[Zero Tolerence]

I have these fields:

-----------

Customer ID

Name

City

State

------

I need the Customer ID to automatically populate with the first 3 letters of the name, first 3 letters of the city and the 2 state code(MT,ID,WA) so Walmart in Boise Idaho would show up as "WALBOIID" in the Customer ID after entered. And advice?

What happens if you have A Wallgreens or whatever in Boise Idaho? Then you have duplicate Customer ID's.

[sbg2]

Futher, what happens when a company moves to a new location? It may not happen with the bigger retailers but if you deal with smaller retailers or distributors this is a big possibility. Right now I have headaches due to someone else initiating a poor ID scheme, that being using the telephone numbers for our customers.

You want to use a key that will never change and has no possibility of being duplicated.

[Leather Knight]

I fixed the multiple issue. I added to the end of the customer ID a 4 digit number reflecting the phone.

So it goes like this:

WALBILMT4544 = Walmart, Billings, Montana,Phone number last 4.

[sbg2]

Ack! you missed the point. You want a value that:

a) can not change

: will not be duplicated

a) I just got finished telling you that not only can a Companies location change but so can their phone number.

: While it is somewhat unlikely that A Walgreen's and A Walmart in Billing, MO will have the same last 4 digits for their phone number it is not impossible!

[Ted S]

Kevin,

You might want to take a few minutes out to consider the advice you have been given by Zero Tolerance and sbg2. They are spot on. You could be in for trouble with a numbering scheme like you have. If you are being forced to do it that is one thing but with database design, its really best to be looking further down the road.

[Leather Knight]

I made the changes after looking at your advice. I decided to go with first 3 name, first 3 city, 2 state, and store number.

IE: WALBILMT6809 ( Walmart, Billings, MT, 6809) This way it is basically the same code each store goes by. What do you think?

[mf]

There can be situations where having an "intelligent" ID is very useful (from the user perspective).

However, if that is what you choose to do, I would still use another field with a unique identifier (you can use an increment number or random). You can hide this field from your interface, but use this field to create your relationships with other tables.

If you run into the situation where the Company moves, change phone number, or whatever, the "intelligent" field can be changed without causing too much grief.

[comment]

Using a random number for your primary key is like playing the lottery: the individual chances are very small, but given a large number of players (records), there will be "winners".

[sbg2]

Again you could, for example, have three companies as follows:

Shoprite, Billings, MT, 53

Shop N Save, Bilko, MT, 53

Shop N Bag, Biloxi, MT, 53

All your doing at this point is reducing the chance that you will duplicate an ID rather than ensuring you will not duplicate one.

I think the real question is why do you feel you need an ID formatted this way rather than a serial incremented number ID?