Copy link to clipboard
Copied
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)
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;
}
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
You can add an additional if-condition that verifies that the two fields are not empty before performing the calculation.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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;
}
Copy link to clipboard
Copied
Thank you again try67!
Copy link to clipboard
Copied
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