Show calculated field on form only - not to be stored in db

Applications Forms
, , , ,
#1

I have a table with cost_price and selling_price. I also have a persistent calculated column (I’m using Mariadb) which shows the margin percentage using the formula (selling_price - cost_price) / cost_price * 100.

That already works fine but my customer wants to be able to change the selling price and see what the margin would be before updating the product record.

My plan is to have a calculated field on the form that uses the same calculation as the table column to show the margin %. The field would be updated when either the cost_price or selling_price are changed. As the calculation would be the same then, when the record in updated, I will not update the calculated table field (actually I don’t think I can anyway) as the calculated field will have the same value as the form anyway.

I did look at a solution which involved updating the record and refreshing the form at each change of selling_price or cost_price… I couldn’t see an easy way to do this and I think the extra overhead is poor design.

The best solution appears to be Ajax using the changed events to update a calculated field on the form but couldn’t find a good example and javascript is not my strong point…

Can anyone point me at a solution? I cannot believe this problem is a new one but I didn’t find one on the forum.

David Goadby, North Wales

#2

Ok, I had a coffee and a think and I now have a solution. I will explain it here as it is not obvious to neewbies how some of Scriptcase really works… (some docs are out of date and others are a bit too glib to be helpful).

I have two table columns called sales_price, cost_price and margin_percent. margin_percent is a calculated field which takes the value: (sales_price - cost_price)/cost_price *100.

I created a field called margin_calc and placed it on the form. All of the fields are decimal with 2 decimal places.

I created a php method called update_margin_field. This saves code as we are going to use a number of events where this update needs to be done.

Here is the php method. The form fields are bounded by {} and should be self explanatory. The thing to remember is the table columns and the extra field are all treated the same way. That’s how we update our margin_calc field on the form.

/**

  • update the margin field
    */

// test for 0 to stop divide by zero error
if ({cost_price} == 0)
{
{margin_calc} = 0;
}
else // should be ok. Round to 2 decimal places
{
{margin_calc} = round(( {sale_price} - {cost_price} ) / {cost_price} * 100,2);
}

Now, we need to decide when the margin_calc field needs updating. I added two form events:onLoad and onNavigate. These ensure that margin_calc is populated when the form is loaded and also when the next record is selected.

As the user will be changing the cost_price and the sales_price we will need to update margin_calc if either field is changed. To do this I created two Ajax events for cost_price_on_change and sales_price_on_change.

For all of the events above I just added this code:

/**

  • update the margin field using our method
    */
    update_margin_field();

And that is it. There may be other and better ways of doing this but this works.

David Goadby, North Wales.