Skip to main content
masonb77741322
masonb77741322
Participating Frequently
May 10, 2019
Answered

Field is defaulting to a negative number after inputting formula

  • May 10, 2019
  • 2 replies
  • 920 views

I applied the below formula to a field that will automatically generate a number when the proper credentials have been met; however, the field currently defaults to -$2.00. Please help! Thank you!

var T1_NDR_3 = Number(this.getField("T1_NDR_3").valueAsString);

if (T1_NDR_3<0) event.value = "0";

else event.value = ((this.getField("T1_RV_3").value - this.getField("T1_Custom_3").value) * (0.2)) - (2)

This topic has been closed for replies.
Correct answer try67

How would I do that? Sorry, I'm not an advanced user when it comes to special formulas. The NDR column is the column that currently shows the -$2.00.


Try this code:

event.value = 0;
var T1_NDR_3 = Number(this.getField("T1_NDR_3").valueAsString);
var v1 = this.getField("T1_RV_3").valueAsString;
var v2 = this.getField("T1_Custom_3").valueAsString;
if (T1_NDR_3>=0 && v1!="" && v2!="") {
    event.value = (Number(v1)- Number(v2) * 0.2) - 2;
}

2 replies

BarlaeDC
BarlaeDC
Community Expert
May 10, 2019

HI,

That is fair enough, but you sum which will be evaluated when the values are 0 will return -2.

event.value = ((this.getField("T1_RV_3").value - this.getField("T1_Custom_3").value) * (0.2)) - (2)

event.value = ((0 - 0) * 0.2)  - 2

So when the file is loaded this formula will default to -2.

This means we have to be able to detect that the fields have not been filled in, so that we don't run the code until a user has changed the field value, if the fields are defaulted to empty then you could check that and not run your calculation until 1 or both have a value that is not defaulted.

//pseudo code ( as I don't have access to my machine at the moment)

If T1_NDR_3 != "" && T!_Custome_3 != "" then

     var T1_NDR_3 = Number(this.getField("T1_NDR_3").valueAsString);

     if (T1_NDR_3<0) event.value = "0";

     else event.value = ((this.getField("T1_RV_3").value - this.getField("T1_Custom_3").value) * (0.2)) - (2)

end

this shouldn't change the value until the fields actually have values.

Regards

Malcolm

BarlaeDC
BarlaeDC
Community Expert
May 10, 2019

Hi,

That is probably because the fields have no values, and therefore your sum is returning -2.

might be worth adding a check to you code to make sure the fields T!_RV_3 and T!_Custom_3, have values that are not 0 or empty strings.

Regards

Malcolm

masonb77741322
masonb77741322Author
Participating Frequently
May 10, 2019

I understand that but the way I'm using it is for a commissions report and each row will contain this formula, so it needs to be able to default to 0 when the other fields are empty. The fields containing this formula will also be added up at the bottom of the column and therefore, needs to have a value of 0 so it doesn't subtract 2 multiple times in the total number. I hope that makes sense.

try67
try67Correct answer
Community Expert
May 10, 2019

How would I do that? Sorry, I'm not an advanced user when it comes to special formulas. The NDR column is the column that currently shows the -$2.00.


Try this code:

event.value = 0;
var T1_NDR_3 = Number(this.getField("T1_NDR_3").valueAsString);
var v1 = this.getField("T1_RV_3").valueAsString;
var v2 = this.getField("T1_Custom_3").valueAsString;
if (T1_NDR_3>=0 && v1!="" && v2!="") {
    event.value = (Number(v1)- Number(v2) * 0.2) - 2;
}
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices