Filemaker conundrum

April 14, 2009 7:14 PM

Anyone out there good with Filemaker? I need to do something to email addresses and can not get my arms around this one...

I have a Filemaker database with email addresses in it. I need to take an email address such as [e-mail] and strip out only the "mac" part so it is in a separate field by itself. For the life of me I can not figure out how to get this right.
I've tried the Right function, Position, etc. figuring I could trim the end but then I have to deal with top level domains and country codes that are not always three letters. I think I might be making this too hard. Any ideas are welcomed!

posted by bkeene12 to Computers & Internet (6 answers total)

I have used filemaker for a project, but not for a task like this. Based on my other (non filemaker) experience where I had to massage textual data, a plugin like this regex plugin would be the way to go. If you have the patience to figure out how to do a regex, or have someone help you make one.
posted by idiopath at 7:19 PM on April 14, 2009

Oh, it looks like filemaker has regex built in now.
posted by idiopath at 7:22 PM on April 14, 2009

Best answer: I just solved it, and this picture will show you how I did it. You could do it with a single formula, but for clarity I broke it into three fields.

Address = the email addresses
startPoint = the place at which you want to start looking for text
endPoint = the place at which you want to stop looking for text
Answer = The formula that will give you "mac" or "gmail" or "yahoo"

If you'd like to email me, go ahead and I'll send you the FMP file itself. Might be easier to work with than a static picture.
posted by crapples at 8:06 PM on April 14, 2009

You could also do it in a single calculation.

Define a new calculation field named ParseEmailAddress (text result). Copy and paste the following into the body of the calc:

Let (
[
AtSign = Position ( MyAddressField ; "@" ; 1 ; 1) ;
PeriodAfterAtSign = Position ( MyAddressField ; "." ; AtSign ; 1)
];

Middle ( MyAddressField ; AtSign +1 ; (PeriodAfterAtSign -1) - AtSign)
)


Substitute your email address field for the MyAddressField field in the above calc.
posted by mosk at 1:50 AM on April 15, 2009

Response by poster: Okay folks, I sat down and started plowing through this over the weekend. I loved mosk's suggestion as it was one shot and finished solution. I plugged in my field name and got the following error message:

"A number, text constant, field name or "(" is expected here."

The first bracket after "Let (" was highlighted. I never managed to figure out what it really wanted. I am using FM7 if it makes a difference.

Crapples won the day with his three step approach. It worked like a charm. Thanks to all of you for taking some time to help me out!
posted by bkeene12 at 7:16 PM on April 19, 2009

Glad you found a solution that worked for you.

Yeah, FMP7 had a bug that prevented it from recognizing the Let function if that function was formatted with hard line breaks. Very happy this bug was fixed in a later release.

FWIW, I copied the calc in my first answer directly from FMP10, where it worked fine, but pasting it into FMP7 gave me the same error it gave you. FMP7 only accepted the calc after I collapsed the line breaks:

Let ( [ AtSign = Position ( MyAddressField ; "@" ; 1 ; 1) ; PeriodAfterAtSign = Position ( MyAddressField ; "." ; AtSign ; 1) ]; Middle ( MyAddressField ; AtSign +1 ; (PeriodAfterAtSign -1) - AtSign) )

You have a working solution so you can ignore this, but I'd like to leave it as a footnote to my the previous post.
posted by mosk at 1:47 AM on April 21, 2009