Calculated fields before_insert hook

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

Calculated fields before_insert hook

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

Hello,

I have a question about calculated fields on before or after insert. I have a calculated field that is called "difference" in "alokim" table. Also I have a file alokim-dv.js which contains java script code for checking the field(difference) before insertions if it meets a condition:

Code: Select all

function show_error(field, msg){
	modal_window({
		message: '<div class="alert alert-danger">' + msg + '</div>',
		title: 'Error in ' + field,
		close: function(){
			$j('#' + field).focus();
			$j('#' + field).parents('.form-group').addClass('has-error');
		}
	});
	
	return false;
}
$j(function(){
	
	
	$j('#insert').click(function(){
		var difference= $j('#difference').val();
		
		if(difference< 1){
			return show_error('Alokim', 'Nuk ka gjendje per kategorine e caktuar' );
		}
	});
	
})	
The problem is that the difference field is not calculated on insertion. I used the code below in before_insert hook but the value is stored after insertion on background.

Code: Select all

function alokim_before_insert(&$data, $memberInfo, &$args) {
		
		$id_alokim = makeSafe($data['selectedID']);
		$formulas = calculated_fields();
                update_calc_fields('alokimi', $id_alokim, $formulas['alokimi']);
		
		return TRUE;
	}


Please any suggestion?
Ervis

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

Re: Calculated fields before_insert hook

Post by a.gneady » 2020-08-20 13:44

The calculated field value is updated after the detail view form is submitted then the record reloaded. The check in your code above takes place on clicking the 'Save changes' button, before submitting the form:

Code: Select all

$j('#insert').click(function(){
The fix to do the validation correctly is, instead of checking the value of the 'difference' field, to manually make the calculation in JS to calculate the difference then check if it's less than 1.
: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: Calculated fields before_insert hook

Post by epano » 2020-08-20 14:09

The difference field in alokim table is calculated by appgini with SQL query:

Code: Select all

SELECT `toner_contract`.`difference` 
FROM 
`toner_contract` LEFT JOIN 
`alokim` ON `alokim`.`toner`=`toner_contract`.`id` 
WHERE `alokim`.`id_alokim`='%ID%'
Can you help me how can i calculate it in js, because i am not too familiar in programming :(

Thank you very much in advance Ahmad

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

Re: Calculated fields before_insert hook

Post by epano » 2020-08-31 22:47

Hello everyone. I have created ajax_alokim.php file:

Code: Select all

<?php
	$currDir = dirname(__FILE__) . '/..';
	include("$currDir/defaultLang.php");
	include("$currDir/language.php");
	include("$currDir/lib.php");

	
	$table1 = get_sql_from('alokim');
	if(!$table1){
		header('HTTP/1.0 401 Unauthorized');
		exit;
	}
	
	$table2 = get_sql_from('toner_kontratat');
	if(!$table2){
		header('HTTP/1.0 401 Unauthorized');
		exit;
	}

	$id_alokim = makesafe($_REQUEST['id_alokim']);
	

	$sql = "SELECT `toner_kontratat`.`diferenca` FROM `toner_kontratat` LEFT JOIN `alokim` ON `alokim`.`toner`=`toner_kontratat`.`id` WHERE `alokim`.`id_alokim`= '{$id_alokim}'";

	$result = sqlValue($sql);

	echo $result;
and also alokim-dv.js:

Code: Select all

$j(function(){
	
	$j('#insert').click(function(){
	
		update_diferenca();	
			
		var diferenca = $j('#diferenca').val();
		if(diferenca < 1){
			return show_error('Gjendja Toner', 'Nuk ka gjendje per kategorine e caktuar' );
		}
	});
		
})
	
function update_diferenca(){	

   $j('#id_alokim-container').on('change', function(){	
	  var pid = $j('#id_alokim').val();
		
	    $j.ajax({
				url: 'hooks/ajax_alokim.php',
				data: { pid: pid },
				success: function(data){
					$j('#diferenca').val(data);
		        }
		
	  });
   }
}
Pls can someone tell me if these is the right way? And if so what am i doing wrong? Thnx

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Calculated fields before_insert hook

Post by pbottcher » 2020-09-01 10:36

Hi,

in you ajax script call you use

Code: Select all

$id_alokim = makesafe($_REQUEST['id_alokim']);
but in your call you use

Code: Select all

data: { pid: pid }
So $_REQUEST['id_alokim'] is not set.
Try

Code: Select all

data: { id_alokim: pid }
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

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

Re: Calculated fields before_insert hook

Post by epano » 2020-09-01 22:09

Thank you, i tried but it doesnt work :(

What i want is to make a validation before insert on a field that actually is a calculated field. Is these the right way?

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Calculated fields before_insert hook

Post by pbottcher » 2020-09-02 08:30

Hi,

not sure I get now what you try to do. A calculated field is calculated, so it does not matter what you enter on that field. Also I think that field is read only anyway.

Maybe you can make an example of what you need.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

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

Re: Calculated fields before_insert hook

Post by epano » 2020-09-02 10:12

For example i have a table called "alokim". It has a calculated field called "diferenca" that represents the quantity left of a product ( ex like stock quantity). I want to make a validation rule when someone insert a new record.. On clicking the button save new record i want to make the calculation of the field before and prevent users of adding a new record if it meets a condition ( smaller then 1).

The problem is that the calculated field value is updated after the detail view form is submitted. So the fix to do the validation correctly is, instead of checking the value of the 'difference' field, to manually make the calculation in JS to calculate the difference then check if it's less than 1.

I dont know very well if what im doing on the previous message is right so what do u suggest? Thank you very much helping :)

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Calculated fields before_insert hook

Post by pbottcher » 2020-09-02 16:36

Hi,

if I get it right, you have the dv with a field diferenca which is a calculated field.

Next you have an input field alokim (?) which the user will input the amount.

So you could just add a max attribute to the alokim field.

var max=parseInt($j('#diferenca').text())-1;
$j('#alokim').attr('max',max);
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

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

Re: Calculated fields before_insert hook

Post by epano » 2020-09-03 00:17

I am sorry because i haven't explain clearly what i need. In a simpler way Attached find the table that I need to look:
Table Preview.png
Table Preview.png (18.54 KiB) Viewed 3346 times
Every record inserted in allocated table is calculated as count for the specific toner consumed Also it is calculated the stock by difference of quantity - consumed. What i need to do is to prevent insertion when the stock value is less than 1 and to show an error message on allocation DV.

Ive created ajax_toner.php to get the value of stock from toner table:

Code: Select all

<?php
	$currDir = dirname(__FILE__) . '/..';
	include("$currDir/defaultLang.php");
	include("$currDir/language.php");
	include("$currDir/lib.php");
	
	$table = get_sql_from('toner');
	if(!$table){
		header('HTTP/1.0 401 Unauthorized');
		exit;
	}

	$id_toner = makesafe($_REQUEST['id_toner']);
	

	$sql = "SELECT stock FROM toner WHERE id_toner = '{$id_toner}'";

	$result = sqlValue($sql);

	echo $result;
Than i have created allocation-dv.js and doubt to have mistakes:

Code: Select all

function show_error(field, msg){
	modal_window({
		message: '<div class="alert alert-danger">' + msg + '</div>',
		title: 'Error in ' + field,
		close: function(){
			$j('#' + field).focus();
			$j('#' + field).parents('.form-group').addClass('has-error');
		}
	});
	
	return false;
}


$j(function(){
	
	$j('#toner-container').on('select', function(){	
	  var pid = $j('#toner').val();
		
	    $j.ajax({
				url: 'hooks/ajax_allocation.php',
				data: { id_toner: pid },
				success: function(data){
					if (data) {
					$j('#stock').val(data);
				}
						
		        }
		
	  });
   });
		
	
	$j('#insert').click(function(){
		
		var stock = $j('#stock').val();
		
		if(stock < 1){
			return show_error('Stock', 'There is no more quantity left' );
		}
	});
		
})
These is my logic but i dont know if it is right or maybe im very close resolving it :?

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

Re: Calculated fields before_insert hook

Post by epano » 2020-09-03 13:54

Hello, i solved it in a simple way,

I created a field named "stock" in "allocation" table with appgini app and then made these field as lookup field of "stock" in "toner" table.
Also i checked the autofill box. Then write on allocation-dv.js only:

Code: Select all


function show_error(field, msg){
	modal_window({
		message: '<div class="alert alert-danger">' + msg + '</div>',
		title: 'Error in ' + field,
		close: function(){
			$j('#' + field).focus();
			$j('#' + field).parents('.form-group').addClass('has-error');
		}
	});
	
	return false;
}



$j(function(){
$j('#insert').click(function(){
		
		var stock = parseInt($j('#stock').text());
		
		if(stock < 1){
			return show_error('Stock', 'There is no more quantity left' );
		}
	});
})

xbox2007
Veteran Member
Posts: 129
Joined: 2016-12-16 16:49

Re: Calculated fields before_insert hook

Post by xbox2007 » 2020-10-28 19:00

i have try this in my Project : but dont know if that what you looking for

Code: Select all

	function Income_after_insert($data, $memberInfo, &$args) {
		
		$outcome = sqlValue("SELECT sum(Amount) FROM Outcome where Account='{$data['Account']}'");	
		$Income  = sqlValue("SELECT sum(Amount) FROM Income where Account='{$data['Account']}'");		
		
		$Tout    = $Income + $data['Amount'];		
		$Total   = $Tout - $outcome ;
	
		sql("UPDATE Accounts SET Balance = '{$Total}' , Income = '{$Tout}' WHERE id='{$data['Account']}'", $eo);

		return TRUE;
	}

Post Reply