Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Validate that email field content is good


Recommended Posts

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


Link to comment
Share on other sites

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,



Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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,






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 ) ;


] ;


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" ;












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.





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.


Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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".

Link to comment
Share on other sites

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"

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 8 months later...
  • 2 months later...

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.








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


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


IsEmpty(Master Entry::Email)




go next

Link to comment
Share on other sites

  • Create New...

Important Information

Terms of Use