Please post your calculation using Substitute().
Also the pilcrow may be a literal character iconized, or the character you need to find is Char(13) or possibly Char(10) or possibly both of these. Char(13) is the Carriage Return (CR) an should appear invisible. The carriage return can also be "literal" like this:
(I typed the double-quote, typed the return key, and typed the doble-quote.) - this is also acceptible in a calculation.
This is how the value list gets back from the server.
Here's the calculation:
Substitute ( text ; ["\"" ; ""] ; ["¶" ; "¶"] ) // i know this does not make sense substituting character for character in the second parameter but I thought I give it a try.
Tried Char ( 10 ) and Char ( 13 ) to no avail. It seams the character does get iconized.
Running the function locally does return same results. As soon as the list is quoted the carriage return is converted to a sign.
Substitute ( text ; ["\"" ; ""] ; [char(182) ; "¶"] )
Thank you Wim, you're the best!
Char ( 182 ) it is!
Should have looked it up in the ASCII character map myself but never had to deal with other than 10 & 13 in filemaker.
I created the little tool in attachment few years ago to track print difference between Preview Mode and Save as PDF.
Now i use it to browse chars numbers and it is quite helpful.
I had not much skill at this time but all is relative . Anyways, please be patient.
Fmp-pdf-chars.fmp12.zip 152.5 K
FileMaker Standards has a number of custom functions that may help with this. Take a look at the list:
In your case, # and #Assign functions might do this for you.
For the full site, check out:
Thank you for the info. I'm familiar with the Custom Functions you mentioned and using them already.
The problem was more with the different behaviour of the CR sign in filemaker.
If I go Code ( "¶" ) it returns 13. But in the case when CR's are quoted they get converted to Char ( 182 ).
This led me to think that the character was irretrievable, as Wim shown, it's just another character in ASCII.
And of course it is!
The Pilcrows is a special case character. It is used as a Carriage Return and as the Pilcrow character. A quoted Pilcrow is the Character not the Return. One way to convert to "returns" is to evaluate the Quoted expression.
Evaluate ( Quote ( "1¶2¶3¶4¶5¶6¶7¶8¶9¶10¶11¶12¶13¶14¶15¶16¶17¶18¶19¶20¶21¶22¶23¶24¶25¶26¶27¶28¶29¶30¶31¶32¶33¶34¶35¶36¶37¶38¶39¶40¶41¶42¶43¶44¶45¶46¶47¶48¶49¶50¶51¶52¶53¶54¶55¶56¶57¶58¶59¶60¶61¶62¶63¶64¶65¶66¶67¶68¶69¶70¶71¶72¶73¶74¶75¶76¶77¶78¶79¶80¶81¶82¶83¶84¶85¶86¶87¶88¶89¶90¶91¶92¶93¶94¶95¶96¶97¶98¶99¶100¶101¶102¶103¶104¶105¶106¶107¶108¶109¶110¶111" ) )
Returns the original List() values with Returns between them instead of Pilcrows.
BTW, you can use this technique to pass text between scripts that includes Pilcrows. First convert all Returns to Pilcrows. Then assemble the Script parameter using your custom Value separator, even a real return works. Use Get Value ( Substitute ( Script Parameter ; "my separator" ; ¶ ) ; NN ) to pull the Text Block including Pilcrows (where "my separator" is some value not included in the text and NN is the parameter number. Note that the Pilcrow is unquoted so it is treated as a Return). Then Evaluate the text block to restore the Returns.
I am having a somewhat similar issue and thought it would make sense to post it here instead of a new post.
I have a text auto enter calculation that has an executeSQL statement to show all primary key values in a related table.
similar to ... executeSQL ( "select pkkeyOfRelatedTable from relatedtable where fkkey = ? " ; "" ; "" ; localtable::pkkey )
this gives me a return separated list of the primary keys in a text field.
I then am using a script to go to a layout that only has this field on it and am using COPY ALL RECORDS ... to copy all the values of this one field from the current found set and paste it into a global field.
Finally I use a patterncount IF statement to see if a specific value is in this newly created global value.
What's odd is that it looks like the carriage return is not being recognized sometimes.
In fact, in troubleshooting this ... after I paste the COPY ALL RECORDS ... I took that value into and pasted it into Microsoft WORD and the values in between the SQL rows show up as a "Forced end-of-line" (shift + enter) and not a paragraph return.
I also tried a simple SUBSTITUTE in script debugger and it doesn't recognize the values as a paragraph return. When I enter Substitute ( SCENE::gText; "¶" ; "|" ) the results do not change ... no pipes are entered.
It's really odd, because if I take the original EXECUTESQL results and try the same substitute function it works perfectly. Something with the COPY ALL RECORDS is changing the paragraph marks to some other CHAR character.
Any thoughts on what this could be??
thank you in advance!!
if you look at the attached image ...
I had two records ... the first value had 162150 and 168739 in the execute SQL results, separated by a carriage return. The second record has 161013 and 162150 and 163040 and so on.
I then substitute ¶ for | and you can see below that filemaker only recognized the carriage return between the 2 records. the other values are not recognized as ¶, but something else. What is it??
Maybe use "|" for the 'row separator' (and possibly the column separator)?
" SELECT ....
WHERE ... "
; "|" ; "|" )
The COPY ALL may be experiencing the same behavior one gets with exporting a field with embedded returns? (returns are changed to VT).
Text fields are copied in tab-delimited format, without styles, in the order they appear in the record layout. Text in container fields is also copied. (This script step is equivalent to choosing Copy from the Edit menu while holding down the Shift key (Windows) or Option key (OS X) when there is no active field.)
Thank you Beverly for your help!
I was actually able to find a work around this morning by copying / pasting the results in the data viewer to get the actual RETURN character that is being outputted. I then created the following substitute command from it. I still don't know what that exact character is but it is some kind of RETURN that is not recognized by FileMaker as a Paragraph return.
I wish I could capture it as a CHAR character since the solution is a bit hokey, but it works ... so I can't complain loudly.
thanks again for your help.
"I wish I could capture it as a CHAR character..."
Not sure why you think you can't.
You already captured the character in as noted in your substitute expression above.