how to insert SUM field on Master Detail form

Applications Forms
#1

Dear all,

I’m trying to “Sum” details fields , within a “New field” field on Master part of Master/Detail form.
Master table: dayly_out_m
Details: dayly_out (here there is the field “quantity” I need to sum on master part of form).
Added field (only for showing the total of sum): somma_qty.

I’ve seen on forum some posts and I wrote following lines.

sc_lookup(dataset,“SELECT SUM(quantity)
FROM dayly_out
WHERE dayly_out.id_dayly_out_m = {id_dayly_out_m}
AND dayly_out.date = {date}”);

$somma_qty = {dataset[0][0]};
sc_master_value(‘somma_qty’, $somma_qty);

echo sc_master_value(‘somma_qty’, $somma_qty);

As … always nothing happens… ;-((

I’ve also tried this with no results.

sc_lookup(dataset,“SELECT SUM(quantity)
FROM dayly_out
WHERE dayly_out.id_dayly_out_m = {id_dayly_out_m}
AND dayly_out.date = {date}”);

$somma_qty = {dataset[0][0]};
{somma_qty} = $somma_qty;
echo {somma_qty};

I wrote it within onLoad events of Master part of forms.
Probably I think I’m wronging code and also event. The big doubt, on the other hand, is concerning the functioning of sc_lookup used on added form fields. The same problem of previous post. Could it be a bug ?
Is it correct to use SELECT SUM in this way ? On mysql it works right !
Many thanks
Giovannino

#2

Re: how to insert SUM field on Master Detail form

Have you tried using an alias for your sum field?

SUM(field) AS sum_field

echo {sum_field}

Regards,
Scott.

#3

Re: how to insert SUM field on Master Detail form

Dear Scott,
This is what I did. The image could probably be more clear.
somma_qty field is “Decimal”. Still no calculation.

Thanks

{somma_qta}="";

sc_lookup(dataset,“SELECT SUM(quantity)as somma_qta
FROM dayly_out
WHERE dayly_out.id_dayly_out_m = {id_dayly_out_m}
AND dayly_out.date = {date}”);
echo " QTA ". {somma_qta};

$somma_qty = {dataset[0][0]};
somma_qty} = $somma_qty;
echo " QTY <br>". {somma_qty};

http://img30.imageshack.us/img30/7589/sumqty.png

#4

Re: how to insert SUM field on Master Detail form

The first echo of {somma_qta} will not work, as it references a field, you will need to use the {dataset[0][0]} as you have below as that is the populated array.

if {dataset[0][0]} does not return a result, then it must be a SQL issue. The date format looks good, but you will need to place quotes around the date to make it


AND dayly_out = "2010-01-04"



sc_lookup(dataset,'SELECT SUM(quantity)as somma_qta
          FROM dayly_out
          WHERE dayly_out.id_dayly_out_m = {id_dayly_out_m}
          AND dayly_out.date = " '.{date}.' " '); // spaced for display purposes
#5

Re: how to insert SUM field on Master Detail form

Also, try to wrap your code for check sc_lookup like:

sc_lookup(dataset,$sql);

if ({dataset} == false || empty({dataset}) ) {
sc_alert(‘ERROR MESSAGE’);
} else {
{form_field} = {dataset[0][0]};
}

regards,
Scott.

#6

Re: how to insert SUM field on Master Detail form

Here it is last modification:

$sql = “SELECT SUM(quantity)as somma_qta FROM dayly_out WHERE dayly_out.id_dayly_out_m = {id_dayly_out_m} AND dayly_out.date = {date}”;

sc_lookup(dataset,$sql);

if ({dataset} == false || empty({dataset}) ) {
sc_alert(‘ERROR MESSAGE’);
} else {
{somma_qty} = {dataset[0][0]};
}

{somma_qty} = $somma_qty;
echo " QTY <br>". {somma_qty};

http://img97.imageshack.us/img97/7254/sumcalc.png

#7

Re: how to insert SUM field on Master Detail form

It seems that the date of sql select would be wrote as:

SELECT SUM(quantity) FROM dayly_out WHERE dayly_out.id_dayly_out_m = 3 AND dayly_out.date = 2010-01-04

I’m not programmer but the date field 2010-01-04’ normally is between hiphens .

$sql = “SELECT SUM(quantity)as somma_qta FROM dayly_out WHERE dayly_out.id_dayly_out_m = {id_dayly_out_m} AND dayly_out.date = ‘{date}’”;

I have added hiphens ‘{date}’ and it looks better but still no data come out !!

#8

Re: how to insert SUM field on Master Detail form

Link to project and sql.

https://docs.google.com/leaf?id=0B_JC1r1rTYiLNzQ0MmY0YTEtZTkyMS00NGQ1LWIxY2EtODc1Y2JkYjRhYTZm&hl=it&authkey=CJm2jaYN

https://docs.google.com/leaf?id=0B_JC1r1rTYiLNDhlZGE2NTctODhiZi00NmZjLWE3YzAtMzI2OTE1MGZlNzYy&hl=it&authkey=CLOvy90G

form_dayly_out_m --> Events --> onLoad

#9

Re: how to insert SUM field on Master Detail form

try reversing the quotes and also trying parsing the string and see if that helps:

your code:


$sql = "SELECT SUM(quantity)as somma_qta FROM dayly_out WHERE dayly_out.id_dayly_out_m = {id_dayly_out_m} AND dayly_out.date = '{date}'";

new code:


$sql = 'SELECT SUM(quantity)as somma_qta FROM dayly_out WHERE dayly_out.id_dayly_out_m = {id_dayly_out_m} AND dayly_out.date = "{date}"';

or


$sql = 'SELECT SUM(quantity)as somma_qta FROM dayly_out WHERE dayly_out.id_dayly_out_m = {id_dayly_out_m} AND dayly_out.date = "'.{date}.'"';

You have to remember that SC is creating these statements for you, so you have be careful about your quote placement and be aware that SC may change it.

As you can see in your image, 3rd SQL statement has quotes around date in SELECT COUNT, but your last one does not, hence the error = lack of results

Regards,
Scott

#10

Re: how to insert SUM field on Master Detail form

$sql = ‘SELECT SUM(quantity)as somma_qta FROM dayly_out WHERE dayly_out.id_dayly_out_m = {id_dayly_out_m} AND dayly_out.date = “{date}”’;

1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘>id_dayly_out_m AND dayly_out.date = "$this->date "’ at line 1

 ADOConnection._Execute(SELECT SUM(quantity)as somma_qta FROM dayly_out WHERE dayly_out.id_dayly_out_m = $this-&gt;id_dayly_out_m AND dayly_out.date = "$t..., false) % line 1010, file: adodb.inc.php

ADOConnection.Execute(SELECT SUM(quantity)as somma_qta FROM dayly_out WHERE dayly_out.id_dayly_out_m = $this->id_dayly_out_m AND dayly_out.date = "$t…) % line 2084, file: form_dayly_out_m_apl.php
form_dayly_out_m_apl.nm_proc_onload() % line 3019, file: form_dayly_out_m_apl.php
form_dayly_out_m_apl.nm_acessa_banco() % line 932, file: form_dayly_out_m_apl.php
form_dayly_out_m_apl.controle() % line 1285, file: form_dayly_out_m.php

$sql = ‘SELECT SUM(quantity)as somma_qta FROM dayly_out WHERE dayly_out.id_dayly_out_m = {id_dayly_out_m} AND dayly_out.date = "’.{date}.’"’;

1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘>id_dayly_out_m AND dayly_out.date = “2010-01-04”’ at line 1

 ADOConnection._Execute(SELECT SUM(quantity)as somma_qta FROM dayly_out WHERE dayly_out.id_dayly_out_m = $this-&gt;id_dayly_out_m AND dayly_out.date = "20..., false) % line 1010, file: adodb.inc.php

ADOConnection.Execute(SELECT SUM(quantity)as somma_qta FROM dayly_out WHERE dayly_out.id_dayly_out_m = $this->id_dayly_out_m AND dayly_out.date = "20…) % line 2085, file: form_dayly_out_m_apl.php
form_dayly_out_m_apl.nm_proc_onload() % line 3020, file: form_dayly_out_m_apl.php
form_dayly_out_m_apl.nm_acessa_banco() % line 932, file: form_dayly_out_m_apl.php
form_dayly_out_m_apl.controle() % line 1285, file: form_dayly_out_m.php

#11

Re: how to insert SUM field on Master Detail form

This works.

$sql =“SELECT SUM(quantity)as somma_qta FROM dayly_out WHERE dayly_out.id_dayly_out_m = {id_dayly_out_m} AND dayly_out.date = ‘{date}’”;

Now I have to display the value of it within “somma_qty” field that is still empty.

Why it looks like a select field also if it’s a decimal ??

Thanks again and be patient …

#12

Well that works - but I find that it takes a long time to run - I got random errors using that query (some rows seemed to divide by 1000) and it consumed resources on my server.

… This is, in my situation, is a more accurate solution - minimal resource usage:

//UPDATE SUM_BILLABLE_EXPENSE
$update_billable_expense=“UPDATE job T1
INNER JOIN
( SELECT job_id, SUM(value) ‘sumu’
FROM job_detail WHERE (job_detail.category_type = ‘expense’ AND job_detail.billable_status >= 1)
GROUP BY job_id
) T2 ON T1.job_id = T2.job_id
SET T1.sum_billable_expense = T2.sumu” ;
sc_exec_sql($update_billable_expense) ;