UPDATE

[calvin3pl]

Hi,

I'm working with version 23.15.1484. I have the below in the calculated field. I would like to have the 'ArticleDocument' number that matches the 'Reference' number returned in the 'CKG" field. It's not working, any help is appreciated. See attachment for example.

UPDATE `MB51_NOS` t1
JOIN `MB51_NOS` t2 ON t1.`Reference` = t2.`ArticleDocument`
SET t1.`CKG` = t2.`ArticleDocument`
WHERE t1.`Reference` IS NOT NULL AND t1.`Reference` != '';

Thank you.

[calvin3pl]

Hi, looks like I had to use 'SELECT', to prevent the php errors and allow other queries to run, see below.

SELECT t1.`ArticleDocument`, t1.`Reference`, t2.`ArticleDocument` AS 'CKG_to_be_set'
FROM `MB51_NOS` t1
LEFT JOIN `MB51_NOS` t2 ON t1.`Reference` = t2.`ArticleDocument`
WHERE t1.`Reference` IS NOT NULL AND t1.`Reference` != '';

The issue I'm getting now are duplicates, see below. Any help is appreciated

AppGini1.png (216.64 KiB) Viewed 3392 times

[jsetzer]

The SQL command for calculated field should update exactly one row. You have to append one more condition to your WHERE part:

Code: Select all

AND TABLENAME.PKCOLUMNNAME = '%ID%'

Replace tablename and pkcolumnname.

%ID% will be replaced by AppGini automatically at runtime.

Additionally, if your SELECT returns more than one value, the field will be updated with the first value. So, there is no need to add more than one field.

Having said this, instead of using calculated fields consider changing the TABLENAME_after_update hook (and perhaps after_insert) and do all necessary database updates in PHP. There you are free to update whatever you want, even multiple fields at once and even for the whole table or parts of a table.

[calvin3pl]

Thank you, Jsetzer! An AppGini Super Hero you are! The below worked perfect and I'll look at the hook recommendation.

SELECT t1.`ArticleDocument`, t1.`Reference`, t2.`ArticleDocument` AS 'CKG_to_be_set'
FROM `MB51_NOS` t1
LEFT JOIN `MB51_NOS` t2 ON t1.`Reference` = t2.`ArticleDocument`
WHERE t1.`Reference` IS NOT NULL
AND t1.`Reference` != ''
AND t1.MB51_NOS_Key = '%ID%';

Have a great day!