3 Replies Latest reply on Dec 1, 2013 7:33 PM by philmodjunk

    Parsing email using position of "@"

    TylerNelson

      Title

      Parsing email using position of "@"

      Post

           Hey! I have a Notes field in a contacts table with emails in it. Only the emails are sometimes at the beginning of the field and sometimes at the end, or even between other notes about the contact.

           Can anyone propose an expression that will parse the values to the left of the @ sign up until the first " " space and do the same for all of the values to the right of the @ sign (to the end of .com, .net or whatnot)? It goes without saying that I will co concatenate the two values with an "@" to rebuild the email. 

           I seem to be pretty close to the correct expression using the middle () function based on the position of the @ sign. But I'm not sure how to capture the values just to the left and right of the @. And I'm not sure how to get the precise number of values before hitting a " " space. Is this a fantasy or can I parse with this precision without a custom function?

           Or maybe some knows of a custom function???

            

           Thanks!!!

            

        • 1. Re: Parsing email using position of "@"
          philmodjunk

               It would be easier to deail with emails if you put them into their own fields instead of embedding them in your notes field.

               You also are referring to multiple emails--suggesting that there is an unknown number of email addresses in the field. In what form do you want to see that parsed list of email addresses? They could be entered via script into a set of related records or into a single text field separated by returns or the delimiter needed for your email program such as a semi-colon.

               Both a looping script or a recursive function can return a list of emails from your text field.

               Do you have FileMaker Advanced?

          • 2. Re: Parsing email using position of "@"
            TylerNelson

                 Yeah, I'm working in FM Pro Advanced. I have a notes field that users have unfortunately used to enter Contact attributes including emails. I made an email field and I would like to Replace Field Contents using a calculation that will extract email addresses from the Notes field. There are about 2,000 records. The vast majority of them have 1 email address... Some have 2. I'd actually like to track emails in a separate table. But first I have to extract them. I'm trying to use Replace Field Contents with an expression like this:

                  

                 Case (
                 PatternCount (Contacts::ContactAttributes ; "@")  ≥  1 ;
                 Middle (Contacts::ContactAttributes ; Position ( Contacts::ContactAttributes ; "@" ; 1 ; 1 ) -1 ; 15) 
                 &
                 "@"
                 &
                 Middle ( Contacts::ContactAttributes ; Position ( Contacts::ContactAttributes ; "@" ; 1 ; 1) +1 ; 15) 
                  
                 Only I can't hard code the 15, because I need to stop at the leading and ending spaces. Plus the above expression gives me too many @ signs. Maybe I need to do this via script or a recursive function, as you suggested. Any more advice? Thanks!!!
            • 3. Re: Parsing email using position of "@"
              philmodjunk

                   Take a look at the Brian Dunning site for custom functions. There's one called Parse that may work and there could be others. You could set up a looping script that parses out one email at a time and then uses the parsed email to create a new related record.