I would use a field to auto enter a serial number and then convert that number in the alphabet field using the formula:
Middle(s;l1+1;1) & Middle(s;l2+1;1) & Middle(s;l3+1;1) & Right("00"&r;3)
Hope that helps,
Ruben van den Boogaard
I suspect that the best way for you to tackle this is via a series of separate calculations being stitched together as 1. Divide the Letters from the numbers to begin with and create the rules.
You may also find a Custom Function on Brian Dunning's site www.dunning.com which does close to what you want and may be able to adapt.
To calculate the NEXT, you have to know what the LAST one used was. With numbers, FileMaker does this for you with Serial numbers. Make use of this.
If you enter Text before it...like AAA... it will increment nicely to end up with AAA999.... but the next number will be AAA1000... so that is not the answer alone.
If you have a serial number with no text... but make it a text field and start the numbering at 001.
Then in a second field calculate that :
serial number < 1000; "AAA";
serial number < 2000; "AAB";
serial number < 3000; "AAC";
serial number < 4000; "AAD";
serial number < 26000; "ABA";
& right(serial number, 3)
I hope that makes sense...
There are many ways to do this sort of thing... This is just one which is simple enough to help you understand how functions work.
The case() function just goes down the list until it finds a case that matches... and the last one says that if no conditions are met it should just put the seriel number in. (That is a problem for you to solve... as you will not want it to also put in the last 3 digits of the serial number... as the right() function is doing.
I'd just like to add that you should never be keying these strings in if they are primary keys. These should never be seen or used by the user.
I think you might need 2 numbers, primary key (sequential number would be fine for this) and a product ID, which could follow the methods above. This Id would then be displayed and possibly keyed in.
I got as far as 26 and realised I was too tired.
I modified ruben's code, his original code didn't loop very well (ie AZ999->BA000). Also, I only needed 2 letters instead of 3, since 2 letters provides 676000 variants.
th=Int(Raw SN/1000); % indicator of thousand’s place or l1
r=Mod(Raw SN;1000); % indicates 1-999, “mod” makes it loop
l1=Mod(Int(th/26);26); % increments every 26000, “mod” makes it loop
l2=Mod(Int(th-l1*26^2);26) % increments every 1000, “mod” makes it loop
Middle(s;l1+1;1) & Middle(s;I2+1;1) & Right("00"&r;3))
If your solution does not require "sequential" serial numbers but only unique serial numbers, then set the serial number of a field to calculation and within that calculation use the FM Funciton Get ( UUID )
This function gives a unique serial number in the format of: F9D789E3-37D9-4EB7-941A-CF33161360F9
From the FM docs:
Get(UUID) returns text representing a Universally Unique Identifier (UUID).
When using Get(UUID), FileMaker Pro will returns a unique 16-byte (128-bit) string. For example, you can use this function as an Auto-Enter Calculated value to generate a unique ID for a record when it is first created.
Note: Unstored calculations will return a new string each time Get(UUID) is evaluated.
I agree with deninger; I believe you would be better off using Get(UUID) if the field in question is to be used as a match field. I am certainly impressed with Ruben's calc (and Domon's modified version) and can see a place for it as a product ID, say (which may be what David requires), but I reiterate the general caution against making FM match fields anything human readable or meaningful.