I would like to create a unique serialnumber on each of my clients. I would like to use the three first letters of the companys name and then have three numbers that will increase with one number for each client whos have the same first three letters.

Example:

client.1 McDonalds, NewYork = MCD001

client.2 McDonalds, Miami = MCD002

client.3 Seveneleven, Ohio = SEV001

client.4 BurgerKing, Dallas = BUR001

I have tried to make this but i used the auto-enter serialnumber funktion, and this will not work as i want it to. Then, for example, client.4 will have the S/N BUR004, and that not what i want.

When the new client is created it must look on what is already stored in the database. Anyone got a tip?

Well, first this way of making serialnumbers is not really recomended because it uses 'meaningfull' serialnumbers.

There are several discussion about the disadvantages of that on the forum.

If, for some reason, you still want this then you could do something like:

-define a calculated field that contains the first three characters from your serialnumber field.

-define a 'self ' relation from that calculated field to itself

-use something like Count(selfRelation::SomeField) + 1 to get the next serial value.

But think twice before resorting to such a construction...

Regards,

Ernst

Thanks, i will think twice. I can see the problem.

what I like to do is use two fields. One is a numbered ID number that increases by one, and then a calculation bases on the fields I want to put together. Kind of a quick serial number with things such as, day,time,rank with class ID put into one. So I can quickly look down the list and see a lot of info in one field. It may not be the best way, but it works for what I need. If you that is what you need then just make another field, and make it a calculation then select each field you want to combine with the "+" between each field entry.

student_ID + class_day + class_time + class_rank

something like this.

I agree with the responses so far. The problem is that you are using a compound key. This breaks the rules of relational database design.

One thing that should result during the process of database normalization (process of deciding what variables go in what tables) should be that key variables should not be in anyway related to other variables (as others have suggested). And, they should not be compound variables.

http://www.fmforums.com/threads/download.php?Number=103594

Here is a link to a solution that uses year and weekofyear. You will need to modify it to use Left(Client,3) rather than Year or weekofyear, but the function is the same. Thanks to [color:"green"]-Queue- for the mechanics of the example.

For a more succinct explanation of the incremental "serial" issue, search for "secuencial fields" (include the quotes) and check for the response by [color:"green"]-Queue-.

Paul

(Disclaimer: This was done with FM6, so there may be a much smoother way to do it with FM7.)