Forum Discussion

QuincyAdam's avatar
QuincyAdam
Qrew Cadet
7 years ago

Extract Templated Text From Email Body

I have QuickBase Sync setup to add new leads from incoming emails from vendors. The vendor specific emails are templated, however, I've been unable to extract specific text from a string into a field. The email content is dumped into a field called Body.

For example, an email template may look like the following. The vendor cannot add delimiters for us, so this is what we're provided with:


STUDY NUMBER 555555

SITE LOCATION Paoli PA

PROTOCOL Asthma

NAME Jon Doe

MEDIA TYPE Facebook

MEDIA NAME Test

MOBILE PHONE 5555555555

I tried using this code, but it only outputs 5555555555:

Left(Right([Body],"STUDY NUMBER"),"SITE LOCATION")

Is there any way of grabbing values between "STUDY NUMBER" and "SITE LOCATION", and "SITE LOCATION" and "PROTOCOL", etc...?


Formulas and functions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    Sure, I game for a brain teaser.  Especially when we get to use new functions in new ways.

    I test ed this..

    Make a field called [Email Body w/ | separators]

    var text RawEmail = [email body];
    var text A = SearchAndReplace($RawEmail,"STUDY NUMBER","|");
    var text B = SearchAndReplace($A,"SITE LOCATION","|");
    var text C = SearchAndReplace($B,"PROTOCOL","|");
    var text D = SearchAndReplace($C,"MEDIA NAME","|");
    var text E = SearchAndReplace($D,"NAME","|");
    var text F = SearchAndReplace($E,"MEDIA TYPE","|");
    var text G = SearchAndReplace($F,"MOBILE PHONE","|");
    $G


    That should replace all the field labels with a pipe | character.  That is a character unlikely to be found in the real data.

    Then the formula for the Study will be
    Trim(Part([Email Body w/ | separators],2,"|"))

    The formula for Site Location will be
    Trim(Part([Email Body w/ | separators],3,"|"))

    .. etc....



    • QuincyAdam's avatar
      QuincyAdam
      Qrew Cadet
      7 years ago
      I adjusted your code to the following format:

      var text RawEmail = [Body];var text A = SearchAndReplace($RawEmail,"STUDY NUMBER","|");
      var text B = SearchAndReplace($A,"SITE LOCATION","|");
      var text C = SearchAndReplace($B,"PROTOCOL","|");
      var text D = SearchAndReplace($C,"NAME","|");
      var text E = SearchAndReplace($D,"EMAIL","|");
      var text F = SearchAndReplace($E,"MEDIA TYPE","|");
      var text G = SearchAndReplace($F,"MEDIA NAME","|");
      var text H = SearchAndReplace($G,"MOBILE PHONE","|");
      $H

      And I'm using the following formula for Media Type:
      Trim(Part([Email Body w/ | separators],7,"|"))

      However, it's displaying the Media Type and the word "MEDIA" from the next line, "MEDIA NAME"

      Is there anyway of preventing that?
  • DerekHutchinson's avatar
    DerekHutchinson
    Qrew Cadet
    6 years ago
    So I am trying to do a similar task but my email format that is received is causing me some issues. What do you guys think is a good way to obtain the 6 pieces of data highlighted in the picture? This is the full message blacked out line is company name. there is a league disculoser below a horizontal line at the bottom of the email. 
    Fields 
    Date: 04/08/19
    Time: 10:00 AM
    Government Shipped Amt: 0.00
    Government Staged Amt: 0.00
    Commercial Shipped Amt: 67,575.93
    Commercial Staged Amt: 667,065.64


    Thanks for everyone thoughts. 
    Derek
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      I could probably help you with the formula but I don't have the time now to take to work it all out.  Contact me directly on my website QuickBaseCoach.com if you want some one on one assistance where we just fire up a Go To Meeting session and bull our way though the formula together.

      Basically I would use the same technique as described above. The nuance that you have is that those last 4 numbers come is "pairs" on the same line so once you grab that text you would need to separate it based on the spaces in between.

      Alternatively, you can just plunge ahead with the technique as described above and post back if you get stuck.  It will just take a lot longer that way, but eventually we will get to the same place.