CONCAT Columns After Save New Record?

[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.

[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

Code: Select all

(date_format(nuDate_field,'dmy')

and also

Code: Select all

(date_format(nuDate_field,'%d%m%y')

but nuBuilder showed a blank field.

Any suggestions?

[kev1n]

It looks to me there's a parenthesis too much. How/where do you run that SQL?

[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.

[kev1n]

Wrap %d%m%y in single quotes:

'%d%m%y'

[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'))

[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

[kev1n]

Also check nuDebug Results (CTRL+SHIFT+D) for errors

[apmuthu]

Wiki-ed.