Master/Detail form (total calculation)

Applications Forms
,
#1

hello,

i have a master/detail form with the next columns:
master sql name: file
master sql columns: id, order_nr, stipulated_total

slave sql name: operation
slave sql columns: id, file_id, order_nr, stipulated

I’m trying to make an ajax event to calculate the total for all the numbers entered on the slave - “stipulated” and put it in master “file” at “stipulated_total”

i created a php method with the name “updMaster” and i inserted this cod:
//-------------------------------------------------------------------------------
sc_lookup(dataset, “SELECT SUM (stipulated)
FROM operation
where file_id = {file_id}”);

$master_total = {dataset[0][0]};

sc_master_value(‘stipulated_total’, $master_total);

sc_exec_sql(“UPDATE file
SET stipulated_total = $master_total
WHERE id = {id}”);
//-------------------------------------------------------------------------------

I then put the name of the php method on “updMaster();” to the events on update, on delete and on insert…

but every time i have this error:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SQL ERROR’s:
Method updMaster: “SELECT SUM (stipulated) FROM operation where file_id = {file_id_}”
()

SQL ERROR’s:
Method updMaster: “SELECT SUM (stipulated) FROM operation where file_id = {file_id_}”
()
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Does anyone have any ideea where i got wrong?

#2

I think for field variables {xxx}, you need to put them outside of the quotes and build the string. E.G.

sc_lookup(dataset, "SELECT SUM (stipulated)
FROM operation
where file_id = " . {file_id});

OR (if file_id is a string):

sc_lookup(dataset, "SELECT SUM (stipulated)
FROM operation
where file_id = '" . {file_id} . "'");
#3

SOLVED - Master/Detail form (total calculation)

[QUOTE=adz1111;34065]I think for field variables {xxx}, you need to put them outside of the quotes and build the string. E.G.

sc_lookup(dataset, "SELECT SUM (stipulated)
FROM operation
where file_id = " . {file_id});

OR (if file_id is a string):

sc_lookup(dataset, "SELECT SUM (stipulated)
FROM operation
where file_id = '" . {file_id} . "'");

[/QUOTE]

Hey, thank you for your hint

I succeeded building it in two ways:

//////////////////////////////////////////////////
////////////////// V2 /////////////////////////////
$check_sql = “SELECT SUM(stipulated)”
. " FROM operation"
. " WHERE file_id =" . {file_id};

sc_lookup(dataset,$check_sql);

if(!empty({dataset[0][0]}))
{

$master_field = ‘stipulated_total’;
$master_total = {dataset[0][0]};

sc_format_num($master_field, ‘.’, ‘,’, 2, ‘S’, ‘1’, ‘’);
sc_master_value($master_field,$master_total);

$exec_sql = “UPDATE file”
. " SET stipulated_total = $master_total"
. " WHERE id = " . {id};

sc_exec_sql($exec_sql);
} else {

$master_total = 0;
sc_format_num($master_field, ‘.’, ‘,’, 2, ‘S’, ‘1’, ‘’);
sc_master_value($master_field,$master_total);

}

//////////////////////////////////////////////////
////////////////// V1 /////////////////////////////
/*$check_sql = “SELECT SUM(stipulated)”
. " FROM operation"
. " WHERE file_id =" . {file_id};

sc_lookup(dataset,$check_sql);

$master_field = ‘stipulated_total’;
$master_total = {dataset[0][0]};

sc_master_value($master_field, $master_total);

$exec_sql = “UPDATE file”
. " SET stipulated_total = $master_total"
. " WHERE id = " . {id};

sc_exec_sql($exec_sql);
*/