Hi, I spent about 2-3 hours puzzling out a calculation to take the "From:" header of an email message imported into Filemaker and clean it up so that ONLY the email address remains. It's useful when you need to make a relation that matches based on email address. Here it is. Comments or suggestions welcomed.

------Start Calculation---------

/* This calculation examines the From: header of an email message and cleans it, yielding JUST the email address. It removes full names with or without quotation marks and spaces in them, before or after the email address, and also removes quote marks and < > symbols. My purpose for the calculation is to facilitate clean relational matching between a table containing 1 email message per record, and a list of clients' email addresses. It also lowercases the email address for sake of consistency. */

/* copyright 2005 Gilbert Osmond, NO RESTRICTIONS on use, reproduction, sale, or incorporation into other code. */

Case (

/* If From: line contains no spaces, there's nothing fancy to do; just eliminate extra characters */

PatternCount (From; " ") = 0; Lower (Substitute (From; ["<"; ""] ; [">"; "" ] ; ["""; ""] ) ) ;

/* If From: line contains one or more spaces after the email address, eliminate everything following the email address. */

( Position (From ; " "; ( Position (From; "@"; 1; 1)); -1)) = 0 ;

Lower ( Substitute (

Left ( From;

(Position (From; " "; 1; 1))

)

; ["<"; ""] ; [">"; "" ] ; ["""; ""])

)

;

/* Otherwise, eliminate everything leading up to (before) the email address. */

Lower ( Substitute (

Right ( From;

(Length (From) -

(Position (From ; " ";

(Position (From; "@"; 1; 1));

-1))

)

)

; ["<"; ""] ; [">"; "" ] ; ["""; ""])

)

)

--------END CALCULATION

Edited December 17, 200520 yr by Guest

Wouldn't this do it as well???

Substitute (

LeftValues (

Substitute (

Replace ( theHeader ; 1 ;

Position ( theHeader ; "Return-path: <" ; 1;1) + 14 ; "" ) ; ">" ; "¶" ) ; 1 ) ;"¶";"")

--sd

or this:

Let([

field = theHeader;

pos@ = Position ( field ; "@" ; 1 ; 1 );

startEmail = Position ( field; "<" ; pos@ ; -1 ) +1;

endEmail = Position ( field ; ">" ; pos@ ; 1 )

];

Middle ( field ; startEmail ; endEmail - startEmail )

)

Yeah, my bad. I had searched other forums (within FMforums.com) extensively last night for a solution, but failed to come across this complete & more comprehensive solution to the problem:

http://fmforums.com/forum/showtopic.php?tid/78240/post/80167/hxl/email/#80167

(Topic #78240 in this forum.)

It has a link to a handy demo file that easily and seamlessly converted to FP7/8 format; covers all bases.