There are several points to consider here, Craig.
Primary keys (unique serial numbers) should be meaningless, FM-generated numbers using the auto-enter serial table in field definitions. These keys should be used in joining all of your relationships. Because they are meaningless, they won't break your relationships if, for instance, someone changes their last name.
It is okay to have another 'quick-find' shortcut key for your customers. (which is what you are describing). However, you will quickly run out of 'logical' quick-find IDs because, as in your example of BLOJ, you will have John Bloggs and Jill Bloom and then what? Someone would need to have a quick-find key which isn't logical (you'd have to assign Jill Bloom BLOO instead) and then your Users would think that Jill Bloom isn't in the system because only John Bloggs comes up when they type BLOJ.
You can do it ... in the field definition for this quick-find that the validation must be unique and let your users assign it when new customers are added. But I've been down this road many times over the years with this exact request and it will eventually (and usually sooner than you expect) bomb out on you.
Best overall to simply let User type the last few letters of a person's name and find the customer from the resulting list and leave your CustomerID to be the unique auto-enter FM-generated serial number.
Hi Laretta Thanks for the reply. I understand what you are suggesting and don't necessarily disagree however the database that I am using has some needs that may mean that need to continue with letters code. 1. I have looked at all of the customer names (I will use this term whilst it is not quite accurate for my database) and there are no double ups. This group is finite so there will not be any more added to the list 2. Each week I need to import and update information regarding each customer. At this stage I manually add the codes so that the information can be referenced elsewhere. 3. If I was to change the customerID to be numerical is there a way that can I then have the appropriate id automatically added when new information is imported. Again many thanks for your suggestions.
It sounds like you are trying to get a less than perfect system to work a little bit less imperfectly.
I totally agree with everything LaRetta said in her last post, but if you want to do this anyway, a simple calculation will produce the text you specify:
Left ( SurnameField ; 3 ) & Left ( FirstNameField ; 1 )
Whether or not you can add an ID number to imported data depends on how consistent the imported data is. If you can rely on names to be 100% consistent in spelling and the combined firstname, surname is unique, you can set up a system that uses the name fields to look up the ID number for the imported data either during the import or in a script that runs through the newly imported records immediately after import.
However, get just two different "John Smiths" in your imported data and you'll have trouble. So, if possible, I'd include as many other fields as possible to try to keep the matching values unique. Sometimes a phone number or address can help here.
You'll also have to look out for changes to names and other such data so the change doesn't keep the imported data from failing to look up the correct ID.
We would need to know more about your system. For instance, is this a flat file or do you have related records associated? Because if so, you're flirting with problems.
You cannot import using the 'update' feature unless you match on a key (unique) field. And if ANY of the data involved in the update changes, the person will be added as another new entity OR skipped (depending upon your specification in the import).
I would import ADD to a temp table. The temp table would hold a calculation (or comparison method) to your real data. And them once the proper key is in place, update your actual data from that table. In this way, if any customers don't match, you will have visual error check and can correct it before final import. It sounds like an added complexity; only you can decide whether you need it.
It would help to know more about this 'incoming' data, ie, what information is updated, what is the data source (tab-delimited, Excel, FM table etc). If there is any other 'unique' quality to the Customer file (such as social security), you could use that because it would never change. Don't count on phone, address etc (or even name because they can change names). That's why an intermediate file (stopping any which don't match for human intervention) might be the way to go.
I began designing the system I still use years ago. I used "Short name" as a key field. Usually this was the first four letters of a person's last name and the first letter of the first name. I really don't recommend this. Although for various reasons, mostly to do with concatenation, the system still uses Short Name, it's no longer under the control of the user (me). It's scripted and I feel quite safe with it, but the problem is it was really the "long way around". Auto generated serial keys are easy and safe. For the last while I've used nothing but. Any other way can cause problems down the road and I can say from experience that they can be very time consuming to solve.
Thanks to all who have replied. Your perspectives have been helpful in considering the set up of my database
I am going to attempt to change the way that the database is set up. To answer LaRetta's queries the database does have related tables. Each week i import/add data from an excel spreadsheet that contains statistics to a table. My problem as i see it is that my database is set up with name and surname fields separate whilst the imported information has names in the format of Smith, J. I somehow want the two name formats to be linked - i was planning to use an alphabetic code as i originally suggested. is there a way that the database can recognise the imported format and link it automatically to the serial key.
If there are any tips on how i can link this imported data easily and automatically i would be very thankful.
If Smith, J. is all you get then I would suggest that you still import the name into one name field, Craig, and have a NameCode (calculation, result is text) with:
Upper ( Left ( name ; 3 ) & Left ( RightWords ( name ; 1 ) ; 1 ) )
Note: You haven't said how accurate the incoming data is in regards to the name. Could they ever have a full first name, such as Smith, Alex? Well, the above calc will protect from that possibility but, of course, garbage in, garbage out. Then create a relationship from this NameCode to your Customer table on this code as:
Statistics::NameCode = Customers::NameCode
Your CustomerID (the true serial ID) would be a lookup from your Customer table. Set this CustomerID as standard number in case you need to correct an entry. I would also add some protection to look for entries without a related Customer record (so you can spot any invalids). Pseudo-script, at the end of your import could be:
Enter Find Mode [ uncheck pause ]
Set Field [ Customers::CustomerID ; "*" ]
Set Error Capture [ On ]
Perform Find [ ]
If [ not Get ( FoundCount ) ]
Show Custom Dialog [ "No invalid customers found." ]
I would also place the CustomerID on your Statistics layout. Simply turn off entry to the field (in field behavior) and set it as a boolean number result (in Inspector) where, if it has a number (meaning it has a related customer record and is valid), produces "Valid". If you have any questions on any of this, we are here to assist.
many thanks to all who gave their advice. apologies for the delays in responding - i have been on holidays so have been put this on the backburner. i have trialled the suggestions over the last week and whilst i hear the concerns with using an alpha keycode i have been able to get the database to work with one. i have put in a backup (if you can call it one ) of a numeric code as well so hopefully that will address some of the concerns that people have raised.
i am very thankful for all the quick and helpful responses. the things i have learnt by trawling through this forum has been extremely helpful for a newbie to filemaker