Validating Email Addresses With The Use of Auto-Enter Calculations

Typos, and Fat-Fingering can happen to any one of us, but if they happen in your database, they will dramatically reduce the integrity of your data.

This article illustrates an attempt at trying to prevent this very thing from happening. Its focus is on the use of an auto-enter calculation that will be performed after modifying the contents of an email address field.

Since there is no way to know whether or not the email address entered is the proper email address to use for this person, being able to catch invalid or improperly formatted data, and giving instant feedback to hint at what the problem might be, is the next best thing to ensuring data integrity.

This example shows a completed form with an invalid email address specified.

Since there was no period present within the domain part of the email address, this auto-enter calculation formatted the domain part with red text to indicate that this is our problem area.

With this instant feedback, it was quite simple to realize that a period was missing, and, after correcting the problem, the data gets reformatted, so you know you have a properly formatted email address.

Below is a depiction of the auto-enter calculation used to format the email address in this manner:

Code for Auto-Enter Calculation

Let ( [ _email = TextColorRemove ( TextStyleRemove( emailField ; AllStyles ) ) ; // Substitute 'emailField' with the field that this auto calc is being performed on _validDomainCharacters = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-." ; _invalidLocalCharacters = "!\"#$%&'*,/:;<>?@[\]^`{}|~" ; // from http://www.remote.org/jochen/mail/info/chars.html _theLocalPart = Left ( _email ; Position ( _email ; "@" ; 1; 1 ) - 1 ) ; _theDomainPart = Right ( _email ; Length ( _email ) - Position ( _email ; "@" ; 1; 1 ) ) ; _theTopLevelDomain = RightWords ( Substitute ( _theDomainPart ; "." ; " " ) ; 1 ) ; _ValidTopLevelDomains = "AC¶AD¶AE¶AERO¶AF¶AG¶AI¶AL¶AM¶AN¶AO¶AQ¶AR¶ARPA¶AS¶AT¶AU¶AW¶AZ¶BA¶BB¶BD¶BE¶BF¶BG¶BH¶BI¶BIZ¶BJ¶BM¶BN¶BO¶BR¶BS¶BT¶BV¶BW¶BY¶BZ¶CA¶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¶KR¶KW¶KY¶KZ¶LA¶LB¶LC¶LI¶LK¶LR¶LS¶LT¶LU¶LV¶LY¶MA¶MC¶MD¶MG¶MH¶MIL¶MK¶ML¶MM¶MN¶MO¶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¶RU¶RW¶SA¶SB¶SC¶SD¶SE¶SG¶SH¶SI¶SJ¶SK¶SL¶SM¶SN¶SO¶SR¶ST¶SU¶SV¶SY¶SZ¶TC¶TD¶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¶YE¶YT¶YU¶ZA¶ZM¶ZW"; _redAt = TextColor ( "@"; RGB ( 255 ; 0 ; 0 ) ); _theDomainUpToTLD = Left ( _theDomainPart ; Length ( _theDomainPart) - Length ( _theTopLevelDomain ) ) ] ; Case ( IsEmpty ( _email ) ; "" ; PatternCount ( _email ; "@" ) > 1 ; Substitute (_email; "@"; _redAt); PatternCount ( _email ; "@" ) = 0 ; TextColor ( _email; RGB ( 255 ; 0 ; 0 ) ); PatternCount ( _theDomainPart ; "." ) = 0 ; _theLocalPart & "@" & TextColor ( _theDomainPart ; RGB ( 255 ; 0 ; 0 ) ) ; Length ( Filter ( _theLocalPart ; _invalidLocalCharacters ) ) ; TextColor ( _theLocalPart ; RGB ( 255 ; 0 ; 0 ) ) & "@" & _theDomainPart ; Length ( Filter ( _theDomainPart ; _validDomainCharacters ) ) <> Length ( _theDomainPart ) ; _theLocalPart & "@" & TextColor ( _theDomainPart ; RGB ( 255 ; 0 ; 0 ) ) ; Left ( _theLocalPart ; 1) = "."; TextColor ( "." ; RGB ( 255 ; 0 ; 0 ) ) & Right ( _theLocalPart ; Length ( _theLocalPart ) - 1 ) & "@" & _theDomainPart ; Right ( _theLocalPart ; 1 ) = "."; Left ( _theLocalPart ; Length ( _theLocalPart ) - 1 ) & TextColor ( "." ; RGB ( 255 ; 0 ; 0 ) ) & "@" & _theDomainPart ; Left ( _theDomainPart ; 1 ) = "." ; _theLocalPart & "@" & TextColor ( "." ; RGB ( 255 ; 0 ; 0 ) ) & Right ( _theDomainPart ; Length ( _theDomainPart ) - 1 ) ; Right ( _theDomainPart ; 1 ) = "." ; _theLocalPart & "@" & Left ( _theDomainPart ; Length ( _theDomainPart ) - 1 ) & TextColor ( "." ; RGB ( 255 ; 0 ; 0 ) ) ; IsEmpty ( FilterValues ( _theTopLevelDomain ; _ValidTopLevelDomains ) ) ; _theLocalPart & "@" & _theDomainUpToTLD & TextColor ( _theTopLevelDomain ; RGB ( 255 ; 0 ; 0 ) ); //Else if it's formatted properly TextStyleAdd(TextColor ( _email; RGB ( 0 ; 0 ; 200 ) ); Underline ) ) ) /* Original Name: EmailIsValid_CFpub (now used as auto-enter calc) History: Created by Paul Turnbull Creation Date: 13-10-2005 Modified by Matthew Leering Modified Date: 05-01-2008 Purpose: Returns email address in blue underlined text if formatted correctly Important Notes: List of TLDs from http://data.iana.org/TLD/tlds-alpha-by-domain.txt */

The way that this calculation works is by first declaring a variable that will be equal to the email address, but without formatting.

_email = TextColorRemove ( TextStyleRemove( emailField ; AllStyles ) ) ;

(This is done so that the red text will get removed if an incorrect email address is corrected.)

Lists of valid and invalid characters are defined.

_validDomainCharacters = “abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-.” ;

(This example shows a list of characters that are valid within the domain part of the email address, and will be used later in the calculation.)

The email address is then parsed down into various parts.

_theLocalPart = Left ( _email ; Position ( _email ; “@” ; 1; 1 ) – 1 ) ;

(The local part of the address is the part to the left of the ‘@’ sign.)

The case statement that shows up about half-way through this calculation is really where the all the validation-checking takes place.

In this part, we:

• Ensure that there is only one @ symbol
• Ensure that there is a period within the domain part
• Filter out invalid characters in the local part
• Filter out invalid characters in the domain part
• Ensure that neither the first nor last characters in the local part are a period
• Ensure that neither the first nor last characters in the domain part are a period
• Ensure that a valid top-level domain was used.

If any of these checks fail, then the offending part is coloured red, otherwise the resulting email address is coloured blue and underlined to give feedback that the email address is indeed formatted properly.

Although this calculation is based upon a custom function originally written by Paul Turnbull, it has been modified so that it can now be used as a visual feedback cue instead of for use in FileMaker’s native validation.

Also, this can be used directly within the auto-enter calculation of a field, which means that you do not need the advanced version of FileMaker Pro in order to take advantage of this functionality.

Making use of this calculation in your own projects is quite simple. Simply insert it as an auto-enter calculation on one of your email fields, replace “emailField” with the name of the email field as it exists in your project (on the first line of the calculation), and make sure that the auto-enter calculation is set to replace existing values. Even if the calculation itself appears complicated at first glance, these simple few steps are all you need to help you make sure you’re keeping valid email addresses within your database.