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?
Create unique index data from other fields
Create unique index data from other fields
Last edited by MrProgrammer on Mon Apr 04, 2022 4:19 pm, edited 1 time in total.
Reason: Moved topic from Beginners forum to Base
Reason: Moved topic from Beginners forum to Base
OpenOffice 4.1.10 on Windows 10
-
- Posts: 318
- Joined: Sun Sep 06, 2020 8:27 am
Re: Create unique index data from other fields
Simple?? No.
You need to (sub-)SELECT your existing index-column
with something like 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
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
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
OpenOffice 3.1 on Windows Vista