AppGini Community Forums

HI,

I need some help with calculating in a hook please

tables
products
order
orderdetail

in orderdetail I have a field called product, when I select a product from the list I have an auto lookup field that populates the price field from the products table, this displays correctly.

I then have another field called quantity which I manually add the value a wish to purchase of the product

Then is the total field which is calculated by quantity x price = total, I do this through the orderdetail hook

$data['total'] = $data['quantity'] * $data['Price'];

problem
the auto lookup sets the price in the database to the ID of the products table and not the actual value which is displayed.

example - correct
product A = $1000
quantity = 2
total = $2000

example - database
product A = 1
quantity = 2
total = 2

How can I get the actual values to be passed to the table instead of the field ID of the other table?

thanks in advance.

Joe

You can try something like this perhaps. Stop the autoupdate field thingy for price. Update your table pulling the price in after_update and after_insert hooks for orderdetails table. The SQL to use should look something like this :

sql("update orderdetail left join products on orderdetail.product = products.product
set orderdetail.price = products.price
where order_num ='{$data['selectedID']}'", $eo);

I am guessing your field names so make sure to verify / update. So this should first get the actual price in that orderdetail row after its inserted or created. You can then do your

$data['total'] = $data['quantity'] * $data['Price'];

thing or even do that in another SQL update statement. Either way should work.

Good luck.

Thanks for the info. Just after a clarification.

I haven't used AppGini for several years & there has been a lot of changes.

I have a similar problem as orig described by JOE. I use several tables to keep track of service jobs with materials & labour.

I have tried this & doesn't appear to work for me, what have I done wrong.


Tables
MATERIALS
JOBCARDS
JC_ITEMS

In JC_ITEMS I have a field called JC_MAT_NAME, I use a lookup in the MATERIALS table to get the material names. When I select a MAT_NAME from the list, I have another auto lookup field that populates the price field from the MATERIALS table, this displays correctly.

I then have another field called JC_QTY which I manually add the value of the product required. The JC_TOTAL field is calculated by JC_QTY x JC_MAT_COST = JC_TOTAL. I tried do this through the JC_ITEMS hook

$data['jc_total'] = $data['jc_qty'] * $data['jc_mat_cost'];

This lead to the error above. The auto lookup sets the price in the database to the ID of the MATERIALS table and not the actual value in the table.


To rectify this, I have tried your suggestion & still calculating wrong. I have put the following code into the jc_items.php file in the hooks folder.


function jc_items_after_insert($data, $memberInfo, &$args){

sql("update jc_items left join materials on jc_items.jc_mat_name = materials.mat_name
set jc_items.jc_mat_cost = materials.mat_cost
where jobcard_ID ='{$data['selectedID']}'", $eo);

$data['jc_total'] = $data['jc_qty'] * $data['jc_mat_cost'];

return TRUE;
}


AND

function jc_items_after_update($data, $memberInfo, &$args){

sql("update jc_items left join materials on jc_items.jc_mat_name = materials.mat_name
set jc_items.jc_mat_cost = materials.mat_cost
where jobcard_ID ='{$data['selectedID']}'", $eo);

$data['jc_total'] = $data['jc_qty'] * $data['jc_mat_cost'];

return TRUE;
}


NOTE: JOBCARD_ID is the equivalent to the order_num in the example.

Have I done this correctly or did I miss read the message???

Any & all assistance is appreciated.

Thanks
Warren