[SOLVED] sc_master_value : why it do not work when I load the M/D the first time ?

Applications Forms
#1

Hi all,

I’m working on a M/D and I insert on Detail part a sc_master_value that makes some SUM on rows values.
It work nice only when I work and edit remaining on detail part of M/D form but when I load the form for the first time the two total fields that are on Master part remain always empty.
I do the calculation on

  • onScriptInit
  • onLoadRecord
  • onLoad
  • onValidateSuccess
  • onAfterInsert
  • onAfterUpdate

I did a lot of test without success.

Have you got idea wich could be my mistake ?
Thanks

I made a PHP method that do the following calculations and then I call it on the above events:

$sql_ordervalue1 = "SELECT SUM(pricequantityOrderedexchange_rate) FROM orderdetails WHERE orderNumber = {orderNumber} ";

sc_lookup(total_order1, $sql_ordervalue1);
$tot_order_value = {total_order1[0][0]};
sc_format_num($tot_order_value, ‘.’, ‘,’, 2, ‘S’, ‘1’, ‘’);
sc_master_value(‘tot_order_value’, $tot_order_value);

$sql_ordervalue2="SELECT SUM(pricequantityOrderedexchange_rate*(SELECT VarPerc FROM vatrates WHERE VatID = {VatRate})) FROM orderdetails WHERE orderNumber = {orderNumber} ";

sc_lookup(total_order2,$sql_ordervalue2);
$tot_order_value_vat = {total_order2[0][0]};
sc_format_num($tot_order_value_vat, ‘.’, ‘,’, 2, ‘S’, ‘1’, ‘’);
sc_master_value(‘tot_order_vat_incl’, $tot_order_value_vat);

#2

you must update master too. UPDATE MASTERTABLE SET tot_order_value = $tot_order_value WHERE condition.

#3

Hi Alvagar,
thanks.

It’s not clear what I’ve to do.
Are not the following lines the update of master ? When you say UPDATE MASTERTABLE SET is it a SC sintax ?

sc_master_value(‘tot_order_value’, $tot_order_value);
sc_master_value(‘tot_order_vat_incl’, $tot_order_value_vat);

#4

Hi Alvagar,
Ok I’ve understood. I used global var to have the right value also on Master side. I hope it was was you suggested.
Thanks again
Bye

#5

Last problem to fix.
Now it works but when I go to insert NEW Master record the two total fields remain populated with last value.
Where I have to insert the
[tot_order_value]=0;
[tot_order_value_vat]=0;
lines in order to have the master form field clean and total set to 0 ?

#6

Picture

This is the picture

MD_0.jpg

#7

You have to clear the global variable on load
Then do your processing

#8

Hi ,
on master or detail part of form ?

#9

see image

See image.
I did one time on details and on one Master but result is that now the calculation are not visible also for yet present records.

MD.jpg

#10

You must create a metod php with UPDATE statement for you master. it metod is called in onafterupdate, onafterinset, onafterdelete.
The idea is that every time you modify your data the master table be update.
SC example has example about that. master - detail.

#11

in this method you must use this macro sc_exec_sql(“update mytable set myfieldtotal= $newvalue where key = {key_act}”);

#12

My total fields are SC added fields and they are NOT connected to DB field. They are a sort of Running total just to show to user the amount od SUM(…) .
Is your method compatible with this one ?
I did it yet the method and I call it under

  • onScriptInit
  • onLoadRecord
  • onLoad
  • onValidateSuccess
  • onAfterInsert
  • onAfterUpdate
#13

[CLOSED]
I sorted out my problem.
There was a mistake on a query I used {… } variable that was empty instead of […] global one.
Thanks