Formula functions to return a text value
I have three fields that each return the body of an html email. The first, which is named [Body], returns the entire email with html tags included:
<html><head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta content="text/html; charset=us-ascii"><meta name="Generator" content="Microsoft Word 15 (filtered medium)"><base href="https://ethos.quickbase.com/db/"><style> <!-- @font-face {font-family:"Cambria Math"} @font-face {font-family:Calibri} @font-face {font-family:"Helvetica Neue"} p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; font-size:11.0pt; font-family:"Calibri",sans-serif} a:link, span.MsoHyperlink {color:#0563C1; text-decoration:underline} .MsoChpDefault {font-size:10.0pt} @page WordSection1 {margin:1.0in 1.0in 1.0in 1.0in} div.WordSection1 {} --> </style><style> <!-- p.MsoNormal {margin-left:7.5pt} --> </style></head><body lang="EN-US" link="#0563C1" vlink="#954F72" style="margin-left:7.5pt; margin-top:7.5pt; margin-right:7.5pt; margin-bottom:7.5pt">
No worries, just a recommendation
From: itsupport @ xxxxxxxx.com (via Quick Base) <notify@quickbase.com>
Sent: Tuesday, September 22, 2020 8:49 AM
To: jon live <jlive@xxxxxx.com>
Subject: Angel Rodriguez just sent you a message for Ticket #20200922-0759-JL-430
[EXTERNAL SENDER]
Hello,
Angel Rodriguez responded to your message with:
Hi Jon. No problem. Thanks for pointing that out, by the way.
If you have any questions about this message, please respond to this email addressed to ITSupport@xxxxx.com.
Thank you,
IT Support
</body></html>
The second field, named [Body Field], returns [Body], but replaces the html tags with <div> tags in order for them not to show in the field. Here's the formula in [Body Field]:
var text htmlField = [Body];
var text htmlStart = SearchAndReplace($htmlField, "<html", "<div");
var text headStart = SearchAndReplace($htmlStart, "<head", "<div");
var text metaStart = SearchAndReplace($headStart, "<meta", "<div");
var text baseStart = SearchAndReplace($metaStart, "<base", "<div");
var text startStyle = SearchAndReplace($baseStart, "<style", "<div");
var text excStart = SearchAndReplace($startStyle, "<!--", "<div ");
var text excEnd = SearchAndReplace($excStart, "-->", "</div>");
var text endStyle = SearchAndReplace($excStart, "</style", "</div");
var text endHead = SearchAndReplace($endStyle, "</head", "</div");
var text startBody = SearchAndReplace($endHead, "<body", "<div");
var text endBody = SearchAndReplace($startBody, "</body", "</div");
var text endHtml = SearchAndReplace($endBody, "</html", "</div"); $endHTML
No worries, just a recommendation
From: itsupport @ xxxxxxxx.com (via Quick Base) <notify@quickbase.com>
Sent: Tuesday, September 22, 2020 8:49 AM
To: jon live <jlive@xxxxxx.com>
Subject: Angel Rodriguez just sent you a message for Ticket #20200922-0759-JL-430
[EXTERNAL SENDER]
Hello,
Angel Rodriguez responded to your message with:
Hi Jon. No problem. Thanks for pointing that out, by the way.
If you have any questions about this message, please respond to this email addressed to ITSupport@xxxxx.com.
Thank you,
IT Support
The last field [Email Body] takes [Body Field] and is supposed to cut the most recent message from the thread starting at the "From" (From: itsupport@...) and grabbing all characters to the left of that. So I would want to return:No worries, just a recommendation
with the formula:
If (
Contains([Body Field], "From"), Left([Body Field], "From"),
Length([Body Field]) > 0, [Body Field]
)
But, for some reason I can't seem to figure out, I'm returning:
<div http-equiv="C
So the html, even the divs I'm running the search and replace on along with some of the html are still showing up. Any idea why this is happening or how I could use a text function to grab the text before "From" without all of the additional html? Thanks.
------------------------------
AR
------------------------------
