Compare and strip formatting

[Drus]

I need to strip / mask a field if it matches another field within the same record.

For example, I have these two fields along with an example of their corresponding values:

{IM.Category3} = CATALANA
{IM.Description} = CATALANA FENDI GOLD
= CATALANA FENDO BONE
= CATALANA FENDO WHITE

I need a formula that will strip out "CATALANA" from the {IM.Description} field when it sees that {IM.Category3} in that same record contains "CATALANA".

In other words, whatever the record sees in {IM.Category3} must be stripped away from {IM.Description}. Andrew Hagenbach
OPUS SYSTEMS, INC.
916.503.3173
ahagenbach@opus-sys.com

 

[synapsevampire]

Try a formula in lieu of the description field as in:

if instr({IM.Description}, {IM.Category3}) = 0 then
{IM.Description}
else
if instr({IM.Description}, {IM.Category3}) > 0 then
if instr({IM.Description}, {IM.Category3}) = 1 then
trim(mid({IM.Description}, instr({IM.Description}, {IM.Category3}) +len({IM.Category3})))
else
if instr({IM.Description}, {IM.Category3}) > 1 then

left({IM.Description}, instr({IM.Description}, {IM.Category3})-1)
+trim(mid({IM.Description}, instr({IM.Description}, {IM.Category3}) +len({IM.Category3})))

I took the liberty of returning everything within the description (both sides of the found text) to cover if the category is not just at the start of the description as in your example.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Drus]

That is exactly what I need; however, I know realize that I will also need to compare DESCRIPTION with another field called ITEM NUMBER to strip any of that away as well. Andrew Hagenbach
OPUS SYSTEMS, INC.
916.503.3173
ahagenbach@opus-sys.com

 

[synapsevampire]

The same theory applies.

I see that you're in the Northern California area, I have a residence up there too (Elk Grove, which I affectionately call Mugwamp Hollar), where are you?

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Drus]

Sorry I've been caught up in some projects. I am in the Roseville area. It's lovely up here.

As for the report, I have tried to apply the same theory but to no avail.

Here is what I have:

if instr({IM.Description}, {IM.Category3},{IM.Category1}) = 0 then
{IM.Description}
else
if instr({IM.Description}, {IM.Category3},{IM.Category1}) > 0 then
if instr({IM.Description}, {IM.Category3},{IM.Category1}) = 1 then
trim(mid({IM.Description}, instr({IM.Description}, {IM.Category3},{IM.Category1}) +len({IM.Category3},{IM.Category1})))
else
if instr({IM.Description}, {IM.Category3},{IM.Category1}) > 1 then

left({IM.Description}, instr({IM.Description}, {IM.Category3},{IM.Category1})-1)
+trim(mid({IM.Description}, instr({IM.Description}, {IM.Category3},{IM.Category1}) +len({IM.Category3},{IM.Category1})))
&ru