Crystal UFL or formula to check for valid email address 1

[dgillz]

Has anyone heard of a Crystal UFL to check for a valid email address? I started writing a formula and it gets pretty long real quick.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[fisheromacse]

what do you mean check for valid email addresses?
do you mean that they contain the necessary elements of an email address? ie: name.name2@host.ext

maybe something like this to check for basics?

//{@check}
numbervar aa;
numbervar na;
numbervar dd;

IF instr({table.email},'@')>0 then aa:=1 else aa:=0;

IF LEFT({table.email),1)<>"@" then na:=1 else na:=0;

IF (LEFT(RIGHT({table.email),4)),1)="." then dd:=1 else dd:=0;

IF (aa > 0 and na > 0 and dd > 0) then "Valid" else "Invalid";

 

[dgillz]

Yes this is what I mean the necessary elements of an email address.

Your formula is woefully inadequate. You need to test that the first character is valid - there are a whole host of invalid first chsaracters. You need to test that there are not 2 periods or@ signs in a row, or more than one @ sign in the address, and you need to test for a valid extension. .com, .biz, .net, .edu, .gov are all ok, but all UK addresses end in .uk. Canada addresses end in .ca. So there are a several hundred valid email address endings.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[CoSpringsGuy]

Your formula is woefully inadequate.

wow

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[fisheromacse]

lol - yep, sure is encouraging to other to try and help with a response like that.

 

[CoSpringsGuy]

I thought your response, fisher, was a good starting point to answer his question. However to ensure his rebuttal to your suggestion is not equally as "woefully inadequate", he should probably research RFC 2822 in regards to acceptable email formatting. For example, TECHNICALLY there are circumstances that the email extension can be valid WITHOUT containing a ".". Therefore, if he wants more than just a basic formatting check on email addresses, research of the aforementioned RFC 2822 is imperative.





_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[IdoMillet]

One of the 3rd-party UFLs listed at

http://kenhamady.com/bookmarks.html

provides an IsValidEmail() function. It doesn't check for the hundreds of currently valid endings, but it checks for valid structure and characters.

By the way, I don't think the "woefully inadequate" comment was meant as an insult, but I can see why it might be taken as such. Written messages run into a danger of offending people because the recipient can't get a good reading on the actual attitude behind the words.

hth,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[CoSpringsGuy]

In hind site, I realize dg probably didn't mean it like it came across. I also have to remember that people from all over the world post on this site so there is sometimes a cultural understanding that I should have. So I apologize for my comments

To the topic, without checking online real time data for new extensions, a 100% reliable function or formula to check for valid email address seems a monumental task!

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[fisheromacse]

Hopefully the link provided by Ido will do what is needed.

anyway, here is what we use to do a basic email check for our systems. It is not all inclusive or 100% accurate, but allows us to catch the most frequent and obvious errors with little human effort:

numbervar isn;  //check for null/blank email addresses
IF {@nullcheck}=1 then isn := 9 else isn := 0;

numbervar ats;  //check that there is at least one @ symbol
IF {@nullcheck}=1 then 1 else IF instr({personnel.email},"@")>0 then ats := 0 else ats := 1;

numbervar uba;  //check that there is not more than 1 @ symbol
IF {@nullcheck}=1 then 1 else IF ubound(split({personnel.email},"@"))>2 then uba := 1 else uba := 0;

numbervar ata;  //check that the email does not begin with an invalid character (list needs revision)
IF {@nullcheck}=1 then 1 else IF NOT(LEFT({personnel.email},1) IN ["@","/","?",".","-","=","+","<",">",";","'","[","]","\","|","`","~","!","#","$","%","^","&","*","(",")","_"]) 
then ata := 0 else ata :=  1;

numbervar enp;  //check that there is a . in the last 4 characters
IF {@nullcheck}=1 then enp := 1 else IF (instr(RIGHT({personnel.email},4),".")>0) then enp := 0 else enp := 1;

numbervar ate;  //check that the email does not end with an invalid character (list needs revision)
IF {@nullcheck}=1 then ate := 1 else IF NOT(RIGHT({personnel.email},1) IN ["@",".","-","=","+","<",">",";","'","[","]","\","|","`","~","!","#","$","%","^","&","*","(",")","_"]) 
then ate := 0 else ate := 1;



numbervar ivc := 0;   //check for invalid characters throughtout email
stringvar ivn ;
numbervar ivl := len({personnel.email});
numbervar ivu := 1;
IF NOT({@nullcheck}=1) then 
While ivu < ivl OR ivu = ivl DO
(   ivn := {personnel.email}[ivu];
        IF ivn in ["=","+","<",">",";","'","[","]","\","/","|","`","~","!","#","$","%","^","&","*","(",")"] 
        then ivc := ivc + 1 else ivc := ivc;
    ivu := ivu + 1;
    ivc     );
ivc;


totext(isn+ats+uba+ata+enp+ate+ivc,0)  & " - " & totext(isn,0) & " " & totext(ats,0) & " " & totext(uba,0) 
& " " & totext(ata,0) & " " & totext(enp,0) & " " & totext(ate,0) & " " & totext(ivc,0)