0

In some occasions, specially when copy-pasting, we end up having some text fields with a character 0 (nul) at the end of a string.

It doesn't show in any way when you display the data, but you do detect it when you export it.

Exported text with null char

We've tried to (at least) detect it by using the "Position" function.

However Position(text_field, char(0), 1, 1) won't find this char (it does return 0, even if the character is there).

I guess this is some kind of bug from FileMaker, but I'd like to know if anyone has found a way to circumvent it...

More info and a database sample at: https://community.claris.com/en/s/question/0D53w00005wrUMMCA2/character-0-0x0-in-text-fields

joanolo
  • 6,028
  • 1
  • 29
  • 37

2 Answers2

2

Unfortunately, the result of Char(0) is an empty string, not the expected control character.

You can generate the null character in a number of ways:

  • HexDecode ( "00" )

  • Base64Decode ( "AA==" )

  • ExecuteSQL ( "SELECT DISTINCT CHR(0) FROM SomeTable" ; "" ; "" )

or paste it into a global field and get it from there.

Once you have the character, it's easy to detect it or just substitute it out.

You may want to bypass the entire issue by allowing only printable characters - see, for example: https://www.briandunning.com/cf/1291

michael.hor257k
  • 113,275
  • 6
  • 33
  • 51
0

I run into this problem quite frequently when users try to copy-paste text from office programs into FileMaker fields on windows (my guess is that FileMaker for some reason can't handle Microsoft Office line endings properly).

The most efficient solution I found is to use auto enter calculation or script with Filter() function, in order to remove any unwanted characters.

Alterntively if you have access to plug-ins you can try using the MBS ("Text.RemoveControlCharacters") function from Monkeybread FileMaker plug-in which is uspposed to remove all characters with code 32 or lower.

matijads
  • 1
  • 1
  • The `Filter()` function does not "remove any unwanted characters". It removes all characters except the wanted ones. Which makes it problematic for solutions that support multiple alphabets. – michael.hor257k Nov 23 '22 at 16:41