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....
nice solve!