Discussions

Expand all | Collapse all

Formula functions to return a text value

  • 1.  Formula functions to return a text value

    Posted 09-22-2020 12:02
    Edited by Angel R 09-22-2020 13:12

    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​​

    This looks for all variations of opening and closing html tags and replaces them with divs in order to hide them from view. This returns:

    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
    ------------------------------


  • 2.  RE: Formula functions to return a text value

    Posted 09-22-2020 13:42
    Edited by Mark Shnier (YQC) 09-22-2020 17:16
    when you use this sytax
    If ( Contains([Body Field], "From"), Left([Body Field], "From"), Length([Body Field]) > 0, [Body Field] )​​

    it means to take the left of the body field up until any of the delimiter characters  f, r, o, or  m.

    Instead, you should do a SearchAndReplace replace to replace the "from" with say a vertical pipe | which is an unlikely character to be found in emails, and then hook onto that pipe character.




    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Formula functions to return a text value

    Posted 09-22-2020 14:11
    Yes, that worked! 

    I changed the formula for 'Email Body' to:
    var text bodyField = [Body Field]; var text replaceStart = SearchAndReplace($bodyField, "From", "|"); var text replaceEnd = Left($replaceStart, "|"); $replaceEnd​


    and I was able to extract the most recent email thread. Thanks, Mark!




    ------------------------------
    AR
    ------------------------------