You do not want a customer code which has meaning. What if the person changes their last name? Well, there are many reasons that you should stick with using a number field named CustomerID and make it auto-enter and select serial option and start with 1. This is the only way that you can guarantee your relationships will remain intact. Every table should have this unique ID.
But you can create a calculation ( result is text) which you can display. It would be something like:
Left ( LastName ; 3 ) & CustomerID
While LaRetta makes a good point about not using the last name in the ID field, it might be better to start the ID numbering at something like 1001, or 10001, depending on how many customers you plan to have. Starting at 1 seems novice. With that said, it is also possible, and sometimes advantageous to use alphanumeric IDs, because they give you almost 4 times the possible IDs, using the same number of characters as a strictly numeric ID would give.
However, I don't think your method is entirely simple to implement. It sounds like you want to increment the numeric portion of the ID, based on the first three letters. So if you have 3 customers with the last name of Smith, their IDs would be SMI001, SMI002, SMI003, correct? One possible way to do this is by a self-relationship of the customer table, based on the first 3 letters of the Customer ID. This would give a calculated field the ability to determine the largest number used for customer IDs with the same first 3 letters.
I do this to generate customer account numbers (not used as a primary key) although my method below was written quite some time ago, its not the most optimal way of doing this ! But still... it does work.
You will need a relationship using two table occurrences of the clients table
Join these with the accno field (or whatever you have it named as)
ClientTable::accno = ClientTable_check::accno
The script would go something like this :
SetVariable [$i ; Value:"001"] Loop Set Variable [$test ; Value: Upper( left(surname ; 3)) & $i ] Set Field [accno ; $test ; If [not isempty(ClientTable_check::accno)] Set Variable [ $i ; Case( Length($i + 1) ≥ 3 ; $i +1 ; Length($i + 1) =2 ; "0" & $i +1 ; Length($i + 1) =1 ; "00" & $i +1 ; $i + 1 )] Else Set Variable [$done ; Value: "done"] End If Exit Loop If [$i > "9999" or $done ="done"] End Loop
In the code above, from memory, I think I had to force it to put the leading zeros in place, hence the case statement.
Lets take Smith as an example
What its basically doing is looping through setting account numbers starting at SMI001
Checking if that exists in the clientTable_check occurrence, if not exiting the loop, otherwise it will move on to SMI002
As mentioned, I created that some time ago, I have recently been thinking of a way to speed this up such as
performing a search for SMI* then using the max function to get the next available acc no, but Ive not had time to play yet.
The methods described here by Etripoli and SWS can result in duplicate codes in multi-user systems as you might get two users generating records at the same eact instant in time for people whose last name starts with the same first three letters. It may be unlikely, but it can and does happen.
Like I say, mine is certainly not the best way of doing this, I realise this myself and will be changing it.. however since its not a primary key, nor used for any other purpose other than display, the possible implications are limited.
I wouldn't call the implications limited. :smileywink:
Having two customers with the same ID code, even if it's not a Primary key could cause all sorts of mischief resulting in confusing and possibly inaccurate reports since you are likely to still search and/or sort on this entry.
Agreed. Another solution would be to force the field to be unique, therefore the duplicate ID would be given an error message upon commit. But in this case, an auto-entered serial number would probably be better.
Agreed again :-)
The above was actually created for a single user system but is now being adopted as a multi user (hence the need for a re-work) Im not disagreeing one bit that its not perfect, in fact... Im hoping this discussion continues :-P may save me some time working out a better method.
My requirements are to have incrementing alpha numeric codes too.. there has to be a fool proof way, ive not really started working on a solution, but will be...
If SMI001 exists, the next must be SMI002 etc
Using an auto enter serial would end up with numbers everywhere, soon jumping into larger numbers.
i.e. If you have 15,000 records, but only 2 Smiths one at the start, one at the end
You would end up with :
Sage Line 50 for instance had (may still have) an 8 character limit for the account number
A method such as this allowed 99999 of each first three letter groups before you started to run into problems, which could take a while.
Using an auto entered serial number, you would run into the issue a lot quicker.
Edit : 999 + 3 letters =/= 8 (LOL) I should have put 99999
Hmmm, I'm just brain storming here and could be way off, but maybe with filemaker 11, you could set a unique values validation rule and then use an OnObjectValidate script trigger to confirm that the value is truly unique and to bump it up one if it is not.
That will not get away from the issue that User may not have committed their record yet.
"it might be better to start the ID numbering at something like 1001, or 10001, depending on how many customers you plan to have. Starting at 1 seems novice. "
Well I was in a hurry and I truly was trying to turn the person AWAY from this thinking (and rightfully so). If I were to pad the number, I wouldn't do it in the serial, I would do it using SerialIncrement() within the concatenated calculation (new field I suggested).
Regardless, whether BRO1 or BRO10001, it means nothing. What is the benefit from knowign that a customer is one of 3600 BRO-beginning-3-letts customers? Besides, what business is it of the customer how many BRO customers you have? My point is that it shouldn't be used because it moves you down the 'this ID means something' pathway. Bad idea.
If you MUST do this (and please do not), then at least use a Serial table and tightly script and control the whole thing. And no, don't ask me how to do it because I do not believe in assisted suicide. :smileywink: