Hello to everyone,
Since I am new on PhpMaker and just an intermediate on PHP, I will need some help to my project. I am trying to build a basic invoice system but I am facing some issues with some calculations that I need to have on the add page. The master page is for invoice header and the details page is for articles/services on it. So, I have on the master section inputs like TOTAL WITHOUT TAX, TAXES, TOTAL WITH TAXES that I want to be autoupdated when an article is added and has filled with prices and tax values.On the details page, we have article inputs from where we will need to get the values for the master inputs as follows:
=> articleQuantity
=> articlePrice
=> articleVPT (article’s value without taxes) that is equal to articleQuantity x articlePrice
=> articleTax (a dropdown with following values: 0, 6%, 10%, 20%)
=> articleTaxValue that is equal to articleTax x articleVPT
=> articleTotal that is the total of articleVPT + articleTaxValueMaster inputs to be autoupdated:
=> invoiceVPT (value without taxes) => is the sum of articleVPT’s on details
=> invoiceTaxes => is the sum of articleTaxValue’s on details
=> invoiceTotal => is the sum of articleTotal’sCan anyone help me on this, please?
Thank you in advance
Go through this link page - http://www.hkvforums.com/viewtopic.php?f=4&t=39740
It explained both server & client side way to achieve your case.
Thank you for the reply.
I have checked out the thread you suggested but seems to be a little complicated for me at this stage. I have “played” around with the solutions on that thread but I didn’t achieve anything.
Anyway, thank you for your help.
kleidi wrote:
Hello to everyone,
Since I am new on PhpMaker and just an intermediate on PHP, I will need some help
to my project. I am trying to build a basic invoice system but I am facing some
issues with some calculations that I need to have on the add page. The master page is
for invoice header and the details page is for articles/services on it. So, I have on
the master section inputs like TOTAL WITHOUT TAX, TAXES, TOTAL WITH TAXES that I want
to be autoupdated when an article is added and has filled with prices and tax values.On the details page, we have article inputs from where we will need to get the values
for the master inputs as follows:
=> articleQuantity
=> articlePrice
=> articleVPT (article’s value without taxes) that is equal to articleQuantity x
articlePrice
=> articleTax (a dropdown with following values: 0, 6%, 10%, 20%)
=> articleTaxValue that is equal to articleTax x articleVPT
=> articleTotal that is the total of articleVPT + articleTaxValueMaster inputs to be autoupdated:
=> invoiceVPT (value without taxes) => is the sum of articleVPT’s on details
=> invoiceTaxes => is the sum of articleTaxValue’s on details
=> invoiceTotal => is the sum of articleTotal’sCan anyone help me on this, please?
Thank you in advanceHi.So far, the only way I have achieved this is to have a php function that is executed after detail records are inserted to update the header amounts.Havent found a jquery solution as of now.
Thank you Niijimasam,
Would you please, share your solution. At this stage i think that will be good the serverside solution too, even that the on-the-fly solution would be the perfect one.
Thank you!
kleidi wrote:
Thank you Niijimasam,
Would you please, share your solution. At this stage i think that will be
good the serverside solution too, even that the on-the-fly solution would
be the perfect one.
Thank you!
- LOGIC EXPLANATION: In my experiments in using a master/detail form PHPMaker, the detail table data is inserted FIRST into the database THEN the master table data is inserted. I say this because when I place this function on the master table server events, it doesn’t work. Don’t know why yet though For this reason, this PHP function will be used at the DETAIL TABLE SERVER EVENTS.
- Create A PHP function to sum up the required detail field and update the master detail field.
a) In my function below, I am using the link field between the master/detail tables as a way to identify the correct records to sum and update.
b) I have also included a paramater in the function for flexibility purposes i.e this will function for both updating a record and adding a new record(Explained below)function UpHeaderAmount($param){
$sql = “select sum(detail_column_a) from detail_table_a where detail_link_field= '”.$param.“”;
$sumtotal = ExecuteScalar($sql);
$updatesql = “update master_table_a set master_column_a= “.$sumtotal.”
WHERE master_link_field= '”.$param.“”;
Execute($updatesql);
} - Decide where to put your function. The Execution differs depending on the event:
a)Server Events-> Table Specific->Row_Inserted : Use $rsnew[“link_field_name”] as you are creating a new record i.e. UpHeaderAmount($rsnew[“link_field_name”]);
b)Server Events-> Table Specific->Row_Updated : Use $rsold[“link_field_name”] as you are updating an existing record i.e. UpHeaderAmount($rsold[“link_field_name”]); If you add a new detail row, the link_key_field value still hasn’t changed.
Thank you. Your code helped me.