How to Change Multiple Values in a String

[beacon5]

Hi,

[Crystal Reports 2008]

There's a field in the database I'm writing my report against that has multiple values in a single string that are separated by a semi-colon (. This field is populated by a list box that has check boxes in it.

When the values are added to the table, the full string is used to identify it. In the report I'm writing, I won't have room for the entire string, so I want to change the values to abbreviations.

So, if I have the following in the table:
Asthma
Diabetes
Heart Condition
Weight Reduction

And it appears like the following when I include the field on a report:
Asthma;Diabetes;Heart Condition;Weight Reduction

And want them to appear as the following on my report:
AS DI HC WR

How would I go about doing this? I thought about using the Instr and Replace functions in a nested If statement, but that will only replace one item at a time. Then I thought about using a loop, but I don't have a lot of experience using loops in Crystal Reports and am somewhat hesitant. I also thought about using two arrays and comparing the values at each position, but I can't guarantee that the values will always appear in the same position in the string.

If anyone can help, I would really appreciate it.

Thanks,
beacon

 

[SkipVought]

Replace 4 times.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[beacon5]

Are you saying that I'll need to create 4 separate formulas that each use Replace or am I to use Replace 4 times in the same formula?

 

[Turkbear]

Hi,
Try something like this:

@MakeAbbrList
NumberVar x := 1;
Stringvar Listing :="";
StringVar Array Conditions := Split({TableStringField},";");
for x := 1 to Ubound(Conditions) do
Listing := Listing + Left(Conditions[x],2) + " ";
RTrim(Listing);

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi Sorry!!,
OOPS, missed handling the 2 word descriptions but some variant of what I posted should get you there :
Make an array of the values in the string
Loop through that Array and parse the 1st letter of each word - using Instr to find the space, if any, and MID to get the first letter after the space and concatenate each 2 letter string with spaces.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[beacon5]

Hi Turkbear,

I realize that my first post makes it appear as though the words that I want to replace would be replaced by parsing the first letter of each word and I apologize for not being clearer with my example.

In actuality, some of the identifiers (the two letters for the words) cannot be created by parsing the word. For instance, "Cholesterol, Low" is a word used by the database, but the identifier for it is "LC". So, I would need to be able to replace certain words with identifiers that I'm assuming I will have to declare separately.

Would I just add if...then statements to the loop to test the value at the current condition in the array and replace it with the value I want? I've got something like 20 of these to use, so if I could process this without having to create conditional statements for each item that would be ideal, but I understand if there's simply no way around it.

Thanks,
beacon

 

[TeyBrady]

I'm with Skip on using replace but also replace the semicolon with nothing while you are doing the other four replaces. The key is to nest the replace calling it with the result of another replace.

Build it one step at a time.
replace(x, ";", "")
becomes
replace(replace(x, ";", ""), "Asthma", "AS ")

etc.

 

[beacon5]

Thanks TeyBrady, but the problem with Skip's solution is that I don't know how many items will be in the list. My example shows four, but there could be as many as 20 in the list...and they could be in any order in the list.

 

[lbass]

The order doesn't matter (there is one exception--where an outer replace changes an already replaced value to something else, but this is unlikely to happen in your case), and you can nest all 20 (or whatever your maximum is) items in one formula. Did you try this?

-LB

 

[TeyBrady]

It doesn't matter what order you call the nested replace function. You could bury it 20 deep.

Another option is to write a database function that does the same thing and you can request the field as an SQL object. Putting it on the database makes it easier to do the same thing on multiple reports. You only have one place to update that way. So when they add the 21st code you will only need to change the function and not any of the reports. If you get creative by creating a table to hold the values you could write your function so all you have to do is maintain the table cross reference of your mapping.

 

[beacon5]

@lbass - No, I hadn't tried it yet because I was trying to find a way to create to split the field into an array, then loop through the array and change the values.

Here's what I've got so far (which isn't working):

// ModifiedDiet
//

StringVar AR := "Anti-Reflux";
StringVar CC := "Calorie Controlled";
StringVar LC := "Cholesterol, Low";
StringVar MC := "Cholesterol, Modified";
StringVar CL := "Clear Liquid";
StringVar DI := "Diabetic";
StringVar ER := "Egg Restricted";
StringVar FR := "Fluid Restriction";
StringVar FL := "Full Liquid";
StringVar GF := "Gluten Free";
StringVar HC := "High Calorie";
StringVar LR := "Lactose Restricted";
StringVar NSA := "No Salt Added";
StringVar Ot := "Other";
StringVar PKU := "PKU";
StringVar P40 := "Protein, 40gm";
StringVar P60 := "Protein, 60gm";
StringVar SR := "Sodium Restricted";
StringVar VLO := "Vegetarian Lacto-Ovo";
StringVar VS := "Vegetarian, Strict";
StringVar WM := "Weight Maintenance";
StringVar WR := "Weight Reduction";

StringVar Array Modifications;
StringVar Result;
NumberVar Limit;
NumberVar i;

Limit := UBound(Split({MHMR_DIET_ORDER.MODIFICATIONS_SHVAL}, ";"));

Modifications := Split({MHMR_DIET_ORDER.MODIFICATIONS_SHVAL}, ";");

For i := 1 to Limit Do
( 
    If Modifications[i] = AR Then
        Result := "AR "
    Else If Modifications[i] = CC Then
        Result := "CC "
    Else If Modifications[i] = LC Then
        Result := "LC "
    Else If Modifications[i] = MC Then
        Result := "MC "
    Else If Modifications[i] = CL Then
        Result := "CL "
    Else If Modifications[i] = DI Then
        Result := "DI "
    Else If Modifications[i] = ER Then
        Result := "ER "
    Else If Modifications[i] = FR Then
        Result := "FR "
    Else If Modifications[i] = FL Then
        Result := "FL "
    Else If Modifications[i] = GF Then
        Result := "GF "
    Else If Modifications[i] = HC Then
        Result := "HC "
    Else If Modifications[i] = LR Then
        Result := "LR "
    Else If Modifications[i] = NSA Then
        Result := "NSA "
    Else If Modifications[i] = Ot Then
        Result := {MHMR_DIET_ORDER.SPECIFY_MODIFICATIONS} & " "
    Else If Modifications[i] = PKU Then
        Result := "PKU "
    Else If Modifications[i] = P40 Then
        Result := "P40 "
    Else If Modifications[i] = P60 Then
        Result := "P60 "
    Else If Modifications[i] = SR Then
        Result := "SR "
    Else If Modifications[i] = VLO Then
        Result := "VLO "
    Else If Modifications[i] = VS Then
        Result := "VS "
    Else If Modifications[i] = WM Then
        Result := "WM "
    Else If Modifications[i] = WR Then
        Result := "WR "
    Else
        {MHMR_DIET_ORDER.MODIFICATIONS_SHVAL}
);

I also tried using Modifications := "WR " and that didn't work either. My formula keeps returning a Boolean value.

If this isn't possible, then I'll use the Replace method...I didn't want to use it at first because I wasn't sure if it would replace parts of the string incorrectly, as you mentioned. But if you don't think that's possible for what I'm trying to do, then I'll give it a shot.

I'd still like to know what I'm doing wrong with the above code if it is possible to make it work.

Thanks,
beacon

 

[TeyBrady]

I think you need to code the results like this.
Result := Result + "XX "

You probably can change the last line from
{MHMR_DIET_ORDER.MODIFICATIONS_SHVAL}
to
Result := Result + Modifications + " "
to pick up any new value you have were not expecting like "The Cooties".

At the end add an extra line and just put this

Result;


For this many values a select case statement might look cleaner.

I'm not sure if this would be faster than the nested replaces. If you are not running for zillions of records you probably will not be able to tell the difference.

 

[beacon5]

That did the trick TeyBrady.

Here's the final code, although I will probably try to clean it up with a select case statement:

// ModifiedDiet
//

StringVar AR := "Anti-Reflux";
StringVar CC := "Calorie Controlled";
StringVar LC := "Cholesterol, Low";
StringVar MC := "Cholesterol, Modified";
StringVar CL := "Clear Liquid";
StringVar DI := "Diabetic";
StringVar ER := "Egg Restricted";
StringVar FR := "Fluid Restriction";
StringVar FL := "Full Liquid";
StringVar GF := "Gluten Free";
StringVar HC := "High Calorie";
StringVar LR := "Lactose Restricted";
StringVar NAS := "No Added Salt";
StringVar Ot := "Other";
StringVar PKU := "PKU";
StringVar P40 := "Protein, 40gm";
StringVar P60 := "Protein, 60gm";
StringVar SR := "Sodium Restricted";
StringVar VLO := "Vegetarian Lacto-Ovo";
StringVar VS := "Vegetarian, Strict";
StringVar WM := "Weight Maintenance";
StringVar WR := "Weight Reduction";
StringVar Modx;
StringVar Mods;
StringVar Array Modifications;
Local StringVar Result;
NumberVar Limit;
NumberVar i;

Modx := Left({MHMR_DIET_ORDER.MODIFICATIONS_SHVAL}, Len({MHMR_DIET_ORDER.MODIFICATIONS_SHVAL})-1);
Mods := Mid(Modx, 2);

Limit := UBound(Split(Mods, "&"));

Modifications := Split(Mods, "&");

For i := 1 to Limit Do
( 
    If Modifications[i] = AR Then
        Result := Result + "AR "
    Else If Modifications[i] = CC Then
        Result := Result + "CC "
    Else If Modifications[i] = LC Then
        Result := Result + "LC "
    Else If Modifications[i] = MC Then
        Result := Result + "MC "
    Else If Modifications[i] = CL Then
        Result := Result + "CL "
    Else If Modifications[i] = DI Then
        Result := Result + "DI "
    Else If Modifications[i] = ER Then
        Result := Result + "ER "
    Else If Modifications[i] = FR Then
        Result := Result + "FR "
    Else If Modifications[i] = FL Then
        Result := Result + "FL "
    Else If Modifications[i] = GF Then
        Result := Result + "GF "
    Else If Modifications[i] = HC Then
        Result := Result + "HC "
    Else If Modifications[i] = LR Then
        Result := Result + "LR "
    Else If Modifications[i] = NAS Then
        Result := Result + "NAS "
//    Else If Modifications[i] = Ot Then
//        Result := Result + {MHMR_DIET_ORDER.SPECIFY_MODIFICATIONS} & " "
    Else If Modifications[i] = PKU Then
        Result := Result + "PKU "
    Else If Modifications[i] = P40 Then
        Result := Result + "P40 "
    Else If Modifications[i] = P60 Then
        Result := Result + "P60 "
    Else If Modifications[i] = SR Then
        Result := Result + "SR "
    Else If Modifications[i] = VLO Then
        Result := Result + "VLO "
    Else If Modifications[i] = VS Then
        Result := Result + "VS "
    Else If Modifications[i] = WM Then
        Result := Result + "WM "
    Else If Modifications[i] = WR Then
        Result := Result + "WR "
    Else
        Result := Result + "*Other* "//Modifications[i] + "* "
);

Result;

I took it one step further and decided to alphabetize the array once I got it working using the following (borrowed and tweaked) code in a separate formula (except the *Other* value, which I moved to the end of the alphabetized string):

// SortModifiedDiet

StringVar Diet;
StringVar Array Sort;
NumberVar i;
NumberVar j;
Local StringVar Temp;
Local StringVar Temp2;

Diet := {@ModifiedDiet};

Sort := Split(Diet, " ");

For i := 1 To UBound(Sort) - 1 Do
(
    For j := 1 To UBound(Sort) - i Do
    (
        If Sort[j] = "*Other* " Then
        (
            Temp2 := Sort[j];
            Sort[j] := ""
        )
        Else If Sort[j] > Sort[j + 1] Then
        (
            Temp := Sort[j];
            Sort[j] := Sort[j + 1];
            Sort[j + 1] := Temp
        )
    )
);

Trim(Join(Sort," ")) & " " & Temp2;

Thanks everybody for your help,
beacon