Extract Templated Text From Email Body

Question

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

QuickBaseCoachD · Answer

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

Sign up

Login to the community