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.
Custom Primary Keys
Re: Custom Primary Keys
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.
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.
Re: Custom Primary Keys
Not possible because calculated field on a Primary Key is not allowed
Re: Custom Primary Keys
... in addition to the record ID...
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Custom Primary Keys
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.
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.
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Custom Primary Keys
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
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
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: Custom Primary Keys
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.
Re: Custom Primary Keys
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
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
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: Custom Primary Keys
Hi,
you can try
This shall return the required key fro the Accidents
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
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Custom Primary Keys
Where would I put this code at? In accident hook ?pböttcher wrote: ↑2020-09-26 09:32Hi,
you can try
This shall return the required key fro the AccidentsCode: 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
Re: Custom Primary Keys
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.
Same would apply for the Cases table with the relevant adjustments
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");
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.