Total of detailed items in Master table

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
alfator
User
Posts: 2

Total of detailed items in Master table

Post by alfator »

Hi,

I have a master table (order) which has a detail table (orderitem). In the Order table there is a field totalordered. I want this field to be automatically populated in master/detail add. It should sum up the subtotal field/s in orderitem and show the value in the master table.

Can someone please help?

Thanks


sangnandar
User
Posts: 980

Post by sangnandar »

I assumed your schema:
Order
id INT
...

orderitem
id INT
order_id INT
value INT
...

Then,

  1. Create totalordered as custom field in Order.
  2. Use this expression: (select sum(value) from orderitem where order_id=id).

alfator
User
Posts: 2

Post by alfator »

Thank you for your reply.

I tried it but it does not seem to work. I'm using PHPMaker 2017.

I would like to have the total adjusted automatically anytime a grid item (orderitem) is added or modified. And the user can see immediate change in order total.

Any idea plz?


sangnandar
User
Posts: 980

Post by sangnandar »

Please explain in detail what kind apps behavior do you expect.
For example:
I open this page, see this thing, click that thing, I expect this thing.


arbei
User
Posts: 9288

Post by arbei »

alfator wrote:
Hi,

I have a master table (order) which has a detail table (orderitem). In the
Order table there is a field totalordered. I want this field to be
automatically populated in master/detail add. It should sum up the subtotal
field/s in orderitem and show the value in the master table.

Can someone please help?

Thanks

Add your code to the "subtotal" field in [Edit Tag] -> [Client side event] to monitor the change of the "subtotal" field.
And when the field change, calculate the "Total" with all the "subtotal" field and assign the total to the "totalordered" field with jQuery code.

For example:
var tt = 0;
$("#<id of the subtotal field>").each(function(){
// code to calculate the total
}
$("#x_totalordered").val(tt); // assign the total to the field "totalordered".

The above id may not correct, and you need to get the correct id with Right Click + Inspect.


hoguera
User
Posts: 2

Post by hoguera »

hi alfator, could you solve it?


kirondedshem
User
Posts: 642

Post by kirondedshem »

SInce both the detail form and master form are all available on the same page, you need to create a javascript fucntion which when called can LOOP throught all detail rows as it sums up the values from each to get the overall toatl, after which it can assign it to a field on your master form.

Then attch event handlers to the fields in your detail table so that they call the above function everytime they change thier values, this will give you the desired effect

THIS IS FROM AN OLD TOPIC BUT ITS CA POINT YOU IN THE RIGHT DIRECTION.

THE FULL TOPIC IS AT viewtopic.php?f=4&t=40703 so you can read through it all BUT IN A SUMMARY below if the approach you want, just read through it shoud give you an idea of how to do what i indicated above

In my case I have the following tables
table(1) product:id,name
table (2)sale_detail:id,product_id,quantity,price,total_amount

2.allow user to put type in quantity & price for any of the rows, and i use that to calculate the total_amount on each row.
3.Be able to display the sum of all total amounts(net_amount) somewhere else everytime the total_amount of any of the rows changes.

5.Attach scripts to auto re calculate the total_amount for every row when quantity or price has changed, got to field settings of quantity&price in sale_detail->client side events and put
{ // keys = event types, values = handler functions
"keyup": function(e) {
// Your code

var $row = $(this).fields();

var total_amount = $row["quantity"].toNumber() * $row["price"].toNumber();
//update total amount for this row
if((total_amount > 0) || (total_amount < 0))
{$row["total_amount"].value(total_amount);}
else
{$row["total_amount"].value("0");}

//recalculate net amount
CalculateNet();

}
}

NOTE: notice the function "CalculateNet()" it is the one responsible for recalculating the net amount so we call it after we are sure total_amoutn on a given row has changed

  1. put the CalculateNet() function in client scripts->table specific->add section so that its available to be called. SO paste the function as below
    NOTE: if you inspect your a total_amount input field you'll see is has certain properties id="x[n]_total_amount" where n is the row number, class="form-control" and data-field="x_total_amount", so we just have to look for these controlls and get thier values and sum them up

function CalculateNet(){
var net_amount = 0;
var all_amounts = $('[data-field="x_total_amount"]');
for(var i = 0;i < all_amounts.length;i++){
var the_amount = 0;
//ensure that its the input controll of interest
if(all_amounts[i].className == "form-control"){
the_amount = all_amounts[i].value;
if(!(the_amount > 0)){the_amount = 0;}
net_amount += parseFloat(the_amount);
}
}
//show the net amount anywhere for now am putting in console log
console.log(net_amount);
}

  1. if you've done it correctly you should see the net_amount in console log change every time you change the qauntity/price on any row. now you just have to draw a row below to show it or something.

Post Reply