Working with Calculated Field Issue

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
epano
Posts: 19
Joined: 2020-07-09 11:56

Working with Calculated Field Issue

Post by epano » 2020-08-03 11:53

Hello,

I have build a contract management database with appgini and i have an issue on receiving automatic email notifications for contacts that are near expiration date. What i have done:
So I have created a calculated field that is called alert and it is calculated with an if statement depending of the result of the diff between the finish date and the Today date. Than i have used hooks before and after update to send email notification when the field alert is changed.

Code: Select all

      function Kontratat_before_update(&$data, $memberInfo, &$args) {
		
		$id_kontrata = makeSafe($data['selectedID']);
		
		$res = sql("select * from kontratat where id_kontrata='{$id_kontrata}'", $eo);
		
		$GLOBALS['old_data'] = db_fetch_assoc($res);
		
		return TRUE;
	}
	function Kontratat_after_update($data, $memberInfo, &$args) {
		
		
		$id_kontrata = makeSafe($data['selectedID']);	
		$data['alert'] = sqlValue("SELECT IF(status!='Ongoing','',
		IF(DATEDIFF(finish_date,NOW()) > 60,'',
		IF(DATEDIFF(finish_date, NOW()) > 30,'2 months',
		IF(DATEDIFF(finish_date, NOW()) > 0,'1 month','Expired'))))
			FROM Kontratat WHERE id_kontrata = '{$id_kontrata}'");
									
		if($data['alert'] != $GLOBALS['old_data']['alert'])
	
	         {
				sendmail(array(
				'to' => '[email protected]',
				'name' => 'Contract Alert',				
				'subject' => 'Alert Kontrata nr. ' . $data['id_kontrata'].' - '.$data['objekti_kontrata'],			
							
				'message' =>'old data'. $GLOBALS['old_data']['alert'].'new data'.$data['alert']
				));		
		}
		return TRUE;
	}
If i change the value of the finish date manually by clicking the button save it works, i receive the email perfectly.
But it doesn't work automatically. When the date is about to expire i see that the field alert is updated on the background but i dont receive email notification. Please any tip what should i do?

epano
Posts: 19
Joined: 2020-07-09 11:56

Re: Working with Calculated Field Issue

Post by epano » 2020-08-04 09:33

What i want is to send email notification when a calculated field is changed. It works when the field is updated manually in detail view by submitting the save button.
However the field is changed on the background by reloading the page in table view, i dont receive an email notification.
So does the hook after update works with calculated field? I have spent a lot of time in resolving these issue and now dont have any idea. Please any solution if someone can help!
Best regards!

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: Working with Calculated Field Issue

Post by a.gneady » 2020-08-04 09:48

Your guess is right. Calculated fields don't get updated at the same time as the rest of the record currently (till AppGini 5.84) so they don't get detected in after_update hook. We plan to fix this in future releases of AppGini. As a temporary work-around, you could add this code into the after_update hook before checking if the calculated field is changed:

Code: Select all

$formulas = calculated_fields();
update_calc_fields('tablename', $id, $formulas['tablename']);
(where tablename is the name of the concerned table)
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Working with Calculated Field Issue

Post by jsetzer » 2020-08-04 09:56

Updating calculated fields in TV does NOT execute TABLENAME_after_update() function in hooks/TABLENAME.php, if I remember right.

You should consider...
  • doing calculations and sending emails in TABLENAME_init() hook (the old fashioned, but bulletproof way)
    and/or
  • configuring a cron-job which executes a serverside script for periodically updating records/sending emails
@Ahmed: Sorry, you were a couple of seconds faster than me ;)
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

epano
Posts: 19
Joined: 2020-07-09 11:56

Re: Working with Calculated Field Issue

Post by epano » 2020-08-04 11:06

Thank you a.gneady but still doesnt work. I used the code below on after_update hook:

Code: Select all

$formulas = calculated_fields();
update_calc_fields('kontratat', $id_kontrata, $formulas['kontratat']);
What can i do else?
Thanks

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: Working with Calculated Field Issue

Post by a.gneady » 2020-08-04 12:49

epano wrote:
2020-08-04 11:06
Thank you a.gneady but still doesnt work. I used the code below on after_update hook:
Hmm ... Please post the entire code of the before_update and after_update hooks.
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

epano
Posts: 19
Joined: 2020-07-09 11:56

Re: Working with Calculated Field Issue

Post by epano » 2020-08-04 13:10

Code: Select all

function Kontratat_before_update(&$data, $memberInfo, &$args) {
		
		$id_kontrata = makeSafe($data['selectedID']);
		
		$res = sql("select * from kontratat where id_kontrata='{$id_kontrata}'", $eo);
		
		$GLOBALS['old_data'] = db_fetch_assoc($res);
		
		return TRUE;
	}
	function Kontratat_after_update($data, $memberInfo, &$args) {
		
		
		$id_kontrata = makeSafe($data['selectedID']);	
		$data['alert'] = sqlValue("SELECT IF(status!='Ongoing','',
		IF(DATEDIFF(finish_date,NOW()) > 60,'',
		IF(DATEDIFF(finish_date, NOW()) > 30,'2 months',
		IF(DATEDIFF(finish_date, NOW()) > 0,'1 month','Expired'))))
			FROM Kontratat WHERE id_kontrata = '{$id_kontrata}'");
			
	        $formulas = calculated_fields();
                update_calc_fields('Kontratat', $id_kontrata, $formulas['Kontratat']);
									
		if($data['alert'] != $GLOBALS['old_data']['alert'])
	
	         {
				sendmail(array(
				'to' => '[email protected]',
				'name' => 'Contract Alert',				
				'subject' => 'Alert Kontrata nr. ' . $data['id_kontrata'].' - '.$data['objekti_kontrata'],			
							
				'message' =>'old data'. $GLOBALS['old_data']['alert'].'new data'.$data['alert']
				));		
		}
		return TRUE;
	}
	
I test it by changing the PC date...the values of alert field are changed but i dont receive email notification.

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: Working with Calculated Field Issue

Post by a.gneady » 2020-08-04 15:22

jsetzer wrote:
2020-08-04 09:56
@Ahmed: Sorry, you were a couple of seconds faster than me ;)
No worries. Thanks so much for your help. Always appreciated :)
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: Working with Calculated Field Issue

Post by a.gneady » 2020-08-04 15:32

@epano, if I understand correctly form your code, the 'alert' field is a calculated field. In this case, you don't need to repeat the calculation SQL in the after_update hook ... You should just call update_calc_fields() then check the field's new value:

Code: Select all

function Kontratat_after_update($data, $memberInfo, &$args) {
	$id_kontrata = makeSafe($data['selectedID']);	

	$formulas = calculated_fields();
	update_calc_fields('Kontratat', $id_kontrata, $formulas['Kontratat']);

	$alert = sqlValue("SELECT alert FROM Kontratat WHERE id_kontrata='$id_kontrata'");
								
	if($alert != $GLOBALS['old_data']['alert']) {
		sendmail(array(
			'to' => '[email protected]',
			'name' => 'Contract Alert',				
			'subject' => 'Alert Kontrata nr. ' . $data['id_kontrata'].' - '.$data['objekti_kontrata'],									
			'message' =>'old data '. $GLOBALS['old_data']['alert'].', new data '. $alert
		));		
	}

	return TRUE;
}
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

epano
Posts: 19
Joined: 2020-07-09 11:56

Re: Working with Calculated Field Issue

Post by epano » 2020-08-04 20:19

Im sorry but still i dont have result. In table view even though the calculated field is changed in the background, i dont receive an email notification. I dont know but it seems like the update does not respond with_after update hook; loading the page in table view doesnt affect it.

epano
Posts: 19
Joined: 2020-07-09 11:56

Re: Working with Calculated Field Issue

Post by epano » 2020-08-04 20:26

jsetzer wrote:
2020-08-04 09:56
Updating calculated fields in TV does NOT execute TABLENAME_after_update() function in hooks/TABLENAME.php, if I remember right.

You should consider...
  • doing calculations and sending emails in TABLENAME_init() hook (the old fashioned, but bulletproof way)
Is the code that i have used compatible with table_init hook? How can i use it?
Thank you very much all for supporting me :)

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: Working with Calculated Field Issue

Post by a.gneady » 2020-08-05 15:49

epano wrote:
2020-08-04 20:19
Im sorry but still i dont have result. In table view even though the calculated field is changed in the background, i dont receive an email notification. I dont know but it seems like the update does not respond with_after update hook; loading the page in table view doesnt affect it.
OK, that would need some debugging .. you could try outputting the data before and after update to a log file and inspecting it ... this code would do so:

Code: Select all

@file_put_contents('data.log', json_encode($data) . "\n", FILE_APPEND);
You should put the above line into before_update hook and after_update hook.

You should also add this one after the lines for updating calculated fields in after_update:

Code: Select all

@file_put_contents('data.log', "alert: $alert\n", FILE_APPEND);
After each record update, the data.log file would include 3 new lines: the first line is the record data before update, the second is data after update, and the third line is the new value of alert after updating calculated fields. Inspecting this would give a better idea of what's going on.
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

epano
Posts: 19
Joined: 2020-07-09 11:56

Re: Working with Calculated Field Issue

Post by epano » 2020-08-06 10:08

The output from 2 point of view:

1) I changed the date of server and after opening the table view "Kontratat" the field alert value changed from "2 months" to "1 month", BUT the data.log was empty and i didnt receive an email notification.

2) I entered in detail view by clicking a random contact, I changed the finish date manually and saved it. In these case the data.log included 3 lines where the field alert was changed after calculation. And i received the email notif perfectly.

* I think that rendering the page in TV isn't related and does not execute before or after_update hook.

Post Reply