Client Codes

[pete10uk]

Hi there.

I'm completely new to FM, so I want to start off

with a simple database of my clients and later build on this, to achieve a full business solution.

The first challenge I have come across is how to get FM to generate unique client reference numbers using the first letter of the client name and a 3 digit serial, i.e. A001, A002, B001, B002 etc.

I have been playing with the Auto-Enter calculation option and can get the first letter using the Left ( text ; numberOfCharacters ) calculation but I cant figure out what to use to complete the code.

Could any of you point me in the direction of a solution.

Cheers in advance.

[comment]

The best solution is not to do it:

http://www.fmforums.com/forum/showtopic.php?tid/191695/post/273995/#273995

[pete10uk]

Hi Thanks for the response,

After reading the linked post, I gather that I should NOT use the client code as the primary key and understand the reasons for this but I would still like to automate my custom client code generation to acheive a code as mentioned.

Cheers

[comment]

Have you read the entire thread? Esp. my last post in it:

http://www.fmforums.com/forum/showpost.php?post/274738/

[pete10uk]

Thanks again for your time.

I fully understand how important it is, not to use any field that means somthing for a relationship etc. but to keep my existing system flowing, I would like to include the letter followed by the 3 numbers, as this is the refference sent with customers electronic payment transfers.

My suppliers send invoices to me that look like they adopt a similar method and I can only presume that they use a database for it as they deal with 1000's of companies. My current solution in excel is just to look down the list and manually enter the next serial but as you can imagine this is now getting tiresome.

What would you suggest? Change my existing system and contact all my customers giving them say the last 5 digits of their new id number or is there another way of figuring it out?

Cheers

[comment]

I am not sure I understand the real problem. Suppose you assign customer Adam code "A001", and customer Betty code "B002". Now enters a new customer, Abby - and she gets code "A003". Who is going to complain that there is no "A002" code assigned?

[David Jondreau]

Create a calculation field in the Client table called Initial and set it =Left(ClientName;1). Then create another table occurrence of the Clients table (call it Clients Initial) and relate it to the Clients table having Initial = Initial.

Then create another field, number field with a calc auto enter (Number) = Max(ClientsIntial::Number)+1. Your auto enter calc for the client number is now = Initial & Number.

[LaRetta]

Max(ClientsIntial::Number )+1

Have you tried this in multi-User mode? Any time you look to another table occurrence (or another table or another file), it cannot update until the User has exited the field. So two Users can assign the same number, ie, the first User hasn't committed yet.

[comment]

Going in a circle again: the threads I have linked to above explain why you should NOT use this method.

[David Jondreau]

Going in a circle again: the threads I have linked to above explain why you should NOT use this method.

I understand the limitations of the answer, but from what I gather from the OP, this sounds like this is a single-user solution.

And I'm not the Normalization Police. If someone insists on doing something that might bite them in the ass later on, I'm happy to oblige them if they ask for help, after they've gotten the proper cautions, which you've supplied.

[comment]

I may be mistaken, but I understood the last question as looking for an alternative.

[pete10uk]

No I don't think that you are understanding.

No one will complain. The only reason that I want to use this code for is for the 700 customers who pay by electronic transfer and send the payment with this number as the reference and for the past 5 years I have used this code as "my primery key" so to speak.

The only issue is that I would rather not try and get the 700 people to change how they send me money, as I know that it would be a nightmare. My initial thought was to keep using the same system but maybe that is not possible.

I will have another think about it but thank you all for your help.

Cheers

[David Jondreau]

NOW were's going in circles!

:yay:

[LaRetta]

You've brought up twice that 'the other database people' use it and that you've had no problems. I appreciate that. I know the method you want can break and if relationships are based upon it, it will hang you when two customers end up with same code. You can use that code but don't use it for your relationships!! Set it to be unique, enter all your 700 client codes (and if not unique change them). Then, when new codes are needed, create them manually however you wish. But treat them as a 'required unique' field but NOT a key. Use serial increments to hold your relationships together.

DJ, I won't assist someone if they may hang themselves. That is my personal belief and right and I can speak up about it if I wish to just as you can. I didn't realize you knew you were helping someone to hang themselves. You can do that if you wish ... but please do NOT refer to us (or me) as normalization police just because we were trying to protect someone. It didn't feel very good.

Edited January 31, 2008 by Guest

[comment]

I see no reason why you couldn't use the existing codes for current customers. All you need is a method to generate new codes for customer #701 and on - a method that will (a) be reliable and ( not generate a duplicate of one of the existing 700 codes.

[David Jondreau]

I was referring to myself as not being NP. It wasn't my intent for you (or comment) to take that as a sly insult. Just my perspective on helping people.

I believe there's two ways of learning. Having someone tell you how (and why) to do something correctly, and doing it the other way and suffering the consequences. The OP does not seem interested in the first way, so I'm willing to help them in the second*.

*Disclaimer: as long as the consequences don't involve innocents getting hurt and some other common sense exceptions, which doesn't seem the case here.