I was able to sort this out by creating the Formula-Text field as follows:
// single quotes and double quotes must be "escaped" so they don't interfere with the JSON data formatting
var Text DOUBLEQUOTE = "\"";
var Text SINGLEQUOTE = "'";
var Text ESCAPEDSINGLEQUOTE = "\\'";
var Text ESCAPEDDOUBLEQUOTE = "\\\"";
var Text ENCODEDFORSINGLEQUOTE = SearchAndReplace([Complaint Summary], $SINGLEQUOTE, $ESCAPEDSINGLEQUOTE);
SearchAndReplace($ENCODEDFORSINGLEQUOTE, $DOUBLEQUOTE, $ESCAPEDDOUBLEQUOTE)
Now, the web hook is able to transmit data with any pattern of inline single or double quotes.
------------------------------
Rick Putnam
------------------------------
Original Message:
Sent: 01-13-2021 08:43
From: Rick Putnam
Subject: Escaping a single quote in WebHook JSON
I have a Web Hook that is sending JSON data to an end point external to Quickbase. If I embed an escaped single quote in one of the fields it works fine:
{
"ComplaintSummary" : "This works fine with the single \'quotes \' escaped"
}
When I use a Formula-Text field to insert the escape characters with the SearchAndReplace function, the web hook fails. The Formula-Text field result is the same as above. As far as I've been able to tell, QB never sends the data.
Thanks in advance for any assistance.
------------------------------
Rick Putnam
------------------------------