Create unique index data from other fields

[r10kbs]

Good Afternoon,

New to this forum and open office, I am trying to have the key index field on a table in base, auto fill using the first three characters from another field (Surname) followed by a unique identifier so it increases when first three letters aren't unique.

Example being Surname field containing "Brown" i would like it to auto fill with "BRO001" and if a second surname field was to be created containing "Brown" then unique index key would need to be "BRO002"

Is there a simple way to do this please?

[Mountaineer]

Simple?? No.

You need to (sub-)SELECT your existing index-column
with something like

Code: Select all

SELECT index FROM table WHERE  index LIKE NewIdPrefix ORDER BY index DESC  LIMIT 1

and get the Max value for your Prefix and extract the number from the string.
You need to add some IFNULL or CASE to start with 0 or 1 when the list was empty.
This value you CONCAT to your prefix and have to know
where to put it... (Field in your Form, because if this is actually your ID for the Database, you need the value before storing the row...). If it is only "some additional human-readable id" you may use an UPDATE statement.J