Product and version (tested in FileMaker 13 and 15)
OS and version Windows 7
When certain repetitive, contiguous text partterns are used, Substitute fails to correctly substitute for all matching instances in the string.
How to replicate
Evaluate this text function call and notice the results:
substitute ( "xbxAxAxAx" ; "xAx" ; "x" )
The expected result would be: "xbx"
The actual result is: "xbxAx" and you get an additional "Ax" for every "xAxAx" that you add to the first string. Substitute is replacing every other "xAx" with "x" apparently.
This is a simplified example of a method that I use to delete an item from a return separated list. I use returns instead of "x" and the other characters represent the return separated values.
substitute ( ¶ & List ( "Kiwi" ; "Apple" ; "Apple" ; "Apple" ) & ¶ ; ¶ & "Apple" & ¶ ; ¶ )
demonstrates the same issue. It's just less convenient to check as a watch expression in a data viewer. It produces:
I discovered this when suggesting a calculation here in the community and the original poster reported back an incorrect result. Further testing then replicated the problem. Since this has become a commonly used method for deleting list items, we need to be careful when it's possible that the value to remove appears more than once in the list and they are adjacent to each other.
I can imagine why/how this is happening and it is quite possible that Filemaker Inc. will consider this a correct result. Which will then make this a "user beware" post.
Workaround (if any)
Use loops or recursion to repeat this function call until no more instances of the specified string exist.