Page 1 of 1

which field was changed after record was updated in table

Posted: 2022-06-21 22:33
by ushay
Hello,

i wish to send by email only the content of the field that was changed in a record.
i know that code has to be placed in table_after_update, but i dont know how to detect which field was changed.

thank you.

shay.

Re: which field was changed after record was updated in table

Posted: 2022-06-22 07:28
by jsetzer
You cannot find out in TABLENAME_after_update-hook. But you can in TABLENAME_before_update-hook.

General idea
  • In TABLENAME_before_update-hook you can get the $selectedID of the record, being changed, like this:

    Code: Select all

    $tn = "TABLENAME";
    $selectedID = $data["selectedID"];
  • Then fetch the current version of the record from the database using $record = getRecord("TABLENAME", $selectedID)-function, for example
  • You now have $data variable, holding the new values, and $record variable, holding the database-stored values
  • Iterate through the fields using something like this:

    Code: Select all

    foreach ($data as $fieldname => $new_Value) {
        // ...
    }
    • compare $record[$fieldname] with $data[$fieldname]
    • If there are differences, that field has been changed in comparison to last-saved record
    • You may store the difference for example in a $differences-array
  • After finding all changed fields, build your email-body according to your needs and send the message to your users.
Notes
  • There may be more fields changed than expected, for example fields like "modified_on", "modified_by", being populated by AppGini automatically
  • There may be more fields in $record than in $data because $data will not contain readonly or hidden fields
Tips

For debugging purposes and for better understanding what is happening there, make use of dumping variables like this:

Code: Select all

var_dump("Data", $data);
exit;
Don't forget to remove those var_dump's and the exit;-command after successfully testing.

Sample Code

As a starting point for your own implementation.

Code: Select all

function calculations_before_update(&$data, $memberInfo, &$args)
{
	$tn = "calculations";
	$pkFieldNameForm = "selectedID";
	$pkFieldNameDatabase = getPKFieldName($tn);

	var_dump("Data", $data); // TODO: remove

	$selectedID = $data[$pkFieldNameForm] ?? $data[$pkFieldNameDatabase];
	var_dump("PK", $selectedID); // TODO: remove

	$record = getRecord($tn, $selectedID);
	var_dump("Record (database)", $record); // TODO: remove

	$differences = [];

	foreach ($data as $fieldname => $new_Value) {

                // skip comparison for field "selectedID"
		if ($fieldname == $pkFieldNameForm) continue;

		$database_value = $record[$fieldname];
		$form_value = $data[$fieldname];

		// compare $data[$fieldname] with $record[$fieldname]
		if ($form_value != $database_value) {
		        // if different, store difference in $differences array
			$differences[$fieldname] = [
				"before" => $database_value,
				"after" => $form_value
			];
		}
	}
	var_dump("Differences", $differences); // TODO: remove
	exit; // TODO: remove

	// if there are differences, build email and send
	// TODO: implement

	return TRUE;
}
Example

Changing field description in detail view:

chrome_QgHT8h2PHG.png
chrome_QgHT8h2PHG.png (66.45 KiB) Viewed 1166 times

Output of all those var_dump's

chrome_le2p6dVujZ.png
chrome_le2p6dVujZ.png (69.15 KiB) Viewed 1166 times

Hope this helps.

Re: which field was changed after record was updated in table

Posted: 2022-06-22 10:15
by ushay
dear jsetzer,

thank you very much for kind suppot.

please forgive my ignorance but i still didnt under which value i should send on the Email .

from the above code, i wish to send the user the name of the change field, which value should i send?

for example, this how send the mail:



$userEmail=sqlValue("select email from membership_users where memberID='".getLoggedMemberID()."'");

$mail = [
"to" => "$userEmail",
"name" => "".getLoggedMemberID(),
"message" => "שינוי התבצע על ידי ".getLoggedMemberID().
".\n\n".
"changes where made in field"."[b][i][u]this is where i dont know which value to send[/u][/i][/b]"."\n".
"To view it, please go to:\n".
"http://tasks.qlc.co.il:10501/tasks/Technicians_view.php?SelectedID=".$data['selectedID']."",
"subject" => "Test-subject (1)",
"debug" => 4
];
sendmail($mail);

Re: which field was changed after record was updated in table

Posted: 2022-06-22 10:50
by jsetzer

Code: Select all

// ...

if (sizeof($differences)>0) {
  $differences_fieldnames_as_string = implode(", ", array_keys($differences));
  $email_body = "changes where made in field(s) " . $differences_fieldnames_as_string . "\n";
  var_dump($email_body);
  exit;
}
chrome_qGD21YnsqA.png
chrome_qGD21YnsqA.png (4.87 KiB) Viewed 1148 times

Please note:
there may be more than one changed field. $differences is an array of 0..n changed fields. In the example above, there are two changes, one by manually changing "description" field, the second due to automatically set modification date. I'm concatenating the fieldnames with "," by using PHP's implode()-function. This converts the array of changed fieldnames into a string which can betaken for building your Email body.

Re: which field was changed after record was updated in table

Posted: 2022-06-22 11:46
by ushay
dear jsetzer,

thank you very much, it works well.

you are indeed a Super Hero.

Re: which field was changed after record was updated in table

Posted: 2022-06-22 12:52
by jsetzer
Thanks, shay!