Percentage table must total 100% and has a limit on how many options can be entered on

[RQ82]

Hello!

This is a tricky one at least for me to explain so please be patient as I try to explain this. I need to create a percentage table with fields that:

Must equal 100% (not less than, not more than). This could mean only one option is entered that must be 100%, or multiples (up to 14) that combine to total 100%.

Of the total number of fields/choices, only 14 of the total number of fields can be entered on. So, if a user has entered 14 of the fields in the table, they cannot enter on any other fields. They would need to remove a field entry (or entries) to enter on others (but no more than 14).

I'm trying to figure out:

1.) Is it even possible to only have 14 fields max be entered on, and if so, what happens if someone tries to enter on more? Are they deactivated, do they throw an error and clear that value out? I'm struggling to figure out how to solve this request. 

2.) How can the Total at the bottom of the table calculate based on 14 fields when there are actually more than that? It currently calculates the total of all the fields.

3.) Is there a way to conveniently have the form throw some sort of message when they don't total to 100% exactly (I can't think of a way to do this).

I am also open to suggestions in the event there is a simpler way to achieve this. I just can't seem to sort out the best way to make this work and could really use some help.

Appreciate and am grateful for any help... and please note that I am far from an expert and am doing my best to figure things out as I explore.

Thanks

I cannot figure out how to achieve 

[try67 · Accepted solution]

You need to think about it carefully. What does it mean that a sum field "must equal" 100%? Does it mean you reject other values if they don't add up to 100? If so, how would the user be able to enter anything, except for "100"? Imagine they want to fill in 20 and 80. They enter 20, and it gets immediately rejected because the sum is not 100 (it's just 20 and nothing)... What now?

[try67 · Accepted solution]

Move the call to the FundCountValidation function to the field's Validation event and remove the On Blur code.

[try67 · Accepted solution]

You need to think about it carefully. What does it mean that a sum field "must equal" 100%? Does it mean you reject other values if they don't add up to 100? If so, how would the user be able to enter anything, except for "100"? Imagine they want to fill in 20 and 80. They enter 20, and it gets immediately rejected because the sum is not 100 (it's just 20 and nothing)... What now?

[RQ82]

Very good point. I'm going to have it not exceed 100%, so if someone enters a value that when combined with the other fields will clear the last entered one if it makes the total greater than 100. I also have the max number of fields that can be entered working using a document level javascript. Happy to share if anyone is at all interested.

[try67]

That makes more sense, and if you already have a script that the number of fields that are filled-in it should not be too difficult to adjust it to limit their total value, too. Can you share what you already have?

[RQ82]

Here it is. I'm not sure it's the best way to achieve maximum fields as well as the % total not exceeding 100 but it's what I have been able to figure out as I work through it. It will currently only allow for 14 fields to be entered and if the total sum of any field/fields is greater than 100 it will clear the entry.

[try67]

It's a good start, but try this code:

function FundCountValidation() {
    var fields = ["P1: 2. Guaranteed Interest Account", "P1: 2. Money Market Fund", "P1: 2. Global Bond Fund", "P1: 2. US Bond Fund", "P1: 2. Inflation Strategy Fund", "P1: 2. Inflation-Linked Bond Fund", "P1: 2. Global Value Stock Fund", "P1: 2. Global Growth Stock Fund", "P1: 2. Global Small Cap Stock Fund", "P1: 2. Short Duration Fund", "P1: 2. High Yield Bond Fund", "P1: 2. Emerging Market Bond Fund", "P1: 2. Emerging Market Stock Fund", "P1: 2. US Stock Index Fund", "P1: 2. Global Stock Index Fund", "P1: 2. US Growth Stock Fund", "P1: 2. US Value Stock Fund"];
    var count = 0;
	var total = 0;
    for (var i = 0; i < fields.length; i++) {
        var v = (event.target.name==fields[i]) ? event.value : this.getField(fields[i]).valueAsString;
        if (v!=="") {
            count++;
			total+=Number(v);
        }
        if (total>100) {
            app.alert("The total percentage cannot exceed 100%");
            event.rc = false;
			return;
        }
        if (count === 14) {
            app.alert("Sorry, only a maximum of 14 funds can be selected");
            event.rc = false;
			return;
        }
    }

}

I've modified it so that it can be used as a real Validation script, so place a call it under all the percentage fields and it will not only alert the user of the issue, but will also reject the value they entered.

[RQ82]

Thanks for this! I'm just trying to figure out what this is doing that what I had isn't? I can't see a difference in how it works, but it may be that I've done it wrong. I replaced the code with what you supplied. On each of the fields I still have:

Run a JavaScript - On Focus:

FundCountValidation();

Run a JavaScript - On Blur:

var totalField = this.getField("P1: 2. Total").value; if(totalField > 100) { app.alert("The total percentage must be equal to and cannot exceed 100%"); this.getField("P1: 2. Field Name").value = ""; }

Am I leaving these on each of the fields still? Sorry... just trying to follow along.

Thanks

[try67 · Accepted solution]

Move the call to the FundCountValidation function to the field's Validation event and remove the On Blur code.

[RQ82]

Got it! That's much cleaner and saves a lot of steps on each of those fields. Thanks very much... always learning and always appreciate the help!

[RQ82]

Ok, so I ran into one last thing. Even though it specifies 14 max fields, it actually throws the alert after 13 fields. Is this to do with the "Total" field at the bottom which is just to show the combined numbers? I'm wondering how to fix this but can't seem to wrap my head around it.

Thanks in advance!

[try67 · Accepted solution]

You have set the alert to show when 14 fields are filled in. If you want to allow a maximum of 14 then change this line:

if (count === 14) {

To:

if (count>14) {

[RQ82]

Thanlk you so much. That completes this adventure 🙂