Extracting Email addresses with Crystal

[blindlemonray]

Hi All,

I am trying to extract contact1 info along with email from contsupp.

My problem is when I add in contsupp table and specify rectype 'P' and/or contact 'e-mail address’ all I get is those records which have an email address. Any records that don't have an email address are excluded.

I thought that if my table’s linkage was a left outer join or a full outer join this would solve the problem as I thought this meant all from contact1 and only those from contsupp where fields are equal, but apparently not!

Any help would be much appreciated.

 

[MIChad76]

Since you are selecting on contsupp the full outer join won't help... Since those records would be null and hence not have a rectype of P... I'd suggest a SQL Query for this operation...



---
Chad Smith
michad76@gmail.com

Check out the GoldMine Blog at

http://www.thegmblog.com

MCP - Microsoft SQL Server
Crystal Reports Certified Professional
GoldMine Certified Professional
Microsoft CRM Certified Professional

 

[dgillz]

Link Contact1 with ContSupp by AccountNo. Just use a straight up equal join. I used the following record selection formula to get email addresses:

{ContSupp.RECTYPE}="P" and
instr({ContSupp.CONTSUPREF},"@")>0

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lisaharris]

If you use dgillz option to select email addresses, be very careful. You need to concatenate contsupref and address1 in the contsupp table to get the FULL email address. Any email address that is over 35 characters will be started in contsupref and continued in the address1 field.

 

[blindlemonray]

Hi dgillz,

I have added your code to the report and it will only show me records where there is an email address. I really need to see all records whether they have an email address or not.

Hi Lisa,
I was aware of the issue with the full email address if over 35 characters long but if I use the "X & Y" to concatenate contsupref and address1 all I get is email address with address1 on the end of it.

Got any pointers on both of the above?

 

[dgillz]

Well, since the title of your post is "extracting email addresses" I thought I would give you a way to do this.

If you want everything, delete the record selection formula.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[blindlemonray]

Hi dgillz,

thanks for the advice. I should have been a bit more thorough with my initial posting. I could not put the entire of my query in the title as it was a bit long.

Unfortunatley I have to have a record selection as I need to specify date ranges and segments of data within the whole dataset. The report at the moment contains contact1, contact2 and conthist fields. I query on conthist ondate and contact1 key4 fields, so the record selection part of the report is essential.

 

[dgillz]

OK then remove my part of the record selection, so you'll see every record within your date range.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?