Formula e-mail adress validation

[Jukkie]

Hello,

I need to validate my e-mail field. I want this to do by a calculation, but I don't know how to do this.

The e-mail adress should look like [email protected] the it is a valid e-mail adress.

Hopefully there is someone how can help me.

greetings Jukkie

FileMaker Version: 6

Platform: Windows XP

[Ugo DI LUCA]

Well, I'm far from being an expert on email structure, but from what I can see, email adresses would follow these conditions :

1. There must be one occurrence of "@" only

2. There can be more than one ".", but then one must follow the "@" without being the final character of the email adress.

3. There can't be a succession of "@."

Based on that

(Patterncount(email,"@") xor Patterncount(email,"@")>1)

and

Patterncount (email,".") and not Right(email,1) and Position(email,".",Position(email,"@",1,1),1)>0

and

not Patterncount(email,"@.")

Hope it works. 'd be glad to know the correct answer as I'd probably need this feature too in a while...

FileMaker Version: 6

Platform: Mac OS 9

[Ugo DI LUCA]

I'm not quite sure about that one, but there should be only one word following the last dot, so :

(Patterncount(email,"@") xor Patterncount(email,"@")>1)

and

Patterncount (email,".") and not Right(email,1) and Position(email,".",Position(email,"@",1,1),1)>0

and

not Patterncount(email,"@.")

and

WordCount(Middle(email,POSITION(email,".",POSITION(email,"@",1,1),1),Length(email)))=1

FileMaker Version: 6

Platform: Mac OS 9

[Ugo DI LUCA]

Oops, As we'd be looking to the last occurrence of "." in this adress, then the Wordcount() part should look like

WordCount(

Middle(email,

POSITION(email,".",

POSITION(email,"@",1,1),

Patterncount(Middle(email,Position(email,"@",1,1),Length(email)),".") ),Length(email)))=1

to be added to :

(Patterncount(email,"@") xor Patterncount(email,"@")>1)

and

Patterncount (email,".") and not Right(email,1)="." and Position(email,".",Position(email,"@",1,1),1)>0

and

not Patterncount(email,"@.")

FileMaker Version: 6

Platform: Mac OS 9

[Fitch]

PatternCount( email, "@" ) = 1 and -- there's only one @

PatternCount( email, "." ) > 0 and -- there's at least one dot

PatternCount( email, ".." ) = 0 and -- no more than one dot in a row

Right( email, 1 ) <> "." and -- can't end in dot

Left( email, 1 ) <> "." and -- can't start with dot

Right( email, 1 ) <> "@" and -- can't end in @

Left( email, 1 ) <> "@" and -- can't start with @

Position( email, ".", Length( email ), -1) > ( Position( email, "@", 1, 1) + 1 )

-- the final dot comes at least one character after the @

That's the easy part.

The next ( tricky ) part is checking that each character is a member of the set:

abcdefghijklmnopqrstuvwxyz1234567890.@

Believe it or not, that's less work than checking for every possible character that's NOT allowed.

PS: Ugo, your rule 1. makes your rule 3. redundant.

[Fitch]

I've also seen examples of validation that try to parse the TLD (top level domain), e.g. .com, .net, etc. This is not very practical with all the possible country codes, but you could test for length of 2 or 3. But now there are 4-letter domains such as .aero, .info, and longer such as .museum. So this method will work most of the time, just be aware of the pitfalls.

[Fitch]

You might also look at a plugin such as SMTPit, which can validate addresses.

Or, You could integrate with a service via XML, see

http://transfer.filemaker.com/collection/ServiceObjects.zip and

http://www.serviceobjects.com/products/dots_email.asp

IOW, instead of parsing the text of the field, you take the domain info from the field and ping the MX server to see if there is really a domain out there to receive the email.

[Jukkie]

Hi Fitch and Ugo DI LUCA,

I did follow the instructions of Ugo and so far it works, But the things Fitch said is also very interesting. I'am working on the ideas. Especialy to ping the MX server if there is really this domain.

I'am working on it.

Thanks for your input in this matter.

Greetings Jukkie

[Ugo DI LUCA]

Tom,

Are these characters really the unique allowed ? Wouldn't a "-" be valid, as well as other foreign characters as "

[Fitch]

Ugo, you're right a "-" (hyphen) is indeed valid AFAIK. So is "_" (underscore). Foreign characters, not sure, but I suspect you are right about that too.

Spaces are definitely prohibited, as are "&", "/", ":" and others I'm sure. Maybe I'm wrong about it being easier to check for permitted rather than prohibited characters, but if you google this problem you'll see that's generally how javascripts and other programs do it. Of course they have the advantage of using regular expressions (does FM7 do regex? Can't remember offhand).

I see what you're saying about your 3rd condition, I misread it. You're right again, I don't think the dot can follow the "@" -- that's why my calc above has the "+ 1" -- so the dot does have to come at least one character after the "@" rather than directly after it.

We are generally doing the same thing in slightly different ways. My head hurts less when I look at my calc vs. yours. For example, if you want to make sure there's only one "@" all you need is:

PatternCount( email, "@" ) = 1

... so I think your first line is overkill, although it's always fun to find a use for xor (works nicely for toggling checkboxes, but I digress).

[Ugo DI LUCA]

Fitch said:... so I think your first line is overkill, although it's always fun to find a use for xor (works nicely for toggling checkboxes, but I digress).

Well, funny it is but useless I agree.

At the moment, I've found 2 real uses of xor in my files. I was trying to find its 3rd implementation

FileMaker Version: 6

Platform: Mac OS 9

[Jukkie]

Hello Fitch,

You said: "instead of parsing the text of the field, you take the domain info from the field and ping the MX server to see if there is really a domain out there to receive the email."

Is there a way to ping the MX server by a CDML Form?

The examples you told about are XML. I don't have experience with XML.

The solution above already works, but it would be nice is there is a DNS lookup.

Greetings

Jukkie

[Fitch]

CDML only works if FileMaker is running on the target host to interpret it. So no, can't do it that way. I imagine you'd send a message to a batch file or other utility to do the actual ping, or perhaps a plugin could handle it. (Troi URL?) Sorry, I don't have anything like that actually implented, so I can't give you an example.