Master/Detail totals not always updating

Applications Forms
, , , ,
#1

I have a Master/Detail form, where I need a total for each row of the detail form and then I need to sum those and display it on the Master form. I have it mostly working but there are two bugs.
[SOLVED]On both version 7 and 8, when the Master/Detail is run the sum shown on the Master form is initially 99.9999, which I guess is undefined or overflow. Once I make a new or update an entry on the Details form it corrects the sum on the Master form.[/SOLVED]

Separately, if I am using version 7 then on the Details form the totals work fine so long as I change focus to another field before you click the ‘update row’ button.
If I am using version 8 then instead the sum on the Master form does not update unless I change focus to another field with in the Details form before clicking the ‘update row’ button.

My updMaster function for updating the master form is:
[HR][/HR]sc_lookup(dataset, “SELECT SUM(price * (yxs+ys+ym+yl+yxl+yxxl+ysize+
axxs+axs+asm+am+al+axl+axxl+a3xl+a4xl+a5xl))
FROM items
WHERE OrderID = ‘{OrderID}’”
);
$subtotal = number_format( {dataset[0][0]}, 2, ‘,’, ‘.’);
sc_master_value(‘subtotal’, $subtotal);
$subtotal = {dataset[0][0]};
sc_exec_sql(“UPDATE orders
SET subtotal = $subtotal
WHERE OrderID = ‘{OrderID}’”
);
[HR][/HR]Just like the Master/Detail example, this is run onAfterInsert, onAfterUpdate, and onAfterDelete on the Detail form. Adding this to onLoadRecord and onLoad made no difference.

My setTotal function for determining totals on the Detail form is:
[HR][/HR]{ItemTot} = {price} * ({yxs}+{ys}+{ym}+{yl}+{yxl}+{yxxl}+{ysize}+{axxs}+{axs}+{asm}+{am}+{al}+{axl}+{axxl}+{a3xl}+{a4xl}+{a5xl});
[HR][/HR]Just like the Master/Detail example, this is run for every field that is required for the calculation as an onChange on the Detail form. I also added this to the onLoadRecord so the total field will be calculated when it first loads. Adding this to any and all the other Events makes no difference.

Any ideas how to fix this?

P.S. It seems the example code they show you on http://www.scriptcase.net/scriptcase-samples/tutoriais/form/form38.php do not match the application that supposedly runs from it http://www.scriptcase.net/sistemas/v7/exemplos/form38/ . I have had similar experience with the video tutorials code not matching the application.

#2

One thing you might like to look into is the following sc macro:

sc_master_value(‘Object’, Value)

This macro update a Master Application object from a Detail Application.

#3

Albert, he is already using that macro in the code snippet he supplied…

#4

cnbrammer - I think you may have a misplaced ‘}’ on dataset array. Should be AFTER the last ‘]’…

$subtotal = number_format( {dataset[0][0]}, 2, ',', '.');     // This line...
sc_master_value('subtotal', $subtotal);
$subtotal = {dataset[0][0]};                                  // ... and this one
sc_exec_sql("UPDATE orders

Of course, no idea if that is actually the problem :slight_smile:

#5

Thanks, I made those corrections; however, I am still having the same problems. In addition I had to change the field name ‘as’ to ‘asm’ to prevent conflicts with PHP’s ‘AS’ function.

#6

I found a work around for the first problem. I added the following to onLoad on the Master form:

sc_lookup(dataset, “SELECT SUM(price * (yxs+ys+ym+yl+yxl+yxxl+ysize+
axxs+axs+asm+am+al+axl+axxl+a3xl+a4xl+a5xl+asize))
FROM items
WHERE OrderID = ‘{OrderID}’”
);
$subtotal = number_format( {dataset[0][0]}, 2, ‘,’, ‘.’);
{subtotal} = $subtotal;

I am still having the same issues with totals not updating unless I change field focus before clicking the ‘update row’ button.

#7

Hi other possibility I noticed is that when building the SQL string for sc_lookup and sc_exec_sql you need to break the variables out if the string so the string has the actual values already substituted - this is unlike say a field’s lookup section in SC where what you have works because SC substitutes in that situation fine.

So what I mean is, from this:

sc_lookup(dataset, "SELECT SUM(price * (yxs+ys+ym+yl+yxl+yxxl+ysize+
axxs+axs+asm+am+al+axl+axxl+a3xl+a4xl+a5xl))
FROM items
WHERE OrderID = '{OrderID}'"
);

It would be better as this:

$sqls = "SELECT SUM(price * (yxs+ys+ym+yl+yxl+yxxl+ysize+" .
                "axxs+axs+asm+am+al+axl+axxl+a3xl+a4xl+a5xl)) " .
           "FROM items " .
           "WHERE OrderID = '" . {OrderID} . "'";       // NOTE: variable outside of the string.

sc_lookup(dataset, $sqls);

Need to so the same further down too…

From:

sc_exec_sql("UPDATE orders
SET subtotal = $subtotal
WHERE OrderID = '{OrderID}'"
);

To:

$sqls = "UPDATE orders ".
      "SET subtotal = " . $subtotal .
      "WHERE OrderID = '" . {OrderID} . "'";       // Again, variable outside of string

sc_exec_sql($sqls);
#8

Thanks for replying. I tried the above, no difference in behavior.

#9

Hmm - not sure then I’m afraid (move / copy / raise this to the SC8 bug forum) - but distil it to focus on the one remaining issue, i.e. update not taking place unless focus moved…

#10

I guess I am going to have to try and make my own update button to get around the quirky behavior of the all ready present ‘up date row’ button.

#11

Hi cnbrammer. How you’re doing with that problem (detail sum not updating unless change of focus)?

I have the same issue here, but I didn’t need to recreate the “update” button. Instead, I added the row’s calculation of the total in the “Before” events (beforeInsert, beforeUpdate)

Still, did you had problems with the “sc_master_value” macro?
I’m not sure you did, but I do, so I’m just making sure I do not duplicate a thread.

(I’m running Dec. 27 2014 Fully updated Scriptcase V8)