Exclude values from a calculation based on drop-down selection

Question

Okay, thanks to other posts in this beautiful forum, I was able to set up fields that calculate totals based on a dropdown selection, but I'm stuck on one final hurdle and I'm hoping you can help.

I have a field for a Requested funding amount and a separate field for FinalAmount and a FundingStatus dropdown. (Requested and FinalAmount because a non-profit may request money and be approved or denied for less than they requested, but they'll need to be able to see the difference, so I can't just use one field.) I have a field that adds the requested fields, but I need for it to exclude the fields in which someone has set FundingStatus to either "Secured" or "Denied" (because the total should be the outstanding asks you still have).

Help in untangling the code for the custom calculation script would be deeply appreciated! Everything will be set up as 1, 2, 3, etc, so using a var i is working for me in the calculation of the Denied and Secured fields and I just can't quite get to how to set it up to add Requested1, Requested2, etc if FundingStatus1, FundingStatus2 != "Secured" or "Denied."

try67 · Accepted Answer

If I understood you correctly, use this code as the custom calculation script of FinalAmount (adjust the maxFields variable as needed. I assumed you had three):

var maxFields = 3;
var total = 0;
for (var i=1; i<=maxFields; i++) {
	var fundingStatus = this.getField("FundingStatus"+i).valueAsString;
	if (fundingStatus=="Secured" || fundingStatus=="Denied") continue;
	total+=Number(this.getField("Requested"+i).valueAsString);
}
event.value = total;

The second issue is caused by an incorrect fields calculation order.

kateb89895260 · Answer

Additionally, in calculating Remaing as a simplified field notation of OverallGoal - Secured, it always appears to be one calculation behind during testing (changing the funding status affects the Secured total field immediately, but it doesn't reflect in Remaining until I edit another field). I...am at a loss on how to handle this part!

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded