OnChange Calculated Field

Greta Dimitrova · ‎06-27-2022

On a Form I have 3 different Filed from Type currency.

The Values of these 3 fields are set by transform script/ spreadsheet upload. Example:

Open Amount : 600 $

Amount Paid: 0$

Amount Credited: 0$

The requirements is that if Amount Paid or Amount Credited are changed by the user in the form, that the Open Amount is calculated as Open Amount = Amount Paid + Amount Credited is updated in the form.

If for example Amount Paid is updated to 600$ then Open Amount should update in the form as 0$.

Alternative case is if Amount Paid is 400 and Amount Credited is 200, the Open Amount shall calculate to 0.

I wrote initially a Business Rule that works but then the value is re-calculated after form update button is checked, and I would like the user to actually see the Open Amount value change on the form before they update the form.

I think this can be accomplished by a Client Script, but I have not been able to make it work.

(my script below):

function onChange(control, oldValue, newValue, isLoading, isTemplate) {
if (isLoading || newValue === '') {
return;
}
//when any of the Amounts such Paid,Credited,Settled is updated Open Amount shall be re-calculated
var a1 = g_form.getValue('u_amount_credited');
var a2 = g_form.getValue('u_amount_paid');

var OpenAmount= g_form.getValue('u_open_amount')-a1-a2;
g_form.setValue('u_open_amount', OpenAmount);
}

What do I need to change for the script to work? Appreciate the help very much.

Greta

OlaN · ‎06-28-2022

Hi,

Currency field are a bit special. If you use .getValue() within a client script on a currency field, you get both the currency and the value, example USD;600.00
And that notation will not work to make calculations on.

To only get the actual value, instead use the method g_form.getDecimalValue('field_name')

Also, when setting a currency value with a client script, you must include the currency in the same formatting.

So your code should look something like this:

    var creditAmount = g_form.getDecimalValue('u_amount_credited');
    var paidAmount = g_form.getDecimalValue('u_amount_paid');
    var openAmount = g_form.getDecimalValue('u_open_amount');
    var recalculatedAmount = openAmount - creditAmount - paidAmount;
    g_form.setValue('u_open_amount', 'USD;' + recalculatedAmount);

View solution in original post

OlaN · ‎06-29-2022

I'm guessing that the issue is the "double" recalculation performed.

Consider this scenario:
Open amount is 600
Paid amount is 0
Credited amount is 0
Then you enter 10 as value in Paid, open will calculate to 590 (600-10-0 = 590)
Then you enter 20 as value in Credited, open will now calculate to 560 which will be incorrect, but that it is how it's calculated (590-10-20 = 560)

The same issue will occur if you first enter 10 as Paid, realize you missed a zero, it should be 100, so you correct it, then it will calculate open amount to 490 (600-10-0 = 590 -> 590-100-0 = 490)

So recalculating based on client script will get rather complicated when you deal with all different types of scenarios that could happen.

I would suggest that you perform the calculating as a Business rule instead, and just use client script to highlight if the entered value is incorrect, for example if credited amount is larger than open amount.

Greta Dimitrova · ‎06-29-2022

I wrote a business rule, but Business Rule before Insert/Update does not change the Open Amount to the calculated value unless a user Updates the record, in which case the value is committed to the database.

Is there a way to implement the business rule before a record is updated?

OlaN · ‎07-01-2022

That's the default behaviour of a business rule, to run before a insert/update happens.

Please share your business rule config.

Greta Dimitrova · ‎07-06-2022

OlaN,

Thank you. The onChange script worked, I had to implement a global variable to store the value of calculated field before the first onChange is executed, in case there was a typo, since multiple changes will start with the last change.