add monthly sales

// Assuming the FCId field is the invoice # (INT) , then add this to after add record added event ****
$strSQLExists = "select * . " ' ";

$rsExists = db_query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);
if($data)

{
$sql="Update [color="#ff0000"]YourTable Set Amount_Todate=(" . $values["Amount"] . " + " . $values["Amount_Todate"] . ") Where (Reporting_Month =' " . $values["Reporting_Month"] . " ' And FCId = " . $values["FCId"] . ") ";

customQuery($sql);
return True;
} else {
[size="2"]// if [/size]don't[size="2"] exist do something else continue[/size]

echo '<script>alert("No Matching Records");</script>';

return false;
}
// After Edit Page is a little different / Before Record edited ***NOTE oldvalues depends on how you have fields setup . If they are read only then $oldvalues are not needed . But remember you have to deduct from //Amount_Todate if you are editing the totals.

// I would make the calculated fields readonly unless they have be editable if not then the code below would not be needed except to update any other fields
$strSQLExists = "select * . " ' ";

$rsExists = db_query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);
if($data)

{
$sql="Update [color="#ff0000"]YourTable Set Amount_Todate=(" . $oldvalues["Amount_Todate]" . " ) ";

customQuery($sql);
return True;
} else {
[color="#1C2837"]
[size="2"]// if [/size]don't[size="2"] exist do something else continue[/size]
}
// After Record Edited ***
$strSQLExists = "select * . " ' ";

$rsExists = db_query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);
if($data)

{
$sql="Update [color="#ff0000"]YourTable Set Amount_Todate=(" . $values["Amount"] . " + " . $values["Amount_Todate"] . ") Where (Reporting_Month =' " . $values["Reporting_Month"] . " ' AND FcId =" . $values["FCId"] . ") ";

customQuery($sql);
return True;
} else {
[size="2"]// if [/size]don't[size="2"] exist do something else continue[/size]
}
Events Documentation is all in the manual [[b]Click Here for events]("https://xlinesoft.com/phprunner/docs/check_if_specific_record_exists.htm";) . Although you can do this in javascript also. It has also been advised to use the newer database API . See Documentation.

This was a freebie as I had similar code already in a project , you really should read all the documentation . This code is all in there or Contact Support for some code help. See Support Options

Thank you very much John, you really have taken the time and pain to help me out, i really appreciate. Will incorporate this and will advise. Thanks again.

By the way FCId is the Id of record, InvoiceDate is the Date field (INT).

Its not doing what its supposed to do, its adding haphazard numbers. i need this to add Amount to AmountTodate from all previous entries of the same month. like get the records of all entries of each month's previous entries and add them to AmountTodate
[quote name='fpilot' date='02 January 2019 - 11:48 AM' timestamp='1546447681' post='86685']

Thank you very much John, you really have taken the time and pain to help me out, i really appreciate. Will incorporate this and will advise. Thanks again.

By the way FCId is the Id of record, InvoiceDate is the Date field (INT).
[quote name='John' date='02 January 2019 - 09:45 AM' timestamp='1546440340' post='86684']

[quote name='fpilot' date='02 January 2019 - 09:54 PM' timestamp='1546484083' post='86688']

Its not doing what its supposed to do, its adding haphazard numbers. i need this to add Amount to AmountTodate from all previous entries of the same month. like get the records of all entries of each month's previous entries and add them to AmountTodate
[quote name='fpilot' date='02 January 2019 - 11:48 AM' timestamp='1546447681' post='86685']

Thank you very much John, you really have taken the time and pain to help me out, i really appreciate. Will incorporate this and will advise. Thanks again.

By the way FCId is the Id of record, InvoiceDate is the Date field (INT).
First , Change date field in database to date . You need to format date field for month then . Reporting_Month . See Here for date formatting in php / mysql . Click Here .

Second , If you are running a total amount , for each month no matter the FCId field then take out
[color="#1C2837"]Another Idea is to make Invoice_Date field , add to filter in field settings and make interval . Then make it by month and make totals . This will show on left panel for each month and at the bottom the totals.

Got it by executing this sql and adding field in DailyReport instead of FoodCost Table itself.
(select sum(t2.Amount) from FoodCost t2 where t2.InvoiceDate >= date_sub(DailyReport.ReportDate, interval day(DailyReport.ReportDate) - 1 day) and t2.InvoiceDate <= DailyReport.ReportDate ) as FoodCost_MTD,
[quote name='fpilot' date='02 January 2019 - 09:54 PM' timestamp='1546484083' post='86688']

Its not doing what its supposed to do, its adding haphazard numbers. i need this to add Amount to AmountTodate from all previous entries of the same month. like get the records of all entries of each month's previous entries and add them to AmountTodate
[quote name='fpilot' date='02 January 2019 - 11:48 AM' timestamp='1546447681' post='86685']

Thank you very much John, you really have taken the time and pain to help me out, i really appreciate. Will incorporate this and will advise. Thanks again.

By the way FCId is the Id of record, InvoiceDate is the Date field (INT).