I'm struggling to understand an intermittent problem using the substitute function.

I have a script that works on a single record extracting data into a global text field, The script adds the item description on the first line, a list of serial numbers on the second line, and a order reference on the third line, so the final text field might look something like this.

Bds-C-Z-2-S-351/364-0.8-Ferrule

s/n 176613.176666.176619.176616.176615.176617.187934.187937.187939.187941

w/o 8220623

When I initially add the item description to the text string, I apply the Proper function to it to to ensure that the item description has a consistent look:

set field(WORKS ORDER::_gClip; Proper(WORKS ORDER::Item Description) & "¶")

I need two have two different versions of this field, one with the part number and one without. To create the one without the part number, I use the substitute() function on this text field viz:

set field[WORKS ORDER::_gClip; Substitute(Substitute(WORKS ORDER::_gClip;"¶¶";"¶");Proper(WORKS ORDER::Item Description);"")]

This works almost every time, but occasionally the substitute process does not remove the part number. When this happens, I've checked the Item description for obvious extra spaces, tab or return characters before or after the description but it seems clean.

Can anyone suggest why the substitute function might sometimes fail?

Brian

Substitute is case sensitive. I'd make it all uppercase or lowercase instead of proper case.

Also, there is no need to nest the substitutes. Instead enclose multiple substitution expressions in square brackets:

Substitute( field ; [ "something" ; "nothing" ] ; [ "gone" ; "forgotten" ] )

Or in your example:

Substitute( Lower( WORKS ORDER::_gClip ) ; [ "¶¶" ; "¶" ] ; [ Lower( WORKS ORDER::Item Description ) ; "" ] )