Calculate Total Sum from fields on Form

Scriptcase 9 Discussion
#1

I have a Form named jobcard with fields like “Service”, “addon1”, “addon2” etc, each just linking to the service and addon tables respectively and also displaying the price when selected.

I also have a Total field that i want to be updated when “Service” and “Addons” are selected.

I created a phpmethod named SumTotal with {total} = {serviceid} = {addon1id}

and then added SumTotal(); in the OnScriptinit and also added new ajax events on “Serviceid” change and “addon1” change. (only trying service and addon1 addition first, for testing and then will add the other addons.)

i have also selected “Reload form when value has changed” on service selection and addon2 selection.

it does not seem to update the total field. i suspect its because i am selecting the serviceid and addon id and not the price (int)

serviceid SQL statement:

SELECT id, sc_concat(name, desc, price)
FROM services
ORDER BY name, desc, price

addon1 SQL Statement:

SELECT id, sc_concat(name, description, price)
FROM addons
ORDER BY name, description, price

Can someone please suggest a better way to achieve this, code examples will sincerely be appreciated.

#2

Edit: removed first response with ideas for using AJAX events that didn’t work… working answer below

#3

Thanks so much for the reply. Okay cool i set that up, but now when the addon field or the service field is 0 (no selection) the system crashes. i think maybe if statements.

I used option 1 to select the service field to use ajax to update the service_price, the other way did not work.

#4

I played around with this a bit and the interaction between the different AJAX events seemed to be an issue. Here is another approach.

Keep the lookups on your services /addon fields the same as you had originally. The {total} field should be text, an integer, or a currency field in SC. You probably want to set it as a “label” field so the user will not be able to change it.

Create AJAX events for onChange for each field that runs your php method

SumTotal();

Use something like this as the PHP method:

//reset total
$total = 0;
//look up the price for the selected serviceID
$check_sql = "SELECT price from services where id = '{serviceid}'";
sc_lookup(rs, $check_sql);
if (isset({rs[0][0]}))     // Row found
{
//add service price to total
$total = $total + ({rs[0][0]});
}
else     // No row found
{  
//do nothing 
}
//lookup the price for addon1
$check_sql = "SELECT price from addons where id = '{addon1id}'";
sc_lookup(rs, $check_sql);

if (isset({rs[0][0]}))     // Row found
{
$total = $total + {rs[0][0]};
}
else     // No row found
{
//do nothing
}

//lookup the price for addon2
$check_sql = "SELECT price from addons where id = '{addon2id}'";
sc_lookup(rs, $check_sql);

if (isset({rs[0][0]}))     // Row found
{
$total = $total + {rs[0][0]};
}
else     // No row found
{
//do nothing
}


//set total field to total of prices
{total} = $total;

You can remove the “else” parts to make it cleaner. I just left those in there to make it more obvious that if they query doesn’t return any results, then that lookup doesn’t have any effect on the total.

#5

This works like a charm! thank you so so much!