AppGini Community Forums

I have "Sales" table and "Sales Details" table. Under "Sales Details" table I have two fields - "Jars" and "Cases"

I need to calculate the Total Jars and Total Cases and reflect the total to my "Sales" table under "TotalJars" and "TotalCases" fields.

Please see attachment.

I need help please. Below is my ajax script code; Im having an issue to display the Total Cases, I'm only seeing 0.00. I also tested per my video course showed but I'm getting "0.00" as the total cases. Will someone please help or point me the error in my script. Thanks so much.

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

/* grant access to all users who have acess to the sales table */
$od_from = get_sql_from('sales');
if(!$od_from){
header('HTTP/1.0 401 Unauthorized');
exit;
}

$id = intval($_REQUEST['id']);
if(!$id) exit;

$sales_SubTotal = sqlValue("select sum((Cases * Pak) + Jars) from salesdetails where SalesID='{$id}'");

$sales_TotalCases = $SubTotal;
sql("update sales set TotalCases='{$sales_TotalCases}' where SalesID='{$id}'", $eo);

echo number_format($sales_TotalCases, 2);

What is the result of the call?

To narrow things down, you should set $sales_TotalCases to 999 or something and then see, if this gets written to the database and if this is the return value of your script.

You should check $eo variable after sql(...) call.

Maybe there is a problem in the SQL update statement: if TotalCases is a numeric field (integer, float), you should leave out the ' ' characters.

should be

Hi,

I think you missasing your $sales_TotalCases, the $SubTotal is not set in your script.

I guess you want to use

$sales_TotalCases = $sales_SubTotal;

instead.

Thanks so much. It works!

So if I wanted to add another column for total jars should I just add or insert this code below?

$sales_TotalJars = sqlValue("select sum(Jars) from salesdetails where SalesID='{$id}'");

$TotalJars = $sales_TotalJars;
sql("update sales set TotalJars='{$TotalJars}' where SalesID='{$id}'", $eo);

echo number_format($sales_TotalJars, 2);

So I tried this code and both total jars and total cases are showing their total sums however Im getting this:

Total Jars 25.003,964.00
TotalCases 3964.00

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

/* grant access to all users who have acess to the sales table */
$od_from = get_sql_from('sales');
if(!$od_from){
header('HTTP/1.0 401 Unauthorized');
exit;
}

$id = intval($_REQUEST['id']);
if(!$id) exit;

$sales_TotalJars = sqlValue("select sum(Jars)from salesdetails where SalesID='{$id}'");

$sales_TotalJars = $sales_TotalJars;
sql("update sales set TotalJars='{$sales_TotalJars}' where SalesID='{$id}'", $eo);

echo number_format($sales_TotalJars, 2);

$sales_TotalCases = sqlValue("select sum(Cases)from salesdetails where SalesID='{$id}'");

$sales_TotalCases = $sales_TotalCases;
sql("update sales set TotalCases='{$sales_TotalCases}' where SalesID='{$id}'", $eo);

echo number_format($sales_TotalCases, 2);

Hi,

if you want to handle more than one dataset, you need to return the data in a different format, eg. json

The echo is used as return of your call, so use it only once.

you could use
But you need to handle the data in your ajax call to put into the right fields.