Important, The solution must assign alpha numeric "Item codes" This gives us upwards of 1200 combinations per style number vs just 99.
Do you mean that the auto-generated two digit code would number from 01 to 99 and then from AA to ZZ or something like that?
So you might have FMAZ, for instance?
a-z and 0-9 would generate 1,296 (36x36), two characters only
add A-Z and you take it to 3,444 (62x62) two character combinations
create a loop script (outer and inner loop) and see how many records might be created out of a set of alpha-numerics.
For the purpose of this solution, "a" and "A" are considered the same.
Character types should combine ie 1A, A2, A1, 2A etc.
The output must be dependent on the department code.
If this is too difficult, I may be able to designate a third character to the auto-generated field (table 2 ,field 2). At that point, every item will have a unique three character sequence irrespective of the department code. That will provide upwards of 40k unique combinations.
Going to the "end game"...There will come a time when you may want to sort by StyleNumber. That will happen in this order:
And so on. If you think about it...the "code" that is "next" in line will be a bit difficult to determine. Let's say you create an item which is assigned "A9". Telling FileMaker to pick the next code in line will be a bit difficult. It would probably be "AA".
I think I'd make a table with every combination (use inner and outer loops as Beverly has suggested), and I'd assign a straight numeric value to each code. Then you could have your system determine the last code which was used, select the number associated with that code, then select the code associated with the number which is one higher than that.
A9 = 103, for instance.
AA = 104, for instance.
A9 was used. System determines that's code 103. System decides 104 is next, and assigns AA.
OR...Use something like FM0001, FM0002, etc. That'll be quite a bit easier to manage, I'd think.
I just wrote this script to generate with a-z & 0-9 taking two characters. revise to fit your needs. there may be functions that let you auto-enter. YMMV
# create a string to use
Set Variable [ $digits; Value:"0123456789" ]
Set Variable [ $counter; Value:97 // start with char "a" ]
Set Variable [ $alpha; Value:$alpha & Char ($counter) ]
Exit Loop If [ $counter=97+25 // a-z ]
Set Variable [ $counter; Value:$counter + 1 ]
Set Variable [ $alphaNums; Value:$alpha & $digits ]
# actual loop
Set Variable [ $counter2; Value:1 ] Loop
Set Variable [ $firstChar; Value:Middle ( $alphaNums; $counter2; 1 ) ]
Set Variable [ $counter3; Value:1 ]
Set Variable [ $myCode; Value:$firstChar & Middle ( $alphaNums; $counter3; 1 ) ]
Set Field [ myTable::myCode; $myCode ]
Exit Loop If [ $counter3 = Length ( $alphaNums ) ]
Set Variable [ $counter3; Value:$counter3 + 1 ]
Exit Loop If [ $counter2 = Length ( $alphaNums ) ]
Set Variable [ $counter2; Value:$counter2 + 1 ]
# produce records with these values: aa-a9, ba-b9, etc.
# could be set to variable(s) rather than a field
Just to show you what I meant by loops