Break field data into two fields - or - reverse data already in field? 1

[loveyoursite]

CRV10. I have a string field EMAIL_ADDRESS. I need to extract part of the data into two seperate fields for a report. For example, Doe.James@companyname.com The result I need is everything before the first . is lastname and everything after the . up to @ is firstname. So the result would be:

lastname firstname
Doe James

I would appreciate anyone's help. By the way, what I need to do in the end is reverse the data in the field so that instead of Doe.James@companyname.com the data would be James.Doe@companyname.com. I figured there was not a way to do this in Crystal (then use another program to reload the data which I have) so that's why I'm breaking the data into two fields. Unless some of you experts out there know how?

 

[lbass]

Try:

mid({table.email_address},instr({table.email_address},".")+1, instr({table.email_address},"@")-(instr({table.email_address},".")+1))+
"."+left({table.email_address},instr({table.email_address},".")-1)+mid({table.email_address},instr({table.email_address},"@"))

-LB

 

[loveyoursite]

Thanks so much. I tried your suggestion. It came back with "String length is less than 0 or not an integer. Here's what I entered:

mid({'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS},instr({'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS},".")+1, instr({'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS},"@")-(instr({'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS},".")+1))+
"."+left({'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS},instr({'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS},".")-1)+mid({'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS},instr({'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS},"@"))

 

[lbass]

I think this means that not all of your e-mail addresses use the format starting: lastname.firstname@. And why do you have quotes around your table name?

Please put your e-mail addresses in the detail section and then add the following to your record selection formula:

instr(split({'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS},"@")[1],".") = 0

See if that returns any records. If so, please provide the format of those records returned.

-LB

 

[loveyoursite]

Thanks so much for your attention to detail. The table the email is in is actually an excell spreadsheet I've linked to through Crystal and I also have a regular table as well in the report build - employee nbr is the common link between the two. I followed your instructions and it looks like I do have some emails that do not follow the exact syntax lastname.firstname@. Some returns were:

jhb098@aol.com
lacerrn@companyname.com
thomta3@yahoo.com
smitdo2@companyname.com

Out of 197 records, only 6 returned this way. Is there a way to modify the formula so that anything not following the lastname.firstname@companyname.com syntax returns either a blank or something like "email unknown"?

 

[lbass]

Change the formula to:

if instr(split({'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS},"@")[1],".")
= 0 then "Email Unknown" else

mid({'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS},instr
({'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS},".")+1,
instr({'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS},"@")-
(instr({'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS},".")+1))+

"."+left({'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS},instr
({'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS},".")-1)+mid
({'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS},instr
({'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS},"@"))

You could also set the initial clause to:

if instr(split({'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS},"@")[1],".")
= 0 then {'In_Lawson_not_in_Addressbook_'.EMAIL_ADDRESS} else//etc.

This way the current e-mail will be displayed for the non-conforming e-mails.

-LB

 

[loveyoursite]

Thanks LB. Sorry, I must be doing something wrong. I put that formula in the record selection and it brought back:
The result of selection formula must be a boolean. As you have probably guessed, I'm not very well versed with complex formulas. Help me understand how this will give me two fields, firstname and lastname?

 

[lbass]

This formula is not for the record selection formula. It belongs in the detail section.

-LB

 

[loveyoursite]

Worked like a charm! I didn't realize you were working toward my "perfect" solution of reversing the data. Thanks so much. You have helped me in the past many times. I appreciate all you do!