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