Custom Primary Keys

[mwilliam]

I have two tables.
Accidents and Cases
Primary key accident_code
Primary Key case_code

When a Primary key is auto assigned for both. How can I make the format to be:

2020C00000 (2020 is the year)(the C is for cases)00000 is auto incremented

2020A00000 (2020 is the year)(the A is for cases)00000 is auto incremented

Last, but prob the hardest to do.

When the year 2021 comes, how can I get the 00000 to start back at zero. So when 2021 rolls over, both we start back at 0 and do this:

2021C00000 (2021 is the year)(the C is for cases)00000 is auto incremented
2020A00000 (2021 is the year)(the A is for cases)00000 is auto incremented

Or maybe a button (hook) (????) to make this year rollover reset the auto incremented to 0 by ADMIN only.

Please any help would be appreciated.

[hgarbe]

Hi,

perhaps not the best way but maybe a starting point. Use a calculated field in addition to the record ID. Leave the ID a Auto Increment number with your desired Length and Zero Fill and use a sql query to generate your individual number in the calculated field including the ID and prepending the year and the C/A letter.

[tchandra]

Not possible because calculated field on a Primary Key is not allowed

[jsetzer]

... in addition to the record ID...

[jsetzer]

I would leave the ID column as is, then create one more field as a counter ("number"), one more unique field as "code" and a field for creation date (which usually exists in my tables anyway)

In TABLENAME_before_insert you can query the maximum of "number" for the current year + 1 and customize "code" value as you like.

Additionally, having separate columns for year, number and code enables you to update your codes even afterwards.

I have used this concept several times and it was quite flexible when customers asked me to change their naming pattern later. For example if they wanted to have a different prefix or suffix based on a type column or so.

[onoehring]

Hi,
had to add my two cents.
I totally agree with the above and strongly recommend using a purely automatic (autoincrement) primary key which has absolutely no other meaning than identifying a record on database level.
If you want to have some identifier as you describe, I recommend - following Jan - you use other fields for that.

Olaf

[mwilliam]

I was going to use the primary number as a CODE that is used to put into another system. So that CODE has alot of meaning, the format is very important.

[onoehring]

Hi,

if the PK for AG comes from another application, you could still use an autoincrement field. Just set an index (unique, can be done right in field definitions in AG) to the field which holds your code.

Olaf

[pbottcher]

Hi,

you can try

Code: Select all

select distinct concat(YEAR(CURDATE()),'A',(SELECT lpad(count(1),5,0) FROM `Accidents` where text like concat('%',YEAR(CURDATE()),'%'))) from `Accidents` limit 1

This shall return the required key fro the Accidents

[mwilliam]

Hi,

you can try

Code: Select all

select distinct concat(YEAR(CURDATE()),'A',(SELECT lpad(count(1),5,0) FROM `Accidents` where text like concat('%',YEAR(CURDATE()),'%'))) from `Accidents` limit 1

This shall return the required key fro the Accidents

Where would I put this code at? In accident hook ?

[pbottcher]

Hi,

yes, put the code into the Accidents_before_insert hook.

But use this statement as the other one will not work if you delete records.

Code: Select all

$data['YOURPRIMARYKEY']=sqlvalue("select distinct concat(YEAR(CURDATE()),'A',(SELECT lpad(max(substr(myid,6,5))+1,5,0) FROM `Accidents` where myid like concat('%',YEAR(CURDATE()),'%'))) from dual limit 1");

Same would apply for the Cases table with the relevant adjustments