Skip to main content
masonb77741322
masonb77741322
Participating Frequently
May 10, 2019 2:49 PM UTC
Answered

Field is defaulting to a negative number after inputting formula

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 is closed to new replies. Start a new post to keep the conversation going.
try67
try67Correct answer
Community Expert
May 10, 2019 3:35 PM UTC

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;
}
View reply

7 replies

BarlaeDC
Community Expert
BarlaeDCCommunity Expert
Community Expert
May 10, 2019 3:28 PM UTC

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

Translate
BarlaeDC
Community Expert
BarlaeDCCommunity Expert
Community Expert
May 10, 2019 3:02 PM UTC

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

Translate
masonb77741322
masonb77741322Author
Participating Frequently
May 10, 2019 3:17 PM UTC

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.

Translate
try67
Community Expert
try67Community Expert
Community Expert
May 10, 2019 3:26 PM UTC

You can add an additional if-condition that verifies that the two fields are not empty before performing the calculation.

Translate
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices