Email address parse

[maykel]

Hi, I am still very new with FM. I just bought the Mail to FM Importer so I can archive all my email from time to time. I also want to expand the capabilities in the future.

Here's the question: How can I convert/parse the "From" or "To" field from "John Doe , Jane " into "[email protected]¶[email protected]"?

So basically extracting the email addresses only and separating them with a return. If possible I also want to make them automatically inserted to a repearting field.

I've tried to seach the forum with no luck. Thanks.

[CobaltSky]

Hello maykel,

You can parse the addresses out with a formula such as:

Let([

p1 = Position(To; "<"; 1; 1) + 1;

p2 = Position(To; ">"; p1; 1)];

If(p1 > 1; Middle(To; p1; p2 - p1))

)

and if you really want them in repetitions, you could set up a conventional calculation field (with a result type of text) with a formula along the lines of:

Let([

rN = Get(CalculationRepetitionNumber);

Tx = Extend(To);

p1 = Position(Tx; "<"; 1; rN) + 1;

p2 = Position(Tx; ">"; p1; 1)];

If(p1 > 1; Middle(Tx; p1; p2 - p1))

)

...which will pull successive addresses from the TO field into each repetition.

Unfortunately, an auto-enter calc will not trigger in repetitions (ie after the first) unless you use a work-around to "trick" it to do so.

Alternatively, however, you could set up a script to break out the email addresses into conventional repetitions fairly easily, using a variant of the first formula above. :wink2:

[comment]

Ray had posted ahead of me, but since he did not include a script, I decided to post my version anyway. Roughly:

# MAKE A CR-DELIMITED LIST

Set Variable [ $list ; Substitute ( From ; "," ; ¶ ]

#

# LOOP THRU THE LIST

Set Variable [ $i ; 1 ]

Loop

Exit Loop If [ ValueCount ( $list ) < $i ]

# GET CURRENT VALUE

Set Variable [ $currentValue ; GetValue ( $list ; $i ]

# SET FIELD TO EXTRACTED E-MAIL

Set Field ( RepeatingFrom [$i] ; Let ( [

start = Position ( $currentValue ; "<" ; 1 ; 1 ) + 1 ;

end = Position ( $currentValue ; ">" ; start ; 1 )

] ;

Middle ( $currentValue ; start ; end - start )

) ]

#

Set Variable [ $i ; $i + 1 ]

End Loop

#

Commit Records

I question the wisdom of using a repeating field to store the extracted e-mails. For a simple solution I would use a carriage-return delimited list in a text field; for a more elaborate one - a related table of e-mails, with a separate record for each.

Edited October 4, 2007 by Guest

[maykel]

Ray, comment, thanks for your responds. I will give them a try, hope they solve my problem.

Well the reason I want to put in a repeating field is because I want to make a relationship between the imported emails with contact management, so it can become a very simple CRM. The only way I can make a direct relationship between contacts and emails tables is by making the To a repeating field, for emails which have more than one address destination.

If you guys have a suggestion for a more proper way to do this, I would really appreciate it.

[CobaltSky]

...The only way I can make a direct relationship between contacts and emails tables is by making the To a repeating field, for emails which have more than one address destination.

I can assure you that you're mistaken about this, maykel.

If you store the email addresses as a carriage-return delimited list in a text field, as recommended by comment, you'll be able to use the field as the basis of a relationship to an email field in the contacts table (each line in the CR-delimited field will be separately matched to records in the related table). :wink2:

[comment]

If the purpose is to link posts to contacts by e-mail addresses, then a repeating calculation field, as suggested by Ray, is probably the easiest way.

LOL, what a switch of positions!