Validate that email field content is good

[colin8888]

Has anyone a script or calculation to validate email fields so that they are accepted as real emails by Outlook / Outlook Express ?

 

If an email has been entered as say "abc&ddd.co.uk"

the send mail will not work. It will block without warning in the middle of a bulk mail action....

 

It looks like the validation has to check

a)that each character is on a list of approved possible characters

b) that the field contains at the end a possible character string in the form ".XX" or ".XXX"

c) that it contains '@' preceded by at least one character and followed by at least two characters before the "."

d) that there are no spaces

......

[Ted S]

Take a look at this:

 

http://www.briandunning.com/cf/360

[colin8888]

Thanks Ted. That script works just fine out of the box.

 

I put it directly as a validation script on the field.

Then made a new boolean calculation field to check existing entries.

For this I changed the script's text error messages to plain zero and found 100 error emails in a base of 7000 records.

That's really useful.

 

Just one point came up, which the script does not handle. Sometimes we put extra email addresses in the same field separated by ;

When we send mail, it then goes to both or all the addresses.

In the script anything more than one email address shows as an error.

 

Kind regards,

 

Colin

[Chris_J]

To use the script in the link then, would I just create a calc field called something like ValidEmail, paste that into the calc field?

 

How would I get it to point to my field called Email Address?

[Chris_J]

Sorted, and seems to work a treat. I just had to modify theEmail to my Email Address field.

 

Found 29 email address with a space after the last character out of about 1,000 addresses.

 

Thank you for the link.

[colin8888]

Hi Chris,

 

I also had the issue with spaces.

The script as downloaded does not check for spaces in the left hand part of the email.

 

So I added a line to check for any spaces in the email..

Here's the calculation script for the field that returns 1 if the email is good and 0 if it fails validation.

 

I have also updated the list of possible top level domains.

 

Kind regards,

 

Colin

 

----------------------------------------------------------------

 

Let (

[

_validDomainCharacters = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-." ;

_invalidLocalCharacters = "!\"#$%&'*,/:;?@[\]^`{}|~" ; // from http://www.remote.org/jochen/mail/info/chars.html

_theLocalPart = Left ( TheEmail ; Position ( TheEmail ; "@" ; 1; 1 ) - 1 ) ;

_theDomainPart = Right ( TheEmail ; Length ( TheEmail ) - Position ( TheEmail ; "@" ; 1; 1 ) ) ;

_theTopLevelDomain = RightWords ( Substitute ( _theDomainPart ; "." ; " " ) ; 1 ) ;

_ValidTopLevelDomains = "AC¶AD¶AE¶AERO¶AF¶AG¶AI¶AL¶AM¶AN¶AO¶AQ¶AR¶ARPA¶AS¶ASIA¶AT¶AU¶AW¶AX¶AZ¶BA¶BB¶BD¶BE¶BF¶BG¶BH¶BI¶BIZ¶BJ¶BM¶BN¶BO¶BR¶BS¶BT¶BV¶BW¶BY¶BZ¶CA¶CAT¶CC¶CD¶CF¶CG¶CH¶CI¶CK¶CL¶CM¶CN¶CO¶COM¶COOP¶CR¶CU¶CV¶CX¶CY¶CZ¶DE¶DJ¶DK¶DM¶DO¶DZ¶EC¶EDU¶EE¶EG¶ER¶ES¶ET¶EU¶FI¶FJ¶FK¶FM¶FO¶FR¶GA¶GB¶GD¶GE¶GF¶GG¶GH¶GI¶GL¶GM¶GN¶GOV¶GP¶GQ¶GR¶GS¶GT¶GU¶GW¶GY¶HK¶HM¶HN¶HR¶HT¶HU¶ID¶IE¶IL¶IM¶IN¶INFO¶INT¶IO¶IQ¶IR¶IS¶IT¶JE¶JM¶JO¶JOBS¶JP¶KE¶KG¶KH¶KI¶KM¶KN¶KP¶KR¶KW¶KY¶KZ¶LA¶LB¶LC¶LI¶LK¶LR¶LS¶LT¶LU¶LV¶LY¶MA¶MC¶MD¶ME¶MG¶MH¶MIL¶MK¶ML¶MM¶MN¶MO¶MOBI¶MP¶MQ¶MR¶MS¶MT¶MU¶MUSEUM¶MV¶MW¶MX¶MY¶MZ¶NA¶NAME¶NC¶NE¶NET¶NF¶NG¶NI¶NL¶NO¶NP¶NR¶NU¶NZ¶OM¶ORG¶PA¶PE¶PF¶PG¶PH¶PK¶PL¶PM¶PN¶PR¶PRO¶PS¶PT¶PW¶PY¶QA¶RE¶RO¶RS¶RU¶RW¶SA¶SB¶SC¶SD¶SE¶SG¶SH¶SI¶SJ¶SK¶SL¶SM¶SN¶SO¶SR¶ST¶SU¶SV¶SY¶SZ¶TC¶TD¶TEL¶TF¶TG¶TH¶TJ¶TK¶TL¶TM¶TN¶TO¶TP¶TR¶TRAVEL¶TT¶TV¶TW¶TZ¶UA¶UG¶UK¶UM¶US¶UY¶UZ¶VA¶VC¶VE¶VG¶VI¶VN¶VU¶WF¶WS¶XN--0ZWM56D¶XN--11B5BS3A9AJ6G¶XN--80AKHBYKNJ4F¶XN--9T4B11YI5A¶XN--DEBA0AD¶XN--G6W251D¶XN--HGBK6AJ7F53BBA¶XN--HLCJ6AYA9ESC7A¶XN--JXALPDLP¶XN--KGBECHTV¶XN--ZCKZAH¶YE¶YT¶YU¶ZA¶ZM¶ZW¶"

] ;

 

Case (

IsEmpty ( TheEmail ) ; "0" ;

PatternCount ( TheEmail ; "@" ) 1 ; "0" ;

PatternCount ( _theDomainPart ; "." ) = 0 ; "0" ;

Length ( Filter ( _theLocalPart ; _invalidLocalCharacters ) ) ;

"invalid characters in the local part" ;

Length ( Filter ( _theDomainPart ; _validDomainCharacters ) ) Length ( _theDomainPart ) ;

"0" ;

Left ( _theLocalPart ; 1) = "." or

Right ( _theLocalPart ; 1 ) = "." or

Left ( _theDomainPart ; 1 ) = "." or

Right ( _theDomainPart ; 1 ) = "." ;

"0" ;

IsEmpty ( FilterValues ( _theTopLevelDomain ; _ValidTopLevelDomains ) ) ; "0" ;

Length ( TrimAll ( TheEmail ; 1 ; 3)) ≠Length ( TheEmail ) ; "0" ;

 

1

)

)

 

 

/*

Name:

EmailIsValid

 

History:

Created by Paul Turnbull

Creation Date: 13-10-2005

Modified Date: 13-11-2005 paul@fourofus.ca - fixed numerous bugs.

 

Purpose: Returns True if the email supplied is valid an error message if it is not.

 

Parameters:

TheEmail

 

Important Notes:

List of TLDs from http://data.iana.org/TLD/tlds-alpha-by-domain.txt

Will not handle urls in the form contact@domain.com/some.name although you could edit to handle this if you like.

*/

[LingoJango]

I'm afraid accented characters are now allowed in domain names. I know because my hosting provider keeps trying to sell me some.

 

Personally I think it's a guaranteed recipe for confusion and will steer clear of the damn things in any websites I have a say in, but watch out for over-sensitive validations; you might need to add characters to the filter.

[David Head]

I have a couple of criticisms of the custom function posted:

 

1. It fails an empty email address. An empty email address is not invalid, it is empty.

 

2. The result types are mixed - number as text, text as text and number.

 

3. The comments in the function suggest:

 

Purpose: Returns True if the email supplied is valid an error message if it is not.

 

This function fails in its purpose - it only returns one error message. Elsewhere, failed validation returns a 0 as text. I don't regard "0" to be a "message".

[colin8888]

1. It fails an empty email address. An empty email address is not invalid, it is empty.

 

The question came up because my bulk emails were stopping without warning when they found an invalid address. The first objective was to select records with valid email addresses.

 

Used as a validation script with the box "Validate only if the field has been modified" checked, it will not trigger a an error if the field is left empty.

 

Used as a separate calculation field to verify the email field, it produces 0 if the email field is invalid or empty and 1 if it is good.

 

Comments 2 & 3:

See the text messages in the published function at the link given by "Ted S"

[David Head]

OK so you have removed the 'error' messages from the custom function linked to by Ted S. I may be able to see the justification in doing so, but the function in the modified form you have posted now:

 

1. is incorrectly and incompletely documented

2. contain mixed results

 

There is no justification for returning mixed results. Why are you returning "0" as a result? Why not return 0? And if you want to return simply 0 for fail and 1 for pass, do it consistently - there is still one text message in there.

[colin8888]

You are obviously right, but script works; the more general point at issue is "If it aint broke, why fix it ?"

 

Sure I would be lazy and happy to use your more perfect version if you post it..... smiley-wink

[David Head]

As a trainer of developers, I needed to point out that this was not an example of good programming. People with less experience will be impressed by the apparent structure and documentation, but on closer examination, there are some fundamental errors.

 

Unfortunately, FileMaker Pro is so forgiving that you can 'get away with it'. But that doesn't make it right. And it may break in a future version.

 

I think it is best to program as well as you can not as poorly as FileMaker Pro will allow.

[tombeek]

Totally awesome contribution. Works great.

 

Thanks a million!

 

-Tom

[Capt Guy]

I hav a looping script step that validates all email and then let's me review them. I mark a global "bad" then find all bad, if zero then send.

 

Of course, I don't run this everytime I send 1,000 emails.

 

-----------------------

 

 

Loop

 

IF

PatternCount (Master Entry::Email; " " )>0

or

PatternCount ( Master Entry::Email; "@" ) ≠ 1

or

IsEmpty(Master Entry::Email)

 

 

 

go next

[Capt Guy]

forgot the

 

trim step after loop