Calculation of text string

[rkass068]

Hello I am currently making a database for cancer research in the hospital I work at and I am trying to set a calculation field that will calculate certain regimens of chemotherapy and with summarize them as their generic name.

 

For example,

 

I have a field called "regimen" that is a text box containing the value list:

Dex

Bortezomib

CTX

Cisplatin

Adriamycin

Etoposide

Vincristine

 

For the summary field I want it to calculate whats in the regimen field and give me a chemotherapy line

 

For example, Bortezomib + Dex =Velcade

Bortezomib + dex + CTX = CyBorD

Cisplatin = DHAP

 

etc

 

I know I will have to include the paragraph symbol and I tried using the value count and filter values calculation but I fall into the problem with CyBorD and Velcade since both use Bortezomib and Dex. 

 

Thank so much for the help

 

 

[Fitch]

Case statements evaluate in order, so if you put the CyBorD first that's one problem solved. E.g.:

Let([

Dex = Position( regimen ; "Dex" ; 1 ; 1 ) ;
Bortezomib = Position( regimen ; "Bortezomib" ; 1 ; 1 ) ;
CTX = Position( regimen ; "CTX" ; 1 ; 1 ) ;
Cisplatin = Position( regimen ; "Cisplatin" ; 1 ; 1 ) ;
// etc

result = 
Case( 
  Bortezomib & Dex & CTX ; "CyBorD" ; 
  Bortezomib & Dex ; "Velcade" ; 
  Cisplatin ; "DHAP"
    )
];

result
)

Edited November 22, 2013 by Fitch
Be sure to fix the calc (replace the "&" with "and") as described below!

[comment]

For the summary field I want it to calculate whats in the regimen field and give me a chemotherapy line

 

For example, Bortezomib + Dex =Velcade

Bortezomib + dex + CTX = CyBorD

Cisplatin = DHAP

 

It's not quite clear what is the intended result here, since the Regimen field can meet more than one criteria. If you want a single result, you must specify some rules of preference.

[comment]

Dex = Position( regimen ; "Dex" ; 1 ; 1 ) ;

 

This is not a good test when looking at a return separated list; it's easy to get a false positive when the list contains "Dexomicyl", "Antidexant" or "Curadex". Better use =

Dex = not IsEmpty ( FilterValues ( "Dex" ; Regimen ) )

etc.

[Fitch]

I agree -- I did the quick/dirty version, sufficient for the given values, but it's best to future-proof it.

[comment]

Wait a minute: I now notice something else, too. I might have swallowed the Position() thing, but the real Tom Fitch would not write "&" instead of "and". You must be either an identity thief or a body snatcher.

[rkass068]

Thank you all for the responses, I will try this out now. However, what I am afraid of is will this allow for any order of the checkboxes to be clicked or will they have to click the boxes in order that is stated in the formula? thanks

[Raybaudi]

Isn't "body snatcher" too cruent ?

[comment]

What does "cruent" mean?

[Raybaudi]

Cruel ?  Sanguinary ?

 

( barbarity, bloodthirstiness, murderousness, truculence, brutishness )

[comment]

I'm not sure you realize it's a reference to a cultural icon:

http://en.wikipedia.org/wiki/Invasion_of_the_Body_Snatchers

[Raybaudi]

Yes, I didn't realize that because of this: http://thesaurus.com/browse/Body%20Snatcher

[Fitch]

Thank you all for the responses, I will try this out now. However, what I am afraid of is will this allow for any order of the checkboxes to be clicked or will they have to click the boxes in order that is stated in the formula? thanks

 

Clicked in any order is OK. What's important is the order that each item is evaluated in the Case calculation, because as soon as it hits one that is true, it stops evaluating.

 

Be sure to replace the "&" with "and" in my formula, and even better, use Mr. comment's variation.

 

Those pesky body snatchers...

[LaRetta]

Happens to me all the time ... every time it seems I make a mistake that's actually when they have snatched my body.  It can be a real problem. 

[rkass068]

Clicked in any order is OK. What's important is the order that each item is evaluated in the Case calculation, because as soon as it hits one that is true, it stops evaluating.

 

Be sure to replace the "&" with "and" in my formula, and even better, use Mr. comment's variation.

 

Those pesky body snatchers...

Thanks a lot for the advise, it did exactly what you said to do with Mr. Comment's variation and its working perfectly. This is the best FM forum, every time I post something it gets answered perfectly. I will probably have to post another question soon hahaha