inclusive select

[chris35]

I feel like a dunce, but I am drawing a blank right now. How do I select for a list of certain items and is inclusive of records that have all the items and not just some?

EX: {field.testcode} = "Amy, Lips, SGOT, and SGPT", inclusively. Not just one or more the above list.

Thanks.

 

[rhinok]

I think we need more information regarding your data. Since you can't have more than one value for any field, I'm guessing you mean something different.

Does the field 'testcode' normally contain an array of values?

If not, can multiple test codes be related to one specific record and are you trying to return records where all of these multiple test codes are attached to a specific record?

 

[synapsevampire]

Try:

{field.testcode} like "*Amy*"
and
{field.testcode} like "*Lips*"
and
etc.

This assumes that you want all of those in the field, there appears to be a typo or a language barrier at work:

What does "Inclusively. Not just one or more the above list." mean?

Does this mean that it must contain all of these words?

If just any of them, use:

field.testcode} in ["Amy, Lips, SGOT, SGPT"]

Perhaps you could provide example data and expected output?

-k

 

[synapsevampire]

Ooops:

{field.testcode} in ["Amy, Lips, SGOT, SGPT"]

-k

 

[synapsevampire]

<swatting myself around the room>

Ooops:

{field.testcode} in [&quot;Amy&quot;, &quot;Lips&quot;, &quot;SGOT&quot;, &quot;SGPT&quot;]

 

[chris35]

Sorry I did not provide enough info. 'Test code' is any of hundreds of tests that can be ordered on one patient sample. A record would have multiple test codes ordered. I want to collect those records that have all 4 tests (amy,lips,sgot, and sgpt) ordered. Does that help I hope?

 

[lbass]

I think you are using &quot;record&quot; to mean patient record. If you placed {table.patientID} and {field.testcode} on the report canvas and ran the report, I'm guessing you would have multiple records, one for each test code. If that is the case, then you could create a record select formula like:

{field.testcode} in [&quot;Amy&quot;, &quot;Lips&quot;, &quot;SGOT&quot;, &quot;SGPT&quot;]

Then create four formulas for the details section:

{@Amy}:
if {field.testcode} = &quot;Amy&quot; then 1 else 0

{@Lips}:
if {field.testcode} = &quot;Lips&quot; then 1 else 0

{@SGOT}:
if {field.testcode} = &quot;SGOT&quot; then 1 else 0

{@SGPT}:
if {field.testcode} = &quot;SGPT&quot; then 1 else 0

Group on {table.patientID} and then go to report->edit selection formula->GROUP and enter:

sum({@Amy},{table.patientID}) > 0 and
sum({@Lips},{table.patientID}) > 0 and
sum({@SGOT},{table.patientID}) > 0 and
sum({@SGPT},{table.patientID}) > 0

This will return only those patients (and their associated details) who have had all four tests.

-LB

 

[chris35]

lbass---Thanks a bunch. you were correct about what I was working with and the formulas worked great!!!