Serious bug in Let function when changing field name?

[jkluchnik]

I have posted this on the FMI website but figured it is serious enough that it deserves a post here as well. The short version of this is:

There are two fields: "Field A", a text field, and "Let Field" a calculation field.

Let Field contains the following calculation:

Let (

[

~fieldName = If (Field A = 1; 1; 0)

] ;

~fieldName

)

After doing this, the name of "Field A" is changed to "Field B". The field name inside the let function DOES NOT CHANGE.

Even more intriguing is, after changing the name of the Field A, if you open the Let Field and then try to close it by clicking "ok", it puts up the message that the field "Field A" cannot be found. However, if you click cancel, it permits Field A to remain even though it does not exist.

It seems to be a problem wrapping Field A in the IF statement. If the Let Field is changed to:

Let (

[

~fieldName = Field A

] ;

~fieldName

)

and then you change Field A to Field B, the Let field variable WILL change to ~fieldName = Field B.

In a third field called "Calc" I so a straight calculation with an IF statement:

If (Field A = 1; 1; 0)

This also works and changes when you change the name of Field A.

I believe this is a serious bug. Or, am I about to have a "duh" moment? I am ready to be schooled!

PS. I have attached a file with the three fields if anyone want to verify. Just change the name of Field A and see what happens in the other two fields.

Let Function Test.fp7.zip

[comment]

Interesting. However, it seems the bug doesn't affect the actual results of the calculation - only the way the formula is being displayed. So I am not sure how "serious" it is.

It seems to be a problem wrapping Field A in the IF statement.

I think it appears when using equality in the test parameter of If().

Another reason to always use Case(), BTW.

[jkluchnik]

Good point about the calculation remaining correct. I didn't realize that until you mentioned it.

As for Case, I just tried that and you are correct in that it works where IF doesn't in this case. Other than this issue, why do you say it is always better to use Case()?

You are also correct about equality. Changing to anything but "=", the problem goes away.

Finally, as to the seriousness, I guess it is just a tad less serious as the calculation is correct. However, in the solution I have built, which is EXTREMELY complex, it is a nightmare when looking into the code and seeing things referenced incorrectly. In may case, I also added a field with the name that was used previously, so it looked like the same field was there twice and therefore is very confusing. An audit of the formulas would indicate that there is a problem. We saw that in this case the result was the same, but since there is a problem that can be seeing, I just don't trust that it will ALWAYS come up with the correct results.

[comment]

why do you say it is always better to use Case()?

Because Case() does everything If() does AND it can use multiple test/result pairs. IMHO, the If() function should be regarded as legacy for compatibility with older versions. Perhaps this discovery of yours shows I'm not the only one to think so.... :tongue:

[jkluchnik]

I see your point. I was just wondering if there were inherent problems with IF(), other than its limited usefulness.

[comment]

I was just wondering if there were inherent problems with IF()

Not until now...

[jkluchnik]

:laugh:

I just tried to send you a private message but the system says Comment cannot receive any more messages. Could you please email me so I can contact you off the forum?

[LaRetta]

Another reason to always use Case(), BTW.

I do not agree. There are reasons to use Case() but using this bug as another reason is just not right. It is like a person telling a doctor that it hurts when they lift their arm and the doc responds with, "Well then don't lift it." Sure ... it solves the issue but it limits the functionality of that arm and that function and I, for one, think we should call it as it is: it is a bug. And the 'seriousness' would depend upon whether I am someone sitting there with solutions which have If() statements which are throwing me off (either in my documentation or perception of fields being used - and I do not know about it). Or it is like BruceR telling me not to use lines because lines are never accurate (although few people are aware of it) and to use rectangles instead.

Thank you for making this bug public, kluchnik. I know it has cost you many hours to figure it out and it has/will cost others many hours stumbling upon it themselves as well. I guess when you get into the long line of bugs in FileMaker then this may be considered small in proportion but sometimes the filemaker community reminds me of a mother who refuses to ever think their child does anything wrong; mother gets defensive towards others about it and dismisses their child's behavior. It is called enabling.

I would suggest that there are (probably) millions of individual If() calculations in all the solutions out there . This function should work as intended or it should be dropped. No in-between.

[comment]

I don't have your e-mail, but I have sent you a PM with mine.

[comment]

This function should work as intended or it should be dropped.

I agree. However, since I believe Case() should be used exclusively regardless of the bug, it does add support to such advice on a practical level.