Best way to create auto number/year (invoice no, order no...)

[Andrea]

Hello,

I'm looking for a possibility to create a field for an auto number per year: ###/YEAR
Something like this:

...
982/2021
983/2021
1/2022
2/2022
3/2022
...

Would be great if anyone has a proposal how to solve this in nubuilder the best way.
Many thanks and a happy new year to all.

Best wishes

Andrea

[kev1n]

Hi Andrea,

I can think of different ways to accomplish that. Here is just one possibility by using the PHP BE (Before Edit) event to retrieve the latest invoice nr of the current year, increment it by 1 and add a "/" and the current year.

Code: Select all

$q = "
	SELECT CONCAT(IFNULL(MAX(SUBSTRING_INDEX( fac_invoice_nr, '/', 1 )),0) + 1,'/', Year(Now())) as x
	FROM   factura
	WHERE  Year(`fac_created`) = Year(Now())
";

$t = nuRunQuery($q);
$r  = db_fetch_row($t);	
nuSetNuDataValue($nudata, nuHash()['nuFORMdata'][0]->id, 'fac_invoice_nr', $r[0]);

Table schema:

table schema.jpg

If anything is unclear or you have questions, please do not hesitate to ask

[Andrea]

Many thanks for the fast reply and helpful proposal!
I'll try it.
Thank you &
Best wishes

Andrea

[Andrea]

I needed to put the code in "before save". There was an error if I put it in "before edit".
But wow, it works! Great!

I made 2 changes:

I put a check for if it is a new record around it ( if(nuHasNoRecordID) )
and as I did not have a column 'fac_created' I got the year from substring of the column 'fac_invoice_nr'.



if(nuHasNoRecordID()){

$q = "
SELECT CONCAT(IFNULL(MAX(SUBSTRING_INDEX( fac_invoice_nr, '/', 1 )),0) + 1,'/', Year(Now())) as x
FROM factura
WHERE SUBSTRING_INDEX( fac_invoice_nr, '/', -1 ) = Year(Now())
";

$t = nuRunQuery($q);
$r = db_fetch_row($t);
nuSetNuDataValue($nudata, nuHash()['nuFORMdata'][0]->id, 'fac_invoice_nr', $r[0]);

}


Thanks a lot!
Best wishes

Andrea

[Andrea]

It stopped working when reaching 9/2022 and repeated 10/2022 as the substring left of '/' obviously was not really recognized as a number.
So I added *1 to fix it:



if(nuHasNoRecordID()){

$q = "
SELECT CONCAT(IFNULL(MAX(SUBSTRING_INDEX( fac_invoice_nr, '/', 1 )*1),0) + 1,'/', Year(Now())) as x
FROM factura
WHERE SUBSTRING_INDEX( fac_invoice_nr, '/', -1 ) = Year(Now())
";

$t = nuRunQuery($q);
$r = db_fetch_row($t);
nuSetNuDataValue($nudata, nuHash()['nuFORMdata'][0]->id, 'fac_invoice_nr', $r[0]);

}

I'm happy

[kev1n]

Good job

[Andrea]

Thankyou