Identify codes in multiple fields, formula to

[luxvivens]

I am using CR 10.5
I have various procedure codes that I must identify to see if they exist in 25 different diagnosis (DX) fields. If they do, I want to see those records on my report.

The field names are "DX1 to DX25". The procedure code values are 410.01, 410.11, 410.21....up to about 20 codes And, I must check for existence of these same procedure codes in fields DX1 and DX2 and DX3 and DX4 to DX25.

Requesting help for a formula that will check for the various procedure code values in each of the 25 DX fields.

Might this be done via a formula (and help with that formula too) in my selection criteria ? Is there another option better?

Example:
Account DX1 DX2 DX3
123 920.01 450.21 335.30
456 880.34 401.11 773.99
789 667.12 882.11 410.01

In the example above I would want to see account 456 and 789 since they contain the codes I need to see.

Thanks in advance for any suggestions and ideas

 

[lbass]

Can you clarify whether you want to see ALL DX fields in the range and any of the related procedure codes? Or do you want to see ALL procedure codes and any related DX fields? Are you working with multiple tables? What are they? Please identify your fields by using this convention: {table.field}.

-LB

 

[luxvivens]

-LB:
I wish to see if 410.01 or 410.11 or 410.21 exist in the Table01.field or the Table02.field or the Table03 field, etc.

If these codes exist, I wish to have the 410.xx value on the report. These are myocardial infarction codes.

If there are no 410.xx codes, I do not need to see any data for that record.

I am working with 25 table.fields and the 410.xx may be resident in any one of these table.fields.

I have taken a stap at this and done it, rather inelegantly, this way:
{POA_QM_Data_Sample_2.Primary Procedure Code} in ["81.51", "81.52", "81.54"] and
{table01.field} in ["410.01","410.11","410.21","410,31","410.41","410.51","410.61","410.71","410.81","410.91"] or
{table02.field} in ["410.01","410.11","410.21","410,31","410.41","410.51","410.61","410.71","410.81","410.91"] or
{table03.field} in ["410.01","410.11","410.21","410,31","410.41","410.51","410.61","410.71","410.81","410.91"]


-Lux

 

[lbass]

You must add parens so that the "or's" are interpreted correctly. You could also simply this by using:


POA_QM_Data_Sample_2.Primary Procedure Code} in ["81.51", "81.52", "81.54"] and
(
left({table01.field},3) = "410" or
left({table02.field},3) = "410" or
left({table03.field},3) = "410" //etc.
)

Or you could use:

POA_QM_Data_Sample_2.Primary Procedure Code} in ["81.51", "81.52", "81.54"] and
"410" in [left({table01.field},3),left({table02.field},3),left({table03.field},3)]

My earlier question was trying to get out how the tables should be linked so that you didn't omit records due to the table joins.

-LB

 

[luxvivens]

Thanks so much for the other ways of doing this, and also for the heads-up on the parens. Much simpler and economical YOUR way. Please accept my sincere thanks for your help and the timeliness and quality of your response.