Using Partial text in an IF statement in Expression Editor for Report Template Editor (RTE) in OpenLAB CDS 2.6

Hello, I have OpenLAB CDS 2.6 and have a question regarding the Expression Editor in the Report Template Editor (RTE).

My question is how I can setup an IF statement for partial text. For example, I have 6 varying ways the B1 units can be expressed as (mg/serving, mg/tablet, and mg/cap; and the equivalent in mcg).

Background:

I can setup a basic IF statement using this basic structure, =IF(CFE (Sample_CustomFields,"B1 units") ="mg/serving", IF(CFE (Sample_CustomFields,"B1 units") ="mg/cap"... but then I have to repeat the IF statement 6 times. Thus, I was wondering if there was a way to perform an IF statement in the RTE using partial text like in Excel:

=IF(COUNTIF(A2,"*mg*"),"Value if true","Value if false")

If there is, could I get assistance with the appropriate syntax if this is possible?

0 martin.adams 17 days ago

Hello,

I am not sure exactly what you are doing but to use part of the text in an if you can parse apart the text using string functions or use the like comparison. So your =IF(COUNTIF(A2,"*mg*"),"Value if true","Value if false") would be =If(A2 like "*mg*", "Value if true", "Value if false") where A2 is the CDS string value and mg is anywhere in that string to resolve true. Please not you can also use a switch function in place of nested If statement when comparing a field to multiple values. See the example below where I use the copied switch expression in the conversion column.

=Switch(Compound_AmountUnit = "ug/mL", 1, Compound_AmountUnit = "ppm", 1 , Compound_AmountUnit = "ppb", 1000,Compound_AmountUnit = "mg/mL",.001,Compound_AmountUnit = "ng/mL",1000)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Was this helpful?