bshelby777
asked on
Crystal Reports-Need way of specifying multiple unnested IFs in formula
I am filtering out substrings in a database comment field with the following example code:
if InStr({MyTable.Comment},"R ed") = 1 then
replace(replace({MyTable.C omment}, "Red","")," "," ")
else if InStr({MyTable.Comment},"B lue") = 1 then
replace(replace({MyTable.C omment}, "Blue","")," "," ")
else
{MyTable.Comment}
This works perfectly if "Red" is in the comment in one row and "Blue" is in the comment in a different row, but what if "Red" and "Blue" are in the same comment? Plus, there may be other substrings in the comment that should not be filtered:
DATABASE COMMENT PRINT ON REPORT
Green Green
Red
Blue
RedYellow Green Yellow Green
RedBlue
BlueYellowRed Yellow
Yellow Cyan Green Yellow Cyan Green
I really need the ability to have independent, unnested IF statements in the formula:
if InStr({MyTable.Comment},"R ed") = 1 then
replace(replace({MyTable.C omment}, "Red","")," "," ")
If InStr({MyTable.Comment},"B lue") = 1 then
replace(replace({MyTable.C omment}, "Blue","")," "," ")
If none of the above <-- pseudocode... unsure of how to specify this in exact code
{MyTable.Comment}
or (in pseudocode, kind of)....
If substring of comment contains "Red" or "Blue"
if InStr({MyTable.Comment},"R ed") = 1 then
replace(replace({MyTable.C omment}, "Red","")," "," ")
If InStr({MyTable.Comment},"B lue") = 1 then
replace(replace({MyTable.C omment}, "Blue","")," "," ")
else
{MyTable.Comment}
When I do this, however, I get the error, "The remaining text does not appear to be part of the formula". How can I filter on multiple, independent conditions? I can't specify all the permutations of color combinations that could be in the comment - there would be way too many.
Important Note: There will never be the condition when the same color appears twice in the comment string:
RedGreenRed <-- won't happen
BlueBlue Blue <-- won't happen
In lieu of sugestions, can you please respond with the exact code and an explanation (if any) of why it needs to be done a certain way. Thanks in advance.
if InStr({MyTable.Comment},"R
replace(replace({MyTable.C
else if InStr({MyTable.Comment},"B
replace(replace({MyTable.C
else
{MyTable.Comment}
This works perfectly if "Red" is in the comment in one row and "Blue" is in the comment in a different row, but what if "Red" and "Blue" are in the same comment? Plus, there may be other substrings in the comment that should not be filtered:
DATABASE COMMENT PRINT ON REPORT
Green Green
Red
Blue
RedYellow Green Yellow Green
RedBlue
BlueYellowRed Yellow
Yellow Cyan Green Yellow Cyan Green
I really need the ability to have independent, unnested IF statements in the formula:
if InStr({MyTable.Comment},"R
replace(replace({MyTable.C
If InStr({MyTable.Comment},"B
replace(replace({MyTable.C
If none of the above <-- pseudocode... unsure of how to specify this in exact code
{MyTable.Comment}
or (in pseudocode, kind of)....
If substring of comment contains "Red" or "Blue"
if InStr({MyTable.Comment},"R
replace(replace({MyTable.C
If InStr({MyTable.Comment},"B
replace(replace({MyTable.C
else
{MyTable.Comment}
When I do this, however, I get the error, "The remaining text does not appear to be part of the formula". How can I filter on multiple, independent conditions? I can't specify all the permutations of color combinations that could be in the comment - there would be way too many.
Important Note: There will never be the condition when the same color appears twice in the comment string:
RedGreenRed <-- won't happen
BlueBlue Blue <-- won't happen
In lieu of sugestions, can you please respond with the exact code and an explanation (if any) of why it needs to be done a certain way. Thanks in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mlmcc - Excellent! That code was exactly what I needed. 460 points.
James0628 - Very helpful example when there are only a few strings. 40 points.
Thank you both for your responses - they were very helpful. I wish there were more than 500 points to give!
James0628 - Very helpful example when there are only a few strings. 40 points.
Thank you both for your responses - they were very helpful. I wish there were more than 500 points to give!
You're welcome. Glad I could help.
James
James
mlmcc