Solved

Crystal Reports XI string function problem

Posted on 2008-06-11
12
5,614 Views
Last Modified: 2013-11-16
I'm trying to do a string manipulation in CR XI R2 and it's not working for me.  It's probably simple and I'm overcomplicating it.

I have a string value (named "itemCode") composed of letters and numbers which I pass into a formula field with the attached code.  I need to conditionally "truncate" or cut a certain number of the last characters off and display them (truncated and full) in my report.  In most of my cases the code works, but with a value of R4.0022, I would like to end up with R4.002 but what I'm getting is R4.00. The line that is causing me problems is:
if {chaProducts.itemCode} like "*.????" then replace({chaProducts.itemCode},right({chaProducts.itemCode},1),"")

It seems to replace the number 2 wherever it finds it, but I only want it to cut off the final 2.  So I would like to replace the 2 only once, starting at the end.  I know I can tell a "replace" function how many times to replace a string but can I tell it to start looking at the end of the string and work backwards?  Another clever function perhaps?  Any help would be appreciated.
thanks

if {chaProducts.itemCode} like "*.000?" then replace({chaProducts.itemCode},right({chaProducts.itemCode},5),"")

    else if {chaProducts.itemCode} like "*.????" then replace({chaProducts.itemCode},right({chaProducts.itemCode},1),"")

        else {chaProducts.itemCode}

Open in new window

0
Comment
Question by:chamlight
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 100 total points
ID: 21765380
Would be using a different function than replace...

I quickly tested (not exactly the same) and got the results expected... So, change @stringfield for chaProducts.itemCode and fix the cirteria (and remove the "formatn:" etc)

It does assume only 1 decimal point in the string.


if      {@stringfield} like "*.0600" then "format1:" + left({@stringfield},instr({@stringfield},".")+2)

else if {@stringfield} like "*.???0" then "format2:" + left({@stringfield},instr({@stringfield},".")+3)

else if {@stringfield} like "*.???3" then "format3:" + left({@stringfield},instr({@stringfield},".")+4)

else                                      "format4:" + left({@stringfield},instr({@stringfield},".")-1)

Open in new window

0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 150 total points
ID: 21765613
Yes you can specify the start position
From Crystal Help
Replace (inputString, findString, replaceString, startPosition)

Try
if {chaProducts.itemCode} like "*.????" then replace({chaProducts.itemCode},right({chaProducts.itemCode},1),"",Length({chaProducts.itemCode})-1)

mlmcc
0
 

Author Comment

by:chamlight
ID: 21769684
Thanks to you both...I'll try your suggestions
c
0
 
LVL 34

Accepted Solution

by:
James0628 earned 250 total points
ID: 21793323
It looks like if the string ends in ".000?", you want to drop that part, otherwise if it ends in ".????", you want to just drop the final character.  Mid is probably a better function for that kind of thing.  Something like:

if {chaProducts.itemCode} like "*.000?" then
  Mid ({chaProducts.itemCode}, 1, Length ({chaProducts.itemCode}) - 5)
else
  if {chaProducts.itemCode} like "*.????" then
    Mid ({chaProducts.itemCode}, 1, Length ({chaProducts.itemCode}) - 1)
  else
    {chaProducts.itemCode}



 FWIW, while you can't actually make Replace work from the end of the string towards the beginning, you could use StrReverse to reverse the string within the Replace, and then use StrReverse again on the result to put it back in the original order.  Probably not the most efficient approach, but it is an option.

 James
0
 

Author Closing Comment

by:chamlight
ID: 31466402
Thanks very much to all...awarded split points.  James, thanks for the info
0
 
LVL 34

Expert Comment

by:James0628
ID: 21798298
You're welcome.

 FWIW, in this particular case, since you're taking characters from the start of the string up to a certain point, Left () may be a better choice than Mid ().  In theory, it's a little simpler, although I don't know that there's any real difference in what happens behind the scenes.  Whichever function you use, the best (simplest and/or most reliable) way to determine the end point depends on your specific circumstances.

 James
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 21798740
@james, yes, I think so as well - esepcially the positioning for the length (end points) - for example "length" function would be interesting if you are dealing with a space filled char column...
0
 
LVL 34

Expert Comment

by:James0628
ID: 21799586
I'm not sure what you mean by "space filled char column", but it you mean a column containing nothing but spaces, that's not going to match your search patterns anyway.

 In general, there may be times when you need to do things like check the length to make sure that it's greater than X before using something like Length () - X in a string function.  With your specific target strings, it's probably not an issue, although there is a potential problem.

 Your first test is for "*.000?".  That would include strings like ".0003", with nothing in front of the period.  A string like that would get an error using the formula I posted, because you'd end up with Mid () trying to take characters from 1 to 0, and CR doesn't like that.  Left ("string", 0) seems to be OK though, so using Left () instead of Mid () should work.

 I'd actually wondered what would happen if you had a string like ".0003", but hadn't actually checked until just now.

 James
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21800036
Wasn't having a shot, just that many a time in the SQL space, there have been problems with length usage when there was an expectation that any trailing spaces where dropped, or non-existant - which is not always the case. Even in varchar columns if explicitly populated with a trailing space will happily store the trailing spaces... A lot of people assume that the string ends with the last significant digit, I do so myself on many an occasion.  

e.g. using SQL Server to exemplify...
declare @str1 varchar(20)
declare @str2 varchar(20)
declare @str3 char(20)
set @str1 = '123.0003    '
set @str2 = '123.0003'
set @str3 = '123.0003'
select datalength(@str1),datalength(@str2),datalength(@str3)
select '['+@str1+']['+@str2+']['+@str3+']'
select len(@str1),len(@str2),len(@str3)

or, in crystal, set up a formula field and try...
Local StringVar addressLine;
Local NumberVar addrLength;
addressLine := "British Columbia V6X 3W2            ";
addrLength := Length (addressLine);
addressLine [addrLength-16+1 to addrLength]

So, really I was agreeing with your observations that you probably need to "know" your data in order to determine which function is going to best suit.

Generally speaking, if it is the first 'n' characters of a column (numeric or character), then the "left" function has served (me at least) particularly well - it is more tolerant of things like a zero length string, and even the type of column.

By the way, the string "*.000?" was used by the author, so assumed that it was part of the specification, and can include ".0003" - so does that mean I posted the only correct entry (just being very cheeky now, and please, please, do not be offended).
0
 

Author Comment

by:chamlight
ID: 21803338
With all of your help, here's what I came up with...it seems to work with my data so far.  

if {chaProducts.itemCode} like "*.000?" then replace({chaProducts.itemCode},right({chaProducts.itemCode},5),"")
    else if {chaProducts.itemCode} like "*.????" then replace({chaProducts.itemCode},{chaProducts.itemCode},(left({chaProducts.itemCode},(Length({chaProducts.itemCode})-1))))
        else {chaProducts.itemCode}


And you're right about having to "know" your data.  When asking you guys for help, it's sometimes hard to fully convey the nature of one's own data.  What is obvious to me, is clearly not obvious to you.  So I commend you for trying to "get into my head" to really help me out.  Thanks

0
 
LVL 34

Expert Comment

by:James0628
ID: 21807498
Mark,

 Sorry.  I didn't think you were "having a shot".  I had actually assumed that you were the OP.  Shows how much attention I was paying.  <wry g>  Maybe some of my comments make more sense now.

 Yeah, trailing spaces can be an issue in some cases, but given the OP's target strings, they're not an issue here.  FWIW, if you think they could be an issue in a CR formula, you could always use TrimRight to make sure there aren't any.

 As for you posting the only correct answer, I don't see anything wrong with mlmcc's suggestion.  My original suggestion would work, as long as there was always something in front of the "." (which may or may not be the case).  And, as you noted, yours would not work if there was more than one "." (which may or may not be the case).  So, really, mlmcc's  answer seems the "surest" of the original answers.
 (And, no, I wasn't offended at all.)

 James
0
 
LVL 34

Expert Comment

by:James0628
ID: 21807614
chamlight,

 It took me a minute to figure out what you're doing in the latter part of that formula, but it looks like you're replacing the entire string with everything but the last character.  Assuming that I'm interpreting that correctly, you really don't need the Replace ().  Just use the Left () part (what you're using to replace the string).

if {chaProducts.itemCode} like "*.000?" then
  replace({chaProducts.itemCode},right({chaProducts.itemCode},5),"")
else
  if {chaProducts.itemCode} like "*.????" then
    left({chaProducts.itemCode},(Length({chaProducts.itemCode})-1))
  else {chaProducts.itemCode}


 What you have should work.  It's just a little more complicated than necessary.  And if you have a lot of long strings, doing all of those replaces might affect performance, although I suspect that you'd have to have a _lot_ of long strings in order for it to have a noticeable effect.

 James
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

13 Experts available now in Live!

Get 1:1 Help Now