CONCAT Columns After Save New Record?

[pmjd]

Hi there,

I'm looking for some help on using the CONCAT function.

I have a table that generates an autonumber onsave. Using the After Save feature I want to use CONCAT to Add a text prefix to the autonumber, so it displays as "PF1001" and saves that to another field. I've tried to set this so it only applies where it hasn't been done before and the field that will have the concatenated name is null.

I've tried the following code but it doesn't appear to work.

Code: Select all

UPDATE sln_prepared_info
SET sln_prepared_solutionnumber=CONCAT("SL"+sln_prepared_autonumber)
WHERE sln_prepared_solution_number=null

Can anyone suggest where I could be going wrong?

Thanks,
Paul

[kev1n]

Hi,

Replace

Code: Select all

= null

with

Code: Select all

is null

[pmjd]

Thanks kev1n but unfotruantely getting this error

C:\xampp\htdocs\lotgen\core\nucommon.php(1277) : eval()'d code
syntax error, unexpected 'sln_prepared_info' (T_STRING)

Traced from...

(line:66) C:\xampp\htdocs\lotgen\core\nuapi.php - nuUpdateDatabase

(line:427) C:\xampp\htdocs\lotgen\core\nudata.php - nuEval

So something is still not quite right with the code.

[kev1n]

From where are you running the code? Can you post your entire code?
I thought you'd run in from phpMyAdmin.

[apmuthu]

Replace

Code: Select all

WHERE sln_prepared_solution_number=null

with

Code: Select all

WHERE ISNULL(sln_prepared_solution_number)

[pmjd]

Hello,

The code is as you see it, it's in the After Save section of the Fast Form.

I thought I'd need to put the code in the After Save section of the Fast Form because the Autonumber won't exist until after saving, so there would be nothing to concatenate.

So looks like I'm using the wrong type of code in the wrong place? Sorry just starting out, don't know how to use php and sql yet.

If I can put this in phpMyadmin, where does it go and how is it triggered when the record is added? Or am I better to use some form of php code in the After Save section in the Fast Form?

Thanks,
Paul

[kev1n]

The After Save event expects a PHP code. So you'd have to run your SQL query with nuRunQuery()

Replace sln_prepared_info_id with the primary key of your table if it is different.

Code: Select all

$sql = "
	UPDATE sln_prepared_info
	SET sln_prepared_solutionnumber = CONCAT('SL',sln_prepared_autonumber)
	WHERE sln_prepared_solution_number IS NULL AND sln_prepared_info_id = ?
";

nuRunQuery($sql, ["#RECORD_ID#"]);

[pmjd]

Brilliant, thank you kev1n

[pmjd]

The solution works great but I was wondering if there is a way to add leading zeroes to the number that is concatenated?

So instead of SL + 10 = SL10, I would get SL + 10 = SL0010 in the field?

I've tried using the sprint php function within the code but it hasn't been working, probably my lack of knowledge.

[kev1n]

You could give this a try:

Instead of

Code: Select all

CONCAT('SL',sln_prepared_autonumber)

Write

Code: Select all

LPAD(CONCAT('SL',sln_prepared_autonumber),4,'0')