email on status change

[utony]

Hello AG,

I am looking for some assistance.

I have a table called sector_book and data point called "status" there are two status, "pending review" and "closed".

I am looking for a code example to send an email to a list of emails when the status of sector book changes to "Pending Review" status.

I am also looking for code example to send an email to a list of emails when the status of sector book changes to "Closed" status.

I am assuming it is an if the status is Pending Review send email update to email1, email2, email3

and if status changes to "Closed" send email to email4, email5, email6.

But, I just don't know how to make that happen.

I understand where to post the update in hooks as I have added email alerts from hooks before, it is just the trigger from the data point now that is getting me hung up.

Any assistance would be appreciated.

[jsetzer]

There are different ways to do this. This is just one:

AppGini stores just one Status value. First task: on update, detect if that Status value has changed at all or if the user has just saved the record but kept Status.

A good place is TABLENAME_before_update hook.

$data will hold the new Status value (before being saved to database).

Additionally, fetch the current value from database, for example by using $data_current = getRecord('TABLENAME', YOUR_PK);

Compare $data['Status'] and $data_current['Status'] to see if Status has changed at all.

If it has been changed, conditionally compose and send your emails.

[utony]

jsetzer, would you be able to provide a code example of this function?

[jsetzer]

would you be able to provide a code example

[...] as I have added email alerts from hooks before [...]

Code: Select all

// file: hooks/YOURTABLENAME.php
function YOURTABLENAME_before_update(&$data, $memberInfo, &$args)
{
	$tn = 'zahlungen'; // your table name
	$cn = 'status_code'; // your status column name
	
	$pk_cn = getPKFieldName($tn);
	$pk = $data[$pk_cn];
	$data_current = getRecord($tn, $pk);

	if ($data[$cn] != $data_current[$cn]) {
		// YOUR CODE FOR SENDING EMAIL NOTIFICATIONS
                // If someone does not know how-to,
                // there are many forum posts about sending emails.
	}
	return TRUE;
}

[utony]

JS any thoughts or adjustments you can help me with? It doesn't work.

$tn = 'Close_Patrol'; // your table name
$cn = 'Report_Status'; // your status column name

$pk_cn = getPKFieldName($tn);
$pk = $data[$pk_cn];
$data_current = getRecord($tn, $pk);

if ($data[$cn] != $data_Closed[$cn]) {

sendmail([
'to' => 'email address here',
'cc' => '',
'subject' => 'Close Patrol Report Closed',
'message' => 'Hello, a close patrol report has just been closed. Log in to review this report.'

]);





} else {

if ($data[$cn] != $data_Pending[$cn]) {

sendmail([
'to' => 'email address here',
'cc' => '',
'subject' => 'Close Patrol Report Is Pending Review.',
'message' => 'Hello, a close patrol report status has changed. Log in to review this report.'



}

return TRUE;
}

[jsetzer]

if ($data[$cn] != $data_Closed[$cn]) {

You have introduced two new variables: $data_Pending and $data_Closed. Both are undefined.

Just reread my code example. The variable is named $data_current.

if ($data[$cn] != $data_current[$cn]) {
// YOUR CODE FOR SENDING EMAIL NOTIFICATIONS
}

PS: if you want us to take our time for helping you, please take the time to format the code in your post for better readability. [code]...[/code]

[utony]

Code: Select all

$tn = 'Close_Patrol'; // your table name
	$cn = 'Report_Status'; // your status column name
	
	$pk_cn = getPKFieldName($tn);
	$pk = $data[$pk_cn];
	$data_current = getRecord($tn, $pk);

	if ($data[$cn] != $data_current[$cn]) {

	sendmail([
			'to' => '',
			'cc' => '',
			'subject' => 'Close Patrol Report Closed',          
			'message' => 'Hello, a close patrol report has just been closed. Log in to review this report.'
		          
	]);

Still doesn't work. But thanks anyway for trying. Every time the row is updated it sends an email. This is not what I need. I needed an email only if the Report Status was changed from - open to Closed.

[jsetzer]

Code: Select all

if ($data[$cn] == 'B' && $data_current[$cn] == 'A') {
    // send mail here
)

[utony]

Thank you for continued assistance!!!! You are the awesome!

[zibrahim]

Hi Jan,
Just wondering if we can use

Code: Select all

$pk = $data['selectedID'];

instead of

Code: Select all

$pk = $data[$pk_cn];

appreciate your feedback. Thanks

[jsetzer]

Just wondering if we can use $data['selectedID'] instead of $data[$pk_cn]

You are completely right and/but I've seen it depends on the specific hook function, wether we have that array entry or not. I recommend var_dump-ing $data to see what exactly we have in $data variable.

I did some tests and this is what I got:

Given...

Code: Select all

$tn = 'TABLENAME';
$pk_cn = getPKFieldName($tn);

$pk_cn equals the primary key column name of a table...

• Before Insert
  Obviously, you will not find $data['selectedID'] nor $data[$pk_cn] here, because primary key is not set before insert.
• After insert
  You will find both, $data['selectedID'] AND $data[$pk_cn].
• Before update
  You will find $data['selectedID'] here, but not $data[$pk_cn], which, honestly speaking, I don't know why.
• After update
  You will find both, $data['selectedID'] AND $data[$pk_cn].
• Before delete
  There is no $data array but there is a $selectedID variable. We can use getRecord($tn, $selectedID) for example to fetch data of record which is meant to be deleted.
• After delete
  There is no $data array but there is a $selectedID variable. As the record has been deleted at this moment, we cannot use getRecord($tn, $selectedID) any longer, but we can use $selectedID for example for deleting or updating other tables, loosely coupled with $tn.

Summary

• Before Insert
  $data['selectedID'] NO
  $data[$pk_cn] NO
• After Insert
  $data['selectedID'] YES
  $data[$pk_cn] YES
• Before Update
  $data['selectedID'] YES
  $data[$pk_cn] NO
• After Update
  $data['selectedID'] YES
  $data[$pk_cn] YES
• Before Delete
  $data NO
• After Delete
  $data NO

This is what I got today. Can anyone verify, please.

---

Just wondering if we can use $data['selectedID'] instead of $data[$pk_cn]

This means you are completely right! in After Insert, Before Update and After Update we should use $data['selectedID'] instead of $data[$pk_cn].

For before_update I have learned something new today.