[SOLVED] Master Detail (Master Total)

Applications Forms
#1

On my first invoice application i need a total for the items on the detail form, and it works really perfectly.
I have my Total Field as a field created on the Master form since it is calculated field, in other words its not
a field from the master database.

i am calling php updatetotal() every time i insert or delete or update a record and it works.

The problem i have is that i dont know how to calculate back the total when i call back a saved form.
I know that if i use a real total field on the database this would solve the problem, but i think is more
professional not to stored calculated fields on database tables.

Apreciate a lot if someone can help me !!!

#2

Re: Master Detail (Master Total)

Hi,
just use the SELECT command.

SELECT SUM(detail_field) FROM detail_table WHERE detail_order_id = master_order_id

Use it with the sc_lookup() macro in the onLoad event of the form and set the value of the form field.

If your master record isn’t too long a left join should get you there as well.
Something like
SELECT
master.customer_name,
master.customer_address,
Sum(detail.price),
FROM master
LEFT JOIN detail ON (detail.order_id = master.order_id )
GROUP BY master.order_.id, master.customer_name, master.customer_address //make sure to group by all fields of the master table you use in the select

Lots of possibilities
j
sb

#3

Re: Master Detail (Master Total)

Thank you for your answer, but i might be doing something wrong, because it does not work

i have used on the onload event on the master form.

sc_lookup(dataset,“select sum(quantity * price) from detail where detail.masterid = master.masterid”);
$mastotal = {dataset}[0][0];
sc_master_value(‘mtotal’, $mastotal);

and nothing happens

is there a mistake ?

Thank you !!!

#4

Re: Master Detail (Master Total)

Try somethin like this in the onLoad event of the master form.

$check_sql=“SELECT SUM(price*quantity) FROM detail
WHERE detail.master_id = {master_id}”;
sc_lookup(erg,$check_sql);
if({erg[0][0]})
{
{total_of_master}=number_format({erg[0][0]}, 2,’,’,’.’)." ?"; // or any other currency symbol
}
else
{
{total_of_master}=number_format(0.00, 2,’,’,’.’)." ?";
}

Hope this helps.

jsb

#5

Re: Master Detail (Master Total)

Thank you so much !!!
Works Perfectly !!!