Page 2 of 2
Re: CONCAT Columns After Save New Record?
Posted: Wed Mar 24, 2021 5:50 pm
by pmjd
Rearranged it to
Code: Select all
CONCAT('SL',LPAD(sln_prepared_autonumber,4,'0')
Which is now working, thanks for pointing me in the right direction.
Re: CONCAT Columns After Save New Record?
Posted: Tue Mar 30, 2021 5:41 am
by pmjd
LPAD worked
Now needing to extend it a bit further...
I have a nuDate field that I need to convert to ddmmyy and include within the Concat function. The nuDate field is on the same form as the other data.
I tried within the concat
and also
Code: Select all
(date_format(nuDate_field,'%d%m%y')
but nuBuilder showed a blank field.
Any suggestions?
Re: CONCAT Columns After Save New Record?
Posted: Tue Mar 30, 2021 8:38 am
by kev1n
It looks to me there's a parenthesis too much. How/where do you run that SQL?
Re: CONCAT Columns After Save New Record?
Posted: Tue Mar 30, 2021 8:55 am
by pmjd
In After Save
The full code looks like this
Code: Select all
$keysql = "
UPDATE prd_key_mbn
SET prd_key_mbn_number = CONCAT('KEY',DATE_FORMAT(prd_key_date,%d%m%y),LPAD(prd_key_mbn_auto,4,'0'))
WHERE prd_key_mbn_number IS NULL AND prd_key_mbn_id = ?
";
nuRunQuery($keysql, ["#RECORD_ID#"]);
Was using W3Schools sql as a reference.
Re: CONCAT Columns After Save New Record?
Posted: Tue Mar 30, 2021 9:06 am
by kev1n
Wrap %d%m%y in single quotes:
'%d%m%y'
Re: CONCAT Columns After Save New Record?
Posted: Tue Mar 30, 2021 9:10 am
by kev1n
BTW, it's easy to track SQL errors, if you run the query in phpMyAdmin or any other DB tool.
Code: Select all
SELECT CONCAT('KEY',DATE_FORMAT(prd_key_date,%d%m%y),LPAD(prd_key_mbn_auto,4,'0'))
Re: CONCAT Columns After Save New Record?
Posted: Tue Mar 30, 2021 9:18 am
by pmjd
Same result with single quotes unfortunately.
Didn't know about the SQL errors tracking, will try the query in phpMyAdmin and see if it gives any hints
Re: CONCAT Columns After Save New Record?
Posted: Tue Mar 30, 2021 9:22 am
by kev1n
Also check nuDebug Results (CTRL+SHIFT+D) for errors
Re: CONCAT Columns After Save New Record?
Posted: Wed Mar 31, 2021 7:03 pm
by apmuthu