Page 1 of 1
AUTOMATIC CODE
Posted: 2021-05-03 01:17
by Moh Youba
Hello
I am using AG 5.94.
I have a field called "code client", I want to generate incremented automatic code like this "AC/001/21".
- "AC" is initial of company name
- "001" is numer of client
- "21" is year
Any help please.
Re: AUTOMATIC CODE
Posted: 2021-05-03 16:41
by onoehring
Hi,
well, probably we need more information like what are your field names and considerations of this:
If AC is the initial, this is saved as an external field - ok, no problem.
001 .. is 002 next? What if you have 5 clients and suddenly client 3 drops out? What if you have more than 999 clients for a company?
21 is easy to take from the system year... what if the customer is "old" or became a customer right before new year (so in the last year)?
In every case, I want to warn here to use calculated fields as identifiers. You might want to consider using your suggested values as "hints" (speaking identifier), but a real, non-speaking unique identifier (primary key, autoincrement).
Olaf
Re: AUTOMATIC CODE
Posted: 2021-05-03 17:12
by pfrumkin
Hi,
I had a similar need. I created a company table with a year column and a number (of entries) column. If there is an entry for the company/year, get that number, add one for this entry, then increment that number counter. If no entry, use 1 and create the record for that company/year, number = 1.
I also built a little padding utility to insert the leading 0's in the string (two if < 10, one if < 100).
Olaf asked excellent questions you need to answer for yourself - what if you remove an entry in the middle, and what if you get > 999.
Good luck.
~Paul
Re: AUTOMATIC CODE
Posted: 2021-05-04 00:25
by Moh Youba
Hi guys
Thank you to all for your support. in my case, I have another field called status, no remove o entry, if entry is no longer in use, it will be mark as expired
Regards
Re: AUTOMATIC CODE
Posted: 2021-05-04 10:15
by onoehring
Hi,
just to point you into some direction ..
Please note, that saving calculated values is not a good idea.
Still .. code not tested - of course.
1. Define your divider (to make later changes easier)
2. use a sql statement to count how many clients your company already has
Code: Select all
$clientnumber = sqlValue("select count (primaryKey) from your_client_tablename were companyFieldInitial ='" . $data('initial') . "'";
$clientnumber++;
$clientnumber = str_pad($clientnumber, 3, '0', STR_PAD_LEFT); // add padding 0
3. concat everything into your targetstring
Code: Select all
$targetstring .= $data('initial') . $divider . $clientnumber . $divider . date("y") ;
Olaf
Re: AUTOMATIC CODE
Posted: 2021-05-04 16:06
by Moh Youba
Hi
yep ! I think this is a good starting point for me, let check and back to you.
Thank you