unique alphanumeric field

[pfrings]

Another query from a novice that I'm sure is simple to someone out there, but I can't work it out...

I want to create an alphanumeric ID field of the form AAA000, to have a unique ID field for a company. A user would have to enter 3 letters then 3 numbers, taking the first 3 letters of a company's name, then putting the lowest 3 digit number afterwards. For example:

Thamesdown Printing would be entered as THA001.

If a user then wanted to create a record for a company called Thamesdown Marketing, they need to enter THA002.

If they try to enter THA001 for this second company, I need Filemaker to refuse to accept it. Once a unique alphanumeric has been entered, I need the field to be locked.

Ideally, the first three letters have to be in capitals, but I can probably work out how to do this using the 'Upper' text function, if someone can show me how to do the other stuff...

Any advice gratefully received!

[Raybaudi]

Hi

give a try to this.

AlphanumericSerial.zip

[pfrings]

Wow, amazed by the fast response, thanks. Have never played with the Let function, grateful to be pointed in the direction of a possible solution!

However the calculation you've done doesn't work if you have a company name of more than one word (e.g. entering 'Apple Print' generates an id of APP001 rather than APP003). I'm not trying to generate an alphanumeric ID automatically – in practice its better if the user does this (eg if you have a company name such as A.G.I, I'd rather have this abbreviated to AGI, rather than A.G, also users can decide when to ignore a 'The' in a company name etc.). So what I'm trying to achieve is just a check that the alphanumeric code the user has entered conforms to the three letters/three numbers format.

In anticipation of more assistance...

Thanks again

[Raybaudi]

ok

so your Id must be validated by calculation:

Length ( Filter ( Left ( id ; 3 ) ; "ABCDEFGHJKILMNOPQRSTUVWXYZ" ) ) = 3 and Length ( Filter (Right ( id ; 3 ) ; "1234567890" ) ) = 3

Also check the "unique value" and set the "maximum number of characters" to 6

[pfrings]

Thanks Daniele, you're a star.

[Raybaudi]

Once a unique alphanumeric has been entered, I need the field to be locked.

this will lock your field

idLocker.zip

[pfrings]

Thanks, Daniele, I think this is a more elegant solution that what I came up with... Looking through older posts I saw the idea of making the ID field a button, and then using a script attached to the button. The script belows works...

If [not IsEmpty (ID Field)]

Show Custom dialog ["You can't change this code..."]

Exit Script []

Else

Go to Field [iD Field]

End If

... but I don't know whether it's more efficient/better to do it your way with two fields or this way with a script.

[Raybaudi]

Hi

that script is good too, but you have to exclude that field from the TAB order, otherwise someone can enter the field by tabbing !

(also, if someone change view from "Form" to "Table", that button no longer will work and the field can be modified)

P.S. The exit script step is useless