string replace with if\else logic not working-ish...

[andeefitzpeabs]

I'm writing a formula that replaces a string "recommendation" (initially created as Null()) with "this is the recommendation"

I currently have 4 different attempts to make this work in my code, and none of them are working.  

([string], [string], 'replacement text')

([string, '[string]', 'replacement text')

([string], '', 'replacement text')

([string], NULL(), 'replacement text')

I know SOMETHING is happening because my output changes from NULLs to '' (empty string).

(FWIW, yes, the #'s are in numeric computable form for the if else logic.)

Anyone have any suggestions?

if ([Size1]>=5.5)
then Replace([recommendation], [recommendation], 'referral to vascular surgeon')

elseif (([Size1]>=4.5) && ([Size1]<=5.4))
then Replace([recommendation], [recommendation], 'follow-up every 6 months and recommend vascular consultation')

elseif (([Size1]>=4.0) && ([Size1]<=4.4))
then Replace([recommendation], '[recommendation]', 'follow-up every 12 months and recommend vascular consultation')

elseif (([Size1]>=3.5) && ([Size1]<=4.0))
then Replace([recommendation], '', 'follow-up every 12 months')

elseif (([Size1]>=3.0) && ([Size1]<=3.4))
then Replace([recommendation], NULL(), 'follow-up every 3 years')

else ([recommendation])
endif

[patrick_digan · Accepted solution]

@andeefitzpeabs Great question! The formula tool by default will replace the entire contents of the cell with whatever formula you write. So I don't think you would ever need a Replace([Field],[Field],"any text"); instead you can just write "any text". 

I think your situation would be something like:

if ([Size1]>=5.5)
then 'referral to vascular surgeon'
 
elseif (([Size1]>=4.5) && ([Size1]<=5.4))
then 'follow-up every 6 months and recommend vascular consultation'
 
elseif (([Size1]>=4.0) && ([Size1]<=4.4))
then 'follow-up every 12 months and recommend vascular consultation'
 
elseif (([Size1]>=3.5) && ([Size1]<=4.0))
then 'follow-up every 12 months'
 
elseif (([Size1]>=3.0) && ([Size1]<=3.4))
then 'follow-up every 3 years'
 
 
else ([recommendation])
endif

You may want to post some dummy data if I haven't quite understood your situation correctly.

[MarqueeCrew · Accepted solution]

This can be simplified. If the size is greater than 5.5 condition is met, then the next condition only needs to test for greater than or equal to 4.5 and so on.

It makes your code more efficient and easier to maintain.

Cheers,
Mark

[andeefitzpeabs]

Derp.  The brain went down one set of tracks and was P-o-s-i-t-i-v-e I needed a Replace function. 
Thanks for pointing out the "elegant" solution.  ;)