Solved

How to escape : in formula

Posted on 2006-11-16
9
328 Views
Last Modified: 2006-11-18
Hi,

I have the following formula in Crystal syntax for my mail to address .

On certain data set, it did not return the formated address, while others work just fine.  I suspect it is because in my head_add1, the actual data has a : in it, such as ATTN: Joe Doe. How do I escape that?

stringVar out := '';
if not isnull({Invoice.head_add1}) then
   out := out + {Invoice.head_add1} + chr(10) + chr(13);
if not isnull({Invoice.head_add2}) and ({Invoice.head_add2})<>'' then
   out := out + {Invoice.head_add2} + chr(10) + chr(13);
if not isnull({Invoice.head_City}) then
   out := out + {Invoice.head_City} + ',' + {Invoice.head_state} + ' ' +{Invoice.head_postal_Code} + ' ' + {Invoice.head_country};
out;

Thanks!
0
Comment
Question by:ommer
  • 4
  • 4
9 Comments
 
LVL 17

Expert Comment

by:MIKE
Comment Utility
Please give me some sample rows of the field {Invoice.head_add1} from your data table..I need to see what it looks like...

thanks
MikeV
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Try something like this

stringVar out := '';
numbervar ColonPlace;
if not isnull({Invoice.head_add1}) then
(
   ColonPlace := instr(":",{Invoice.head_add1});
   if ColonPlace > 0 then
       out := out + mid({Invoice.head_add1},ColonPlace+1)  + chr(10) + chr(13);
   else
       out := out + {Invoice.head_add1} + chr(10) + chr(13);
)
if not isnull({Invoice.head_add2}) and ({Invoice.head_add2})<>'' then
   out := out + {Invoice.head_add2} + chr(10) + chr(13);
if not isnull({Invoice.head_City}) then
   out := out + {Invoice.head_City} + ',' + {Invoice.head_state} + ' ' +{Invoice.head_postal_Code} + ' ' + {Invoice.head_country};
out;

mlmcc
0
 

Author Comment

by:ommer
Comment Utility
Thanks! I understand your approach.
I got a syntax error "The ) is missing" with the ELSE highlighted. I did not find any missing ).

Sample data for head_add1:
Normally they look like these:
38 Plant Rd
4198 Town Center Ave
But could also like:
ATTN: Joe Doe
Or
Split Ship
If it is ATTN, just mix it with the rest of the address, and if it is Split Ship, don't show anything at all.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
>>I got a syntax error "The ) is missing" with the ELSE highlighted. I did not find any missing ).
The problem isn't a missing ) but rather an extra ;.
Delete the ; before ELSE
   if ColonPlace > 0 then
       out := out + mid({Invoice.head_add1},ColonPlace+1)  + chr(10) + chr(13)
   else
       out := out + {Invoice.head_add1} + chr(10) + chr(13);

If there is a : will you always have ATTN:

Try this
if not isnull({Invoice.head_add1}) then
(
   if Left({Invoice.head_add1},10) = "ATN:"  then
       out := out + mid({Invoice.head_add1},6)  + chr(10) + chr(13);
   else if  Left({Invoice.head_add1},5) <> "Split Ship"  then
       out := out + {Invoice.head_add1} + chr(10) + chr(13);
)

mlmcc
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:ommer
Comment Utility
Thanks! I made little adjustment as:
if not isnull({Invoice.head_add1}) then
(
   if Left({Invoice.head_add1},5) = "ATTN:"  then
       out := out + mid({Invoice.head_add1},6)  + chr(10) + chr(13)
   else if  Left({Invoice.head_add1},10) <> "Split Ship"  then
       out := out + {Invoice.head_add1} + chr(10) + chr(13);
)

It still wouldn't show data with :. The whole return is empty. Let me try to take it out in sql.

But it did pick up "Split Ship" nicely. By the way, how do I exit out in the middle? I want to return empty when it has Split Ship, i.e. no need to concat city, state etc.
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 100 total points
Comment Utility
Probably the easiest is

WhilePrintingRecords;
stringVar out;
out := '';
if not isnull({Invoice.head_add1}) then
(
   if  Left({Invoice.head_add1},10) <> "Split Ship"  then
   (    
       if Left({Invoice.head_add1},5) = "ATTN:"  then
           out := out + mid({Invoice.head_add1},6)  + chr(10) + chr(13)
       else
           out := out + {Invoice.head_add1} + chr(10) + chr(13);
       if not isnull({Invoice.head_add2}) and ({Invoice.head_add2})<>'' then
          out := out + {Invoice.head_add2} + chr(10) + chr(13);
       if not isnull({Invoice.head_City}) then
          out := out + {Invoice.head_City} + ',' + {Invoice.head_state} + ' ' +{Invoice.head_postal_Code} + ' ' + {Invoice.head_country};
    )
)
out

mlmcc
0
 

Author Comment

by:ommer
Comment Utility

Fantastic!

Thanks a lot!
0
 

Author Comment

by:ommer
Comment Utility
Oh, one more thing, what is this "WhilePrintingRecords;"? Can I leave it out? Will that make it only work on print but not preview?


0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
It will work when running the report.  It means the formula will run when the records are being displayed or previewed.

mlmcc
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now