A calculation that drives me nuts

Questions
1

Hi there,

The title says it all.

Background

We have a FileMaker file that is the backend of a Web App made with Xojo. This Web App is used by the customers to create support tickets. These customers may access the Web App from either Windows or MacOS. On the. other side, the support people will work on these tickets mainly on MacOS but that could be on Windows.

The text fields are different whether they are created on a Mac or Windows computer because Windows and Macs do not use the same EOL sequence: Windows is char(13) & char(10) and Mac is char(10). Displaying the text on FileMaker side is always right because FileMaker adapts by itself to the EOL. But on the Xojo side, I need to use the proper EOL to display data.

The work is a little bit more complicated because the connector used, XFM, for obscure reasons strips the EOL sequence when reading form FileMaker. To go around that issue, in FileMaker I have added a calculation field that encode the EOL character(s) by doing a substitution: whatever is the EOL sequence, I replace it by "¬", and that is the calculation field I use in Xojo, where I replace "¬" by the proper sequence depending on the OS.

Sorry for the long introduction. My calculation does not work right. To understand why, I recreated it in a watch expression in the DataViewer:

Let ( 
	[
	$$Pos = Position ( COMMUNICATION::Texte ; Char(13) & Char(10) ; 1 ; 1 );
	$$EOL = If ( $$Pos > 0 ; Char(13) & Char(10); Char(10) );
	$$EOL_2 = "[" & $$EOL_2n & "]"
	];
	Substitute ( COMMUNICATION::Texte ; $$EOL ; "¬" )
	)

Pretty simple, hey ! But that does not always work. When EOL is Char(13) & Char(10), it's Ok. But when EOL is Char(10), it works only sometimes. When it fails, EOL is set by the if to "", thus there are no "¬" in the calculated field and in the Web App I get the text on a single line.

To debug I copy the plain text to BBEdit and asks to get a HEX dump. In that dump, I see the proper EOL:

image

But since $$EOL is empty, the calculation field is the same as the plain field.

What's wrong in the calculation ? Can you please bring some light ?

Thanks a lot !

2

Can you in some way "assert" that the values of $$Pos and $$EOL are acceptable?

3

Absolutely, I have replace local Let variables by global variables to be able to see what's going on. $$POS = 0 $$EOL is empty. To make sure $$EOL is empty, I added $$EOL_2 as you can see and it's value is "[]".

The only other clue I have is if the text was type in the WebApp on a Mac, the calculation works, but if the text is typed in FileMaker on a Mac, the calculation fails. Looking the two texts in Hex, there are no differences in EOL sequences.

4

Another observation.

$EOL is always empty, whether the calculation is right or wrong. The file is located on a server, I used FMP 19.4.2 and 18.0.3.317 and got the same result.

How can the substitution work when $$EOL is empty ??? Looks like I am doing something wrong in debugging.

5

Would initializing the variable(s) help?

This might sound like a crazy approach, but at least for debugging, maybe try a plug-in to run similar code or a REST service. If either / both those approaches work, you would have a lot more info to go on. Would like to help if I can.

I always try to divide and conquer in one way or another to chop down that problem space...

6

Just a note that you cited the variable name here with just one $, not $$. If you happen to do the same in the Data Viewer, it will be a gotcha.

7

The reasoning in your calc makes sense, but it does seem to make the assumption that the block of text would never have an errant CRLF in text which otherwise uses LF as the EOL.

As an example, suppose your input is:

Hello.[Char10]This is my message.[Char10]My cat is asleep[Char13][Char10]

$$POS will calculate to a value > 0

$$EOL will then calculate to CRLF.

And the substitution will only affect that trailing CRLF, and leave all the other LFs in place.

8

How about using a different approach for your calculation. Try the following:

Substitute (COMMUNICATION::Texte; "¶"; "¬")

Hope this helps.

1 Like
9

+1

The pilcrow is FileMaker's representation of EOL. It is most likely to work in most situations without needing to think about platform specifics.

Also, because it is a defined symbol you do not need to put it in quotation marks.

1 Like
10

Steve, my bad, I meant $$EOL.

11

Sometimes the simplest solutions work. Thing is your's almost work ... in fact when it was broken previously it's now working, and when it was working, it's now broken. The plot thickens :frowning: .

12

Ah! This looks like an issue I had in the past. Try this then:

Substitute (
    COMMUNICATION::Texte;
    ["¶"; "¬"];
    [Char (13) & Char (10); "¬"];
    [Char (10) & Char (13); "¬"];
    [Char (13); "¬"];
    [Char (10); "¬"]
)
4 Likes
13

Yes, yes and yes !! That works with the tickets already in the file in FileMake, wherever the text come from. Same on the Xojo side !

Thanks a million