Code to check that total does not exceed 100%

[diane_ngf]

I have a form that I need to enter percentages assigned for one to four people, the total cannot exceed 100%.  Each field checks the field above it, adds the percentages, and an error message pops up if the total is over 100%.

Example 1:

Person 1          25%     [under 100%, proceed to next field]

Person 2          25%     [under 100%, proceed to next field]

Person 3          25%     [under 100%, proceed to next field]

Person 4          35%     [over 100%, error message "Total cannot exceed 100%"]

Example 2:

Person 1          25%     [under 100%, proceed to next field]

Person 2          80%     [over 100%, error message "Total cannot exceed 100%", stop checking]

Person 3         

Person 4         

I'm using a MAC but this should work on Windows as well.

Thanks for any help you can give me!

DP

[diane_ngf]

I entered the following for Person 1 and it works, but when I try to check Person 2 it doesn't create the pop-up.

Person 1 - THIS WORKS

var v1 = Number(this.getField("Percentage_1").valueAsString);

var v2 = Number(this.getField("Percentage_2").valueAsString);

var v3 = Number(this.getField("Percentage_3").valueAsString);

var v4 = Number(this.getField("Percentage_4").valueAsString);

if(v1>100) {

app.alert("Total cannot exceed 100%");

} else {

event.value = v1;}

Person 2 - THIS DOESN'T WORK

var v1 = Number(this.getField("Percentage_1").valueAsString);

var v2 = Number(this.getField("Percentage_2").valueAsString);

var v3 = Number(this.getField("Percentage_3").valueAsString);

var v4 = Number(this.getField("Percentage_4").valueAsString);

if((sum(v1+v2))>100) {

app.alert("Total cannot exceed 100%");

} else {

event.value = v2;}

[try67]

Why not simply create a Total field that adds the values of all four fields, and then displays an error message if that value is larger than 100?

Also, you should keep in mind that the values of percentage fields is between 0 and 1, normally, not 0 and 100...

[diane_ngf]

That's a great idea but I'm limited on space on the form. My thinking was to catch the error as they enter the information but your suggestion would make it a lot easier.  Maybe I can hide the field.  I'll let you know if it works   Thanks!

My coding skills are minimal at best.  I looked at some of the posts and found the code I used above.  I appreciate you all being out here to help!

[try67]

Yes, the field can be hidden.

For the calculation you can use the built-in Sum command. No script required.

For the validation you can use this code:

if (event.value>1) app.alert("Error! The sum of the fields must not exceed 100%.");

[diane_ngf]

Thanks!  We're getting there but I must be doing something else wrong.

I tried the code and if the numbers entered start out under 100 it works, but if any one of the numbers goes over 100 it throws the error even after the number is corrected.

For example, I entered the first value as 125%, got the pop-up, I changed it to 100% and got the pop-up again.  After that it let me go to the next field.  But when I entered a number in there I got the pop-up again. So it's not clearing the error once I correct the number.  Is there a "clear" I can add to the formula?

BTW, I'm using ==> if (event.value) event.value += "%"; in the custom format script to add the % sign.

[diane_ngf · Accepted solution]

I read a few posts about entering 50 and seeing 5,000% when formatting it as a percent.  So I found the code below that took 50 and made it 50% without having to enter it as .50.  For others who need to know this, it's in the Format tab, Custom, Custom Format Script.

if (event.value) event.value += "%";

When I couldn't get your formula to work I changed >1 to >100 and it worked perfectly.

     if (event.value>100) app.alert("Error! The sum of the fields must not exceed 100%.");

Thank you for your help!  You're a genius