Question

Forms calculations are inconsistent

Updated May 8, 2018

asked on May 4, 2018

On my starting form, I have a table with a field that's hidden with CSS at the end of each row. The field contains the following formula to calculate a value:

=IF(INDEX(GLDropdown.Program,ROW()) = "101 - Unrestricted",
IF(INDEX(GLDropdown.SubProgram,ROW()) = "510 - General",
IF(INDEX(GLDropdown.GLNum,ROW())="6245 - Bank fees & service charges ",0,
IF(INDEX(GLDropdown.GLNum,ROW())="2006 - Accounts payable clearing ",0,1)),0),0)

Sometimes, this works perfectly, and sometimes it doesn't. I can't figure out why. When I test various combinations in the source fields, the field with the formula always seems to return an accurate value. However, when users submit the form, the values that should be 0 are sometimes 1, making the form travel down an incorrect path.

In some cases, none of the numbers entered on the form match the numbers being checked in the formula, so 0 should be returned. In other instances, 2006 or 6245 are used in combination with 101 and 510, which should result in 0 being returned.

No javascript is running that would modify that field.

How can I determine when and why this is happening?

0 0

Replies

replied on May 7, 2018

I've replicated your setup and I find that when using an IF statement, whether it be one if, or multiple ifs nested, Laserfiche only wants to update the value in the hidden column once. Once it has updated, it doesn't seem to update until the page reloads. I've experienced this in other cases when using If functions. Does this seem to match your findings?

An alternate idea that may work for you, but may require you to rework the values that you use (i.e. make a 3 or a 4 be the desired value instead of a 1), is to assign values to the choices. (I'm assuming you are using Dropdown fields) When you assign a value, you can use a simple sum calculation instead of an if, and it will update the totals every time correctly.

Hopefully that is a route that can work if you find you are having similar issues with IF calculations. The only slight hiccup to this idea is each choice will need a unique value. This will come into play on the GLNum column.

0 0

replied on May 8, 2018

That's not what I'm finding. When I'm testing, every time I change the values in a row, the hidden fields change as they should. But when users submit the forms, sometimes the calculations are wrong and sometimes they're right. I'm not sure why the inconsistency and I have no idea how to find out what's causing it.

While you're idea of using number values for each choice is good, it won't work in this case for a couple of reasons: 1) the combination of 3 fields is what determines whether or not the form gets routed in one direction vs another, and 2) the dropdowns are populated by a database lookup.

0 0

replied on May 8, 2018

Strange we are getting different results on the usage of IF formulas. (Perhaps there is some difference on my attempt to replicate your setup. I'm on Forms 10.2.1, if that helps.)

With mine, I can get it to change to a 1, but can never get it to change back to a 0.

0 0

replied on May 8, 2018

I'm on Forms 10.3.1. On this version it changes between 1 and 0 correctly, except on some occasions. Not sure why. But the behavior is definitely inconsistent. Sometimes a combination like 2006-101-510 routes it appropriately and sometimes it doesn't. Sometimes a combination of three numbers that don't match anything the formula is looking for returns a 1 rather than a 0. Again, I have no idea why. I'm almost at the point of removing the calculations and adding my own javascript. Calculations, even very straightforward ones, seem to be an issue on fields that are read-only and/or hidden. Maybe they work fine for fields that are always displayed and r/w. I don't know....

I'll have to submit a case for this. The problem is that I can't reproduce it on demand.

0 0

You are not allowed to follow up in this post.

Question

Question

Forms calculations are inconsistent

Replies

Sign in to reply to this post.