Validate Data as Email Address

[Quartekoen]

Our customer database has a field in it for the customer's email address. I would like to ensure that anything entered in this field is, in fact, a valid email address.

First question. If I want to search the field for email addresses only (thus excluding invalid address), I've searched for *"@"*"."@@*

Would this be the correct way of searching for email address, or is there a better/more effective way? I wonder about some email address with this. For example, if it were [email protected], but if it were entered incorrectly as [email protected], this search would still work on it, even though the latter is not a valid address.

Second, how can I validate the data entered upon data entry? Can I use something like *"@"*"."@@* in the field definition's Validation tab? If not, is it something that could be done with script triggers?

Any suggestions would be appreciated. Thanks.

[Vaughan]

Our customer database has a field in it for the customer's email address. I would like to ensure that anything entered in this field is, in fact, a valid email address.

Putting my "Mr Pedantic" hat on, the only way to ensure an e-mail address is valid is to send a message to the address and see if works.

What you want to do is "syntax checking" which is a lot easier. Syntax checking confirms the address "looks" correct - for any given definition of what a correct address look like. It's really just guessing.

I think there is a unix command that allows an address to be validated by the smtp host.

A couple of web sites that discuss this issue:

http://haacked.com/archive/2007/08/21/i-knew-how-to-validate-an-email-address-until-i.aspx

http://www.coveryourasp.com/ValidateEmail.asp

[Quartekoen]

Thanks for the reply, Vaughan, and sorry about my absence in replying back.

...is to send a message to the address and see if works.

What you want to do is "syntax checking" which is a lot easier.

I beg to differ.

I think I need to clarify what it is that I'm trying to do, even though looking through your links warns me that this may be futile :

First of all, I'm not worried about whether the email entered is an existing email, only that it is a valid, correctly formatted email address.

Second, while I appreciate the links you supplied, neither really seems to do what I'm looking for. The first one uses some RegEx to validate the local portion of the email. This part I'm not too worried about. Not only are tons of characters supported in the local portion, but it's businesses I work with, and it's unlikely they'll have any of these strange characters for me to watch out for. It is more the domain portion that I want to look through.

The second seems to do a lot of what you were talking about at first, which was to ensure the the email is a real, existing email address. This too, I'm not too worried about. If an email fails, the sender will get an email from the Mailer Daemon, and we'll call the customer for an update. While this will also happen if the user enters an invalid email into the customer's file, it would be nice to read what the user enters and immediately warn that it's impossible for it to be the correct email address because it's formatted incorrectly.

The thing I am looking for is to read the domain portion of the email for validity. While most people will say "look for an @ followed by some text followed by a . followed by more text, and it's valid," this isn't necessarily true. I am still in school, and my email there ends with @students.myschoolname.edu. If I were to mistype this as @students.myschoolname.ed, it would be impossible for this to be a valid email address, but the quoted rule above would pass.

Though, at this point, I wonder if it is really worth it to do this. Like I said, you'll get an auto-response when you send an invalid email address, and we can update it from there. What do you think on the matter?

[Vaughan]

First of all, I'm not worried about whether the email entered is an existing email, only that it is a valid, correctly formatted email address.

There is a BIG difference between "what the specification allows" and "what is conventionally used for addresses". So when you refer to a "correctly formatted email address" what criteria do you want to judge it by, convention or specification?

For instance, this is a valid e-mail address but it's highly unconventional:

"Abc@def"@example.com

Note that the "local" part is quoted and contains an @ symbol. It's a valid address under the specs. Not likely to ever see it in the wild though. (When botanists first brought a platypus skeleton back to Britain it was considered to be a hoax because it was so unconventional.)

Your example is also very US-centric: in the UK domains are 2 characters not 3, so xx.co.uk is the norm not the exception.

I haven't suggested any code to verify addresses because it's bloody complicated. It will involve long hard-coded lists of expected values and lots of switches to allow for variations.

[Quartekoen]

Your example is also very US-centric: in the UK domains are 2 characters not 3, so xx.co.uk is the norm not the exception.

I apologize if my post came off as pretentious, but this helps reaffirm by understanding of how complicated this really is.

I haven't suggested any code to verify addresses because it's bloody complicated. It will involve long hard-coded lists of expected values and lots of switches to allow for variations.

Exactly my point. I've stopped wondering whether it is possible to do in FileMaker, but rather whether it is reasonable to even attempt. I expect that the gain from doing so is greatly outweighed by the effort of it.

[Vaughan]

Sorry if I implied you were being pretentious, that was not my intention.

A pragmatic suggestion is to sit down and do some data examination and work out what the likely addresses are that the database will get. If they are all from within the US then indeed leave ".co" out of the spec and even forget the country code. Validate for com, edu, etc (one of the sites has a complete list of codes).

Build the validation calculation around what the database is likely to have entered, but (and this is the important bit) allow the user to override the validation so that addresses that the validation does not like can be left in. So the user gets a warning and can decide themselves whether to correct it or not.

Over time build up the validation calculation to get smarter and include the edge cases.