Field for autoentering alphabet characters

[bluearrow]

Hi,

I need a field that, upon creating a new record:

- Autocompletes with a combination of 3 characters

- The 3 characters are roman alphabet letters (A-Z). For instance ATN, VBR, DEX, etc.

- The combination must be unique for each record

- The combination must be consecutive (for instance AAA, AAB, AAC...BAA, BAB...BBA...CAA, CAB, etc)

- I need to be able to define in the initial function or calculation certain combinations that must be omitted (I am upgrading a database from 6 to 8.5 and some combinations are already present). I have a list of codes to omit (comma separated values) that I could include in the function.

The secuence must follow a strict assigment when a new record is created:

Record 1 (Field1 = AAA)

Record 2 (Field1 = AAB)

...

Record x (Field1 = AAZ)

...

Record y (Field1 = AZZ)

Record n+1 (Field1 = BAA)

Record n+2 (Field1 = BAB)

Record n+3 (Field1 = BAC)

...

Last record (Field1 = ZZZ)

Maybe a custom function could help, but I have not been able to create it.

Thanks in advance

[Søren Dyhr]

Thats pretty easy; hack this CF:

http://www.briandunning.com/cf/216

into this:

Lets call it Doit:

Let ( [

bit = Mod ( number ; 26 ) ;

alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ;

char = Middle ( alpha ; bit + 1 ; 1 ) ;

next = Div ( number ; 26 )

] ;



Case ( 

next ;

Doit( next )

)//end Case

& char

)//end Let

Now we need to have a leading 0 scheme as well so the CF is used this way:

Right("AA" & Doit ( entry );3)

--sd

[comment]

Or you could use this custom function with this call:

DecToAlpha ( SerialID + 701 )

Of course, you will be limited to a maximum of 17,576 records.

[bluearrow]

Thank you Soren Comment, for pointing me to this resources. I looked into Dunning's custom functions before posting my question to the list, but I overlooked the numerical ones (I looked into the text functions only )

Now, I have the automatically entered 3 code combination. However, my list of 3 characters to omit includes combinations inserted in between. For instance, I might start from:

AAA, AAB, AAC but AAD might be in the list of combinations to omit. Then, after AAC I would like next record to be AAE. This kind of omission could be located anywhere, and could range from a single code in between to dozens of consecutive codes.

The most practical approach would be to somehow "pass" this list to the function. I have this list and includes hundreds of codes that I cannot use. All codes between AAA and ZZZ. How can I include this omission list in the custom function?

Thanks again for your help.

[comment]

How can I include this omission list in the custom function?

Sorry, missed that part. You can't - at least not reliably. If you can't change the existing records to use the new scheme, I'd say make yourself a table of all the AVAILABLE codes (17,576 records minus the codes already taken), number them serially starting from the next serial number in your main table, and have each new record lookup its code by matching serial.

[Søren Dyhr]

Yes you hit a iteration-max or several if say utilizing some CF's, but the new List( introduced with fm8.5 can handle it in a unstored field showing the desired value, picked with the behind the screens serial number, and a global strainerfield containing all unwanted combinations:

GetValue ( List ( allPossible::theAaa2Zzz ) ;actualSerialNumber )

...where the global field is primary key for not equal relation to the prefab table, with all combinations. Although the rendering is not the fastest known to mankind!

--sd

[bluearrow]

Hi Sohren, Comment,

With the help of the custom functions, I have setup a database with all the codes I need, removing already assigned codes. I am not using the custom functions afterwards, as it seems there is no way to reliably omit certain values.

I have created the TOs, TOG and the script. Everything is working (a sequential 3 letter code is added to every new record), but I do not like the results.

My knowledge of 8.5 is very limited. I am a pre 7.0 user. I just cannot believe I have to write all those script lines for doing what I want to do. Besides, reading the script seems like a nightmare. All the naming (following FM conventions) looks confusing.

I am uploading 2 tables: one simple and one more complex. Really, both look too complex to me taking into account the problem I am trying to solve.

I would appreciate if you could tell me what is wrong (possibly everything ). A revised (simplified) file would help. Please take into account that:

- The reason I use a PREF table is to show the database Admin the current GCL code (GCL just mean GroupCode). I have to show it in a PREF table because this table shows several other codes, and not all comes from the same table (for instance, I have a Quote number to show, a Project Code, etc.)

- In the complex file I use a global table. I like to use to keep all my globals in a single table. This seems useful, although it looks like it complicates everything else, including scripts and TOG.

Thanks in advance

AutoCode_simple.zip

AutoCode_complex.zip

[comment]

I can't make any sense out of your file - and that's the one marked as "simple".

I had in mind something like the attached.

AssignCodes.fp7.zip

[bluearrow]

Thank you Comment,

Yes, my file is a mess, and your is wonderfully simple. I will try to adapt it to my needs. The files I uploaded basically do the same, but in a much more complicated way. If you go to Script and run the script named Code_GCL you will see that in layout Asign_CodeGCL, a new record is created and the new code is assigned.

Thank you for showing me the easy way through. Making my way to the new FileMaker is harder than I initially thought...