A variation on the substitute function

[Jason Goldsmith]

I need to do the following:

If a word in present in a field (for example, the word "blue") then it is replaced by the word, "green". I use the Substitute function for this. But if the word "blue" is not present, then I want to return the word "yellow". How can I do this?

Thanks so much,

Jason

[-Queue-]

You mean like Case( PatternCount( field, "blue" ), Substitute( field, "blue", "green" ), "yellow" ) ?

[Fitch]

Just in case you didn't know, the Substitute function can also be nested like this:

Substitute(

Substitute(

Substitute(

field,

"blue", "green" ),

"red", "yellow" ),

"pink", "purple" )

[-Queue-]

You mean like Lower( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( c.t.description|major, "

[Jason Goldsmith]

What I need is:

If blue, then green

If not blue, then yellow

I may be wrong, but I don't think the suggested nested Substitute function will deliver this result:

Substitute(

Substitute(

Substitute(

field,

"blue", "green" ),

"red", "yellow" ),

"pink", "purple" )

My application is for writing eye drug prescriptions. Most eye drug are drops. So if you select Drug A, the prescription reads, " Drop A: 1 drop 4 times per day". But a few eye drugs are ointments, and read, "Drug B: 1 strip 4 times per day". The drug value list contains drops by drug name alone, but ointments are listed as Drug name + "ointment". So, if the value list choice contains the word "ointment", then the the word "drop" becomes "strip". If the value list choice does not contain the word "ointment", then the calculation returns "drop".

Thus,

If blue, then green

If not blue, then yellow

There are probably better ways to design this, were I to start over, such as having a field with drop or ointment as a choice, but on the other hand, this is just one more thing to click and slow the user down. So, I'd like to solve this with a calculation or script. Thanks for your advice!

Jason

[-Queue-]

Did you try my Case() calc from way up /| there?

[Ugo DI LUCA]

or

Substitute(Prescription, "Drop",

Case(Drug="ointment","STRIP","DROP"))

So if you select Drug A, the prescription reads, " Drop A: 1 drop 4 times per day". But a few eye drugs are ointments, and read, "Drug B: 1 strip 4 times per day".

or rather use something like :

c_Prescription = DrugSelected & " "& NumberOfDrop & " "& Case(DrugSelected = "Ointment", "Strip", "Drop") & " " & Interval & " " & "times per day"

[Jason Goldsmith]

Queue,

I've looked your suggestion, but still can't get it to work. I've attached a file to illustrate my problem. If I select "Antibiotic" from the Drug field, it works fine (returning, "Antibiotic: 1 drop four times per day")

But if I select "Antibiotic ointment", I don't get my desired result: "Antibiotic Ointment: 1 strip four times per day".

Thanks for your help,

Jason

[Jason Goldsmith]

Here's the attachment

Test1.fp5.zip

[-Queue-]

Change your substitution field to: Case( PatternCount(Drug, "Ointment"), "strip", "drop" )

The way you are doing it, you're really not substituting and could include this calculation in your Prescription field.

[Jason Goldsmith]

That solves the problem. Thanks!!

[Fitch]

Jason, you are right, my example didn't address your specific question. I only offered for the sake of completeness -- and because sometimes, people don't ask exactly what they mean. Glad to see your problem resolved.

-Q-, here in the forums, I generally use the Quick Reply box at the bottom of the page. So, although my post may have said "in response to -Q-," it was really more of a general comment. I was not intending to imply that you didn't know how to use a common function.

[-Queue-]

I figured as much, Tom. I just enjoyed exaggerating the point excessively.