How do I create a custom calculation script?

[vickicardoni]

I have zero knowledge of javascript and having some trouble creating a formula that would calculate a percentage.  I have two fields, GrantTotals and NonGrantTotals, that are both formatted as numbers to two decimal places.  Here's the code I wrote to calculate a number in a new field that's formatted as text:

event.value = ( this.getField("GrantTotals").value / ( this.getField("GrantTotals").value + this.getField("NonGrantTotals") ) ) * 100;

It seems to be partially working and calculating a total, but not multiplying by 100.  For example, if I have 25.00 in GrantTotals and 75.00 in NonGrantTotals, the calculation is 0.25.  How do I format the custom script to recognize the multiplier?

Any help would be great!

[try67]

Did you set the field's Format to Percentage? If so, the value needs to be between 0 and 1, so you should drop the multiplication by 100.

[vickicardoni]

When I set the field format to percentage that contains the custom calculation script, I receive the error "The value entered does not match the format of the field."

[try67]

You need to make sure that the value you're dividing by (the denominator) is not zero, as that is an illegal operation.

[vickicardoni]

Is there a way around that?  The formula is sort of working when the field is formatted as text with the denominator a zero.  It's just not recognizing *100.

I've also tried a simplified formula:  (GrantTotals/(GrantTotals+NonGrantTotals))*100.  But, this doesn't work either and still get the same result - 0.25 instead of 25.

[try67 · Accepted solution]

You have various errors in your code. Use this one instead:

var a = Number(this.getField("GrantTotals").value);

var b = Number(this.getField("GrantTotals").value) + Number(this.getField("NonGrantTotals").value);

if (b==0) event.value = "";

else event.value = (a/b);

If you want the result to be zero when the denominator is zero (although that's not correct), change the third line to:

if (b==0) event.value = 0;

[vickicardoni]

YESSSSSSSS!!!  It worked.  THANK YOU!!

Last question, do you happen to know how to suppress the zeros now?  This is the validate script I was using before:

if( Number(event.value) == 0) event.value = '';

[try67]

That should still work.

[vickicardoni]

I formatted the field with the custome calculation to a percentage.  I added if( Number(event.value) == 0) event.value = ''; to the validate script section but the 0.00% is still showing (even when I delete 0.00% it reappears).  If it's not possible, that's fine with me.  I'm grateful that you got the other code working!

[try67]

If the Percentage option is selected under Format the field will always show a value. It can't be blank.