Replace Multiple Variables in String 2

[msi30502]

Hi all,
I'm a new member and a big fan. I've come to an issue for which I haven't found a solution in any of the threads. Here goes...

I'm using Crystal Reports 2008.

I have a field in which there are numerical codes seperated by spaces (text string format). Each code has a value.

CODE Value
```` ``````````
01 Apples
02 Bananas
03 Carrots
04 Rice
05 Mangos

However, not all code values are the same per individual record. Every record has it's own entry. Here's what I mean,

RECORD ENTRY
``````` ````````
101 01 02
102 01 03 05
103 04
104 01 02 03 04 05

I need to replace the codes each record with it's corresponding value. The result has to be in a comma format. The result per record on the report should look like the "DESIRED RESULTS" below:

RECORD DESIRED RESULT
``````` ``````````````
101 Apples, Bananas
102 Apples, Carrots, Mangos
103 Rice
104 Apples, Bananas, Carrots, Rice, Mangos

I've been able to: REPLACE({Table.field},"01","Apples"). The result was simply "Apples" in the report (as expected), but I don't know where to go from this point. How do I include the multiple values with commas?

Thanks in advance!

 

[lbass]

stringvar x := {table.codes};
stringvar array y := split(x," ");
numbervar i;
stringvar z := "";
for i := 1 to ubound do(
z := z +
(select y
case "01" : "Bananas"
case "02" : "Peaches"
case "03" : "Pears"
case "04" : "Apples"
case "05" : "Grapes"
)+", "
);
left(z,len(z)-2)

-LB

 

[msi30502]

Thanks for the help. I never would have figured it out on my own.

However, Crystal reports is giving me an Error Message. It highlights the following:

left(z,len(z)-2)

And says "String Length is less than 0 or not an integer"

So I removed it and left it at: left(z,len(z))

Doing so allowed me to generate the report, but with problems.
1. Records with more than one code (i.e., 01 02) only showed the value for the first code "Bananas" and nothing else. It's not listing all the values associated with that record.
2. There is an unnecessary comma after the first word, even if only one value should be there "Bananas,"

Look forward to anyones response. thanks

 

[CoSpringsGuy]

you have recodrs that dont have codes in your database which is why you recieved the ""String Length is less than 0 or not an integer" error..
Use LBass code exactly as originally posted but add the following where the ** are
stringvar x := {@test};
stringvar array y := split(x," ");
numbervar i;
stringvar z := "";

if ubound > 0 then //**
( //**
for i := 1 to ubound do(
z := z +
(select y
case "01" : "Bananas"
case "02" : "Peaches"
case "03" : "Pears"
case "04" : "Apples"
case "05" : "Grapes"
)+", "
);
z := left(z,len(z)-2)
); //**
z //**

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[msi30502]

Thanks for fixing my error message. Now I just need to fix the next two problems (items 1 and 2 above).

 

[CoSpringsGuy]

thats why my post said to use LBass code EXACTLY and add the 4 lines I sent .. Your change caused number 2 ... number 1 I am unable to duplicate.. works fine for my test data

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[msi30502]

stringvar x := {Table.Field};
CoSpringsGuy,
If I'm not mistaken, I updated exactly as you said. See below:

stringvar array y := split(x," ");
numbervar i;
stringvar z := "";
if ubound >0 then
(
for i := 1 to ubound do(
z := z +
(select y
case "01" : "Bananas"
case "02" : "Peaches"
case "03" : "Pears"
case "04" : "Apples"
case "05" : "Grapes"
)+", "
);
z := left(z,len(z)-2)
);
z

Did I miss something? because It's not working out. I've tried refreshing and still have problems 1 & 2.

 

[CoSpringsGuy]

There must be something in your data that is not being understood. I have tested this with my own string data created as you described and it works flawlessly.

Perhaps someone else has some input.

Clumsily thumbed from my iPhone....

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[lbass]

What is the ACTUAL formula as you implemented it? Can the field be null?

-LB

 

[msi30502]

Here's the actual formula:

stringvar x := {LineItem.CustomField3};
stringvar array y := split(x," ");
numbervar i;
stringvar z := "";
if ubound >0 then
(
for i := 1 to ubound do(
z := z +
(select y
case "01" : "Africa"
case "02" : "Argentina"
case "03" : "Australia"
case "04" : "Brazil"
case "05" : "Chile"
case "06" : "Colombia"
case "07" : "Costa Rica"
case "08" : "Cuba"
case "09" : "El Salvador"
case "10" : "Fiji"
case "11" : "Guam"
case "12" : "Guatemala"
case "13" : "Hawaii"
case "14" : "Honduras"
case "15" : "Jamaica"
case "16" : "Mexico"
case "17" : "New Zealand"
case "18" : "Nicaragua"
case "19" : "Philippines"
case "20" : "Peru"
case "21" : "Puerto Rico"
case "22" : "Samoa"
case "23" : "Tonga"
case "24" : "Venezuela"
case "25" : "Middle East"
case "26" : "India"
case "27" : "S. East Asia"
case "28" : "Central America"
case "29" : "Equador"
case "30" : "Dominican Republic"
case "31" : "Haiti"
case "99" : "ALL COUNTRIES"
case "W" : "WIC"
case "*" : "Unknown"
case "N" : "New"
)+", "
);
z:=left(z,len(z)-2)
);
z
========================================

Regarding NULL values. If you mean must there be a value assigned when the entering information in the field, then, no. The database allowes null values upon data entry.

Look forward to any help....

 

[msi30502]

Whoa! This is Crazy. I copied and pasted the same formula into another Crystal Reports file, and it worked!!!

Maybe one file is corrupt. I hope that it doesn't happen again.

 

[CoSpringsGuy]

good! Glad it worked .. I couldnt figure out why it wouldnt..

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection