12 Replies Latest reply on Oct 10, 2013 10:00 AM by ArtL

    eMail Validation Code Problem

    ArtL

      Title

      eMail Validation Code Problem

      Post

           I have an issue with this email validation code (very cool script I found online and modified).  I have expanded it to look for .com, .ca, .net and .org as the only email extensions that will be accepted in the field.

           For some reason, it is taking the first entry in the inclusion and saying it is not valid.  In the code below, the extension it will not allow is ".ca"

            and Right (_email;2)="ca"

           If the abvove code was ".com", my warning message would read that .com was incorrect.  Etc Etc.

           Can anyone tell me why this is the case? 

            

           Thank you in advance for your help!

            

           IsEmpty (Email) or
           Let ([
                   _alphanum = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
                   _email = Trim (Email);
                   _posAt = Position (_email;"@";1;1)
           ];
                   Filter (_email;_alphanum & "_-@.!#$%&^'*+-{|}~")=_email           

                   and PatternCount (_email;"@")=1
                   and PatternCount (_email;".com")=1
                   and PatternCount (_email;".ca")=1
                   and PatternCount (_email;".net")=1
                   and PatternCount (_email;".org")=1
                   and IsEmpty (Filter (Left(_email;1);"@"))
                   and IsEmpty (Filter (Left(_email;1) & Middle (_email; _posAt -1;3) & Right (_email;1);"."))
                   and not Position (_email;"..";1;1)
                   and Position (_email;".";_posAt +2;1)
                   and Right (_email;2)="ca" or Right (_email;3)="net" or Right (_email;3)="org" or Right (_email;3)="com"
                   and not IsEmpty (_email)
           )

        • 1. Re: eMail Validation Code Problem
          philmodjunk

               Is your email field of type text or type number?

               Often, the best way to debug a complex calculation is to divide it up into smaller "pieces" and examine the results returned by each such "piece".

          • 2. Re: eMail Validation Code Problem
            ArtL

                 Hi Phil,

                  

                 email field is type Text

            • 3. Re: eMail Validation Code Problem
              philmodjunk

                   Then I suggest setting up several calculations, each of which performs a portion of the total calculation to check and see which part is not returning the expected value.

              • 4. Re: eMail Validation Code Problem
                ArtL

                     Thanks, I have set up a file with 6 email fields, and I am customizing the code in each to see if I can isolate this down.

                      

                     So far, no luck.

                      

                     Keep at it.

                • 5. Re: eMail Validation Code Problem
                  philmodjunk

                       If you have fileMaker Advanced, you can open up the data viewer and set up a version of this calculation as a "watch" expression and then you can enable, disable different portions of the full calculation to see what values are being returned by each portion of the calculation.

                  • 6. Re: eMail Validation Code Problem
                    ArtL

                         I do have Advanced.

                          

                         OK, I will give this a shot...

                    • 7. Re: eMail Validation Code Problem
                      ArtL

                           Tried it, it reports back the obvious and not where it is making the error.

                           I can't seem to find why I have to add a fack extension, "XXX", in order to let the rest of the function work.  without the XXX, it picks the first extension inthe equation.  In the example above, it would be ".ca"

                           It also allows "CNET" which I don't get! lol

                           I will have to write this in a script to I can follow it step by step, if possible.

                      • 8. Re: eMail Validation Code Problem
                        philmodjunk

                             Do you have a single email in the field or a list of them? I've assumed a single email and this calc won't validate a list of emails in the same field.

                             I ask that as I can't follow some of your comments.

                             What do you mean by: "add a fack extension, "XXX""?

                             What do you mean by: "it picks the first extension in the equation."?

                             Here's how I have started testing this in the DataViewer:

                             Let ( Email = " SomeName@Domain.ca" ;

                             IsEmpty (Email) or
                             Let ([
                                     _alphanum = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
                                     _email = Trim (Email);
                                     _posAt = Position (_email;"@";1;1)
                             ];
                                     Filter (_email;_alphanum & "_-@.!#$%&^'*+-{|}~")=_email           

                                     and PatternCount (_email;"@")=1
                                     and PatternCount (_email;".com")=1
                                     and PatternCount (_email;".ca")=1
                                     and PatternCount (_email;".net")=1
                                     and PatternCount (_email;".org")=1
                                     and IsEmpty (Filter (Left(_email;1);"@"))
                                     and IsEmpty (Filter (Left(_email;1) & Middle (_email; _posAt -1;3) & Right (_email;1);"."))
                                     and not Position (_email;"..";1;1)
                                     and Position (_email;".";_posAt +2;1)
                                     and Right (_email;2)="ca" or Right (_email;3)="net" or Right (_email;3)="org" or Right (_email;3)="com"
                                     and not IsEmpty (_email)
                             )  // Inside Let

                             )  // Outside Let that assigns a value to email for testing purposes

                             As you have reported, it fails, returning zero even though the email has a valid format. Now I can start using the data viewer to examine each of the intermediary values and the logic expression. More posts to follow....

                        • 9. Re: eMail Validation Code Problem
                          philmodjunk

                               Found It!

                               use:

                                      Filter (_email;_alphanum & "_-@.!#$%&^'*+-{|}~")=_email           

                                       and  PatternCount (_email;"@")=1
                                       And ( PatternCount (_email;".com")=1
                                       Or PatternCount (_email;".ca")=1
                                       Or PatternCount (_email;".net")=1
                                       Or PatternCount (_email;".org")=1 )
                                       and IsEmpty (Filter (Left(_email;1);"@"))
                                       and IsEmpty (Filter (Left(_email;1) & Middle (_email; _posAt -1;3) & Right (_email;1);"."))
                                       and not Position (_email;"..";1;1)
                                       and Position (_email;".";_posAt +2;1)
                                       and Right (_email;2)="ca" or Right (_email;3)="net" or Right (_email;3)="org" or Right (_email;3)="com"
                                       and not IsEmpty (_email)

                          • 10. Re: eMail Validation Code Problem
                            ArtL

                                 Phil,

                                 you are amazing.

                                 This is an email validation for ONE field.  An employee email field in a personnel file.

                                 This solved the problem of the fake email filter.  Since it was picking the first occurance... ".com" and rejecting it, I made another occurance that was ".xxx" and it proceeded through.  You solved that.

                                 It is still allowing ".cnet" or ."1com" or ".2com" etc.  It is still not getting the Right Values correct, since it is allowing 4 characters to return as valid.

                            • 11. Re: eMail Validation Code Problem
                              philmodjunk

                                   Hmmm hadn't taken a close look at that last bit. You just need to include the period in your quoted text and increment the number of characters by one. This also makes the part that I enclosed in parenthesis redundant so you can remove it to get:

                                   IsEmpty (Email) or
                                   Let ([
                                           _alphanum = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
                                           _email = Trim (Email);
                                           _posAt = Position (_email;"@";1;1)
                                   ];
                                          Filter (_email;_alphanum & "_-@.!#$%&^'*+-{|}~")=_email           

                                           and  PatternCount (_email;"@")=1
                                           and IsEmpty (Filter (Left(_email;1);"@"))
                                           and IsEmpty (Filter (Left(_email;1) & Middle (_email; _posAt -1;3) & Right (_email;1);"."))
                                           and not Position (_email;"..";1;1)
                                           and Position (_email;".";_posAt +2;1)
                                           and Right (_email;3)=".ca" or Right (_email;4)=".net" or Right (_email;4)=".org" or Right (_email;4)=".com"
                                           and not IsEmpty (_email)
                                   )

                              • 12. Re: eMail Validation Code Problem
                                ArtL

                                     Bingo!

                                      

                                     Nailed it.

                                     My problematic examples now work properly.  I will test it further but I think you nailed it.

                                      

                                     Thanks!!

                                      

                                     Art