SUM of detail in mater grid application

Scriptcase 8 Discussion
#1

Hi All,

I have transaction table which holds the information such as transactionNo, stamp, operator and transaction_detail table which holds information such as transactionFk, productFk, quantity and unitPrice.

I would like to create a grid application that shows transactionNo, stamp, operator and totalTransaction from correspond transaction_detail rows. First, I create new field totalTransaction with type currency, then add this lines of codes into onRecord event:


sc_lookup(dataset,"SELECT SUM(quantity * unitprice) FROM transaction_detail WHERE transactionFk = {id}");

if(!empty({dataset[0][0]})) {
    {totalTransaction} = {dataset[0][0]};
} else {
	{totalTransaction} = 0;
}

But it is not working, the totalTransaction is always 0. Can anyone help on this?

Daniel

#2

Use isset, not empty.
Did you tried the generated SQL isolated from SC?

Do:
{totalTransaction} = “SELECT SUM(quantity * unitprice) FROM transaction_detail WHERE transactionFk = {id}”;

Try this quyery on your faviourite db tool and look if it works

#3

Somehow it works now. I was wrong when reading my data. Some are true should return 0. Thanks Giu, I replaced empty with isset.