Need help with "Replace" formula 1

[leftfldr]

Using Crystal 10 with Microsoft SQL Server and the ERP package of Microsoft Dynamics GP.

All information in the item database is stored in uppercase. For this particular crystal report we do not want all uppercase so I am using the propercase function first. When I use the propercase some initials and abbreviations are changed and should not be (ex. Gps should be GPS and Mci should be MCI). To combat that issue I am using this formula:

WhilePrintingRecords;
StringVar NotesVar;
NotesVar := propercase({CSC42100.ivdef6});
If InStr(NotesVar, "7Tz") > 0
then
Replace(Propercase(NotesVar), "7Tz", "7TZ")
else
If InStr(NotesVar, "10Ra") > 0
then
Replace(Propercase(NotesVar), "10Ra", "10RA")
else
If InStr(NotesVar, "10Rb") > 0
then
Replace(Propercase(NotesVar), "10Rb", "10RB")
else
If InStr(NotesVar, "7Rb") > 0
then
Replace(Propercase(NotesVar), "7Rb", "7RB")
else
If InStr(NotesVar, "Md") > 0
then
Replace(Propercase(NotesVar), "Md", "MD")
else
If InStr(NotesVar, "W/O") > 0
then
Replace(Propercase(NotesVar), "W/O", "w/o")
else
If InStr(NotesVar, " With") > 0
then
Replace(Propercase(NotesVar), " With", " with")
else
If InStr(NotesVar, "Rz") > 0
then
Replace(Propercase(NotesVar), "Rz", "RZ")
else
If InStr(NotesVar, "Or") > 0
then
Replace(Propercase(NotesVar), "Or", "or")
else
If InStr(NotesVar, "Non-T.S.O.'D") > 0
then
Replace(Propercase(NotesVar), "Non-T.S.O.'D", "Non-T.S.O.'d")
else
If InStr(NotesVar, "See New") > 0
then
Replace(Propercase(NotesVar), "See New", "Replace w/ new")
else
If InStr(NotesVar, "Replace With New") > 0
then
Replace(Propercase(NotesVar), "Replace With New", "Replace with New")
else
If InStr(NotesVar, "Replace w/ new") > 0
then
Replace(Propercase(NotesVar), "Replace w/ new", "Replace with New")
else
If InStr(NotesVar, "Atc") > 0
then
Replace(Propercase(NotesVar), "Atc", "ATC")
else
If InStr(NotesVar, "Mci") > 0
then
Replace(Propercase(NotesVar), "Mci", "MCI")
else
If InStr(NotesVar, "Gps") > 0
then
Replace(Propercase(NotesVar), "Gps", "GPS")
ELSE
Propercase(NotesVar)

The problem is is that if the line has more than one of these instances in it it only changes the first instance. For example: Use test or use Mci
This happens b/c the statement using "or" happens before "Mci".

How can I have it change all instances?

Thanks!

 

[bdreed35]

You need to make each if statement a seperate statement from the rest so they execute independently:

WhilePrintingRecords;
StringVar NotesVar := propercase({CSC42100.ivdef6});
If InStr(NotesVar, "7Tz") > 0 then NotesVar := Replace(NotesVar, "7Tz", "7TZ");
If InStr(NotesVar, "10Ra") > 0 then NotesVar := Replace(NotesVar, "10Ra", "10RA");
If InStr(NotesVar, "10Rb") > 0 then NotesVar := Replace(NotesVar, "10Rb", "10RB");
If InStr(NotesVar, "7Rb") > 0 then NotesVar := Replace(NotesVar, "7Rb", "7RB");
If InStr(NotesVar, "Md") > 0 then NotesVar := Replace(NotesVar, "Md", "MD");
If InStr(NotesVar, "W/O") > 0 then NotesVar := Replace(NotesVar, "W/O", "w/o");
If InStr(NotesVar, " With") > 0 then NotesVar := Replace(NotesVar, " With", " with");
If InStr(NotesVar, "Rz") > 0 then NotesVar := Replace(NotesVar, "Rz", "RZ");
If InStr(NotesVar, "Or") > 0 then NotesVar := Replace(NotesVar, "Or", "or");
If InStr(NotesVar, "Non-T.S.O.'D") > 0 then NotesVar := Replace(NotesVar, "Non-T.S.O.'D", "Non-T.S.O.'d");
If InStr(NotesVar, "See New") > 0 then NotesVar := Replace(NotesVar, "See New", "Replace w/ new");
If InStr(NotesVar, "Replace With New") > 0 then NotesVar := Replace(NotesVar, "Replace With New", "Replace with New");
If InStr(NotesVar, "Replace w/ new") > 0 then NotesVar := Replace(NotesVar, "Replace w/ new", "Replace with New");
If InStr(NotesVar, "Atc") > 0 then NotesVar := Replace(NotesVar, "Atc", "ATC");
If InStr(NotesVar, "Mci") > 0 then NotesVar := Replace(NotesVar, "Mci", "MCI");
If InStr(NotesVar, "Gps") > 0 then NotesVar := Replace(NotesVar, "Gps", "GPS");
NotesVar

~Brian

 

[bdreed35]

Another option would be to use a loop:

WhilePrintingRecords;
StringVar NotesVar := propercase({CSC42100.ivdef6});
local stringvar array bad_value := ["7Tz","10Ra","10Rb","7Rb","Md","W/O"," With","Rz","Or","Non-T.S.O.'D","See New","Replace With New","Replace w/ new","Atc","Mci","Gps"];
local stringvar array good_value := ["7TZ","10RA","10RB","7RB","MD","w/o"," with","RZ","or","Non-T.S.O.'d","Replace w/ new","Replace with New","Replace with New","ATC","MCI","GPS"];
local numbervar x;

for x := 1 to ubound(bad_value)
do
(
    if InStr(NotesVar, bad_value[x]) > 0 then NotesVar := Replace(NotesVar, bad_value[x], good_value[x]);
);

NotesVar

~Brian

 

[leftfldr]

PERFECT! Thanks so much for your help!