Criteria match = Y, no match = N but getting some blank lines that should be N

It does not appear like my code is fully evaluating the criteria. There are blanks where "N" should appear

The way it should evaluate is it should look for the CPT_CODe first, if a match is found then look to see if any of the DX codes exist in any position (DX1-DX6) then it will look to see if COPAY Due is > 0.00 and COPAY Collected is > 0.00. If all is true then "Y".    If anything past the CPT_CODE is not true then "N"

Preventive Y/N column - The blanks appear to be False after the CPT_CODe because no other criteria is found but do not indicate "N" they appear as Blanks and not sure why. See attachment for data.

Using formula Editor - Crystal  

if {Command.CPT_CODE} = ["44388", "44389", "44392", "44393", "44394", "44401", "45300", "45305", "45308", "45309", "45315", "45320", "45330", "45331", "45333", "45338", "45346", "45378", "45380", "45383", "45384", "45385", "45388", "74261", "74262", "74263", "G0104", "G0105", "G0106", "G0120", "G0121", "G6019", "G6024"]
and
(
{Command.DX1} = ["Z01.89","V10.00","V10.05","V10.06","V12.72","V12.79","V16.0","V18.51","V70.0","V72.31","V76.41","V76.50","V76.51","V76.52","792.1","Z00.00","Z00.01","Z01.411","Z01.419","Z12.10","Z12.11","Z12.12","Z12.13","Z80.0","Z83.71","Z85.00","Z85.038","Z85.048","Z86.010","Z87.19","R19.5"]
or {Command.DX2} = ["K64.8","V10.00","V10.05","V10.06","V12.72","V12.79","V16.0","V18.51","V70.0","V72.31","V76.41","V76.50","V76.51","V76.52","792.1","Z00.00","Z00.01","Z01.411","Z01.419","Z12.10","Z12.11","Z12.12","Z12.13","Z80.0","Z83.71","Z85.00","Z85.038","Z85.048","Z86.010","Z87.19","R19.5"]
or {Command.DX3} = ["V10.00","V10.05","V10.06","V12.72","V12.79","V16.0","V18.51","V70.0","V72.31","V76.41","V76.50","V76.51","V76.52","792.1","Z00.00","Z00.01","Z01.411","Z01.419","Z12.10","Z12.11","Z12.12","Z12.13","Z80.0","Z83.71","Z85.00","Z85.038","Z85.048","Z86.010","Z87.19","R19.5"]
or {Command.DX4} = ["V10.00","V10.05","V10.06","V12.72","V12.79","V16.0","V18.51","V70.0","V72.31","V76.41","V76.50","V76.51","V76.52","792.1","Z00.00","Z00.01","Z01.411","Z01.419","Z12.10","Z12.11","Z12.12","Z12.13","Z80.0","Z83.71","Z85.00","Z85.038","Z85.048","Z86.010","Z87.19","R19.5"]
or {Command.DX5} = ["V10.00","V10.05","V10.06","V12.72","V12.79","V16.0","V18.51","V70.0","V72.31","V76.41","V76.50","V76.51","V76.52","792.1","Z00.00","Z00.01","Z01.411","Z01.419","Z12.10","Z12.11","Z12.12","Z12.13","Z80.0","Z83.71","Z85.00","Z85.038","Z85.048","Z86.010","Z87.19","R19.5"]
or {Command.DX6} = ["V10.00","V10.05","V10.06","V12.72","V12.79","V16.0","V18.51","V70.0","V72.31","V76.41","V76.50","V76.51","V76.52","792.1","Z00.00","Z00.01","Z01.411","Z01.419","Z12.10","Z12.11","Z12.12","Z12.13","Z80.0","Z83.71","Z85.00","Z85.038","Z85.048","Z86.010","Z87.19","R19.5"]
)
and
(
{Command.COPAY_DUE} >0.00
and {Command.COPAY_COLLECTED}>0.00

)
 then  "Y" Else "N"
sample-data.xls
Culwatrnca11Data AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
I haven't looked at the spreadsheet but can any of the fields be NULL?
If so you have to test for that before evaluating the field

Crystal stops evaluating a formula if it encounters a NULL and returns an unknown value, quite often NULL
0
Culwatrnca11Data AnalystAuthor Commented:
Mlmcc,

yes, and thats what it feels like is happening..  any field after the CPT_CODE could be null.   how would you work the null into the code?
0
Raghavendra HullurSoftware DeveloperCommented:
As mlmcc said, you need to check for the fields with null values. I see {Command.COPAY_COLLECTED} is all null values. Hence I included another row of data with {Command.COPAY_COLLECTED} = 318211623969 and I modified the code you shared to check for null values.

Below is the code and I see that an Y is returned for the new data I added and all other values are N for the formula (displayed as last column in attached report).

if {sample_data.CPT_CODE} = ["44388", "44389", "44392", "44393", "44394", "44401", "45300", "45305", "45308", "45309", "45315", "45320", "45330", "45331", "45333", "45338", "45346", "45378", "45380", "45383", "45384", "45385", "45388", "74261", "74262", "74263", "G0104", "G0105", "G0106", "G0120", "G0121", "G6019", "G6024"]
and
(
(not isNull({sample_data.DX1}) and {sample_data.DX1} = ["Z01.89","V10.00","V10.05","V10.06","V12.72","V12.79","V16.0","V18.51","V70.0","V72.31","V76.41","V76.50","V76.51","V76.52","792.1","Z00.00","Z00.01","Z01.411","Z01.419","Z12.10","Z12.11","Z12.12","Z12.13","Z80.0","Z83.71","Z85.00","Z85.038","Z85.048","Z86.010","Z87.19","R19.5"])
or (not isNull({sample_data.DX2}) and {sample_data.DX2} = ["K64.8","V10.00","V10.05","V10.06","V12.72","V12.79","V16.0","V18.51","V70.0","V72.31","V76.41","V76.50","V76.51","V76.52","792.1","Z00.00","Z00.01","Z01.411","Z01.419","Z12.10","Z12.11","Z12.12","Z12.13","Z80.0","Z83.71","Z85.00","Z85.038","Z85.048","Z86.010","Z87.19","R19.5"])
or (not isNull({sample_data.DX3}) and {sample_data.DX3} = ["V10.00","V10.05","V10.06","V12.72","V12.79","V16.0","V18.51","V70.0","V72.31","V76.41","V76.50","V76.51","V76.52","792.1","Z00.00","Z00.01","Z01.411","Z01.419","Z12.10","Z12.11","Z12.12","Z12.13","Z80.0","Z83.71","Z85.00","Z85.038","Z85.048","Z86.010","Z87.19","R19.5"])
or (not isNull({sample_data.DX4}) and {sample_data.DX4} = ["V10.00","V10.05","V10.06","V12.72","V12.79","V16.0","V18.51","V70.0","V72.31","V76.41","V76.50","V76.51","V76.52","792.1","Z00.00","Z00.01","Z01.411","Z01.419","Z12.10","Z12.11","Z12.12","Z12.13","Z80.0","Z83.71","Z85.00","Z85.038","Z85.048","Z86.010","Z87.19","R19.5"])
or (not isNull({sample_data.DX5}) and {sample_data.DX5} = ["V10.00","V10.05","V10.06","V12.72","V12.79","V16.0","V18.51","V70.0","V72.31","V76.41","V76.50","V76.51","V76.52","792.1","Z00.00","Z00.01","Z01.411","Z01.419","Z12.10","Z12.11","Z12.12","Z12.13","Z80.0","Z83.71","Z85.00","Z85.038","Z85.048","Z86.010","Z87.19","R19.5"])
or (not isNull({sample_data.DX6}) and {sample_data.DX6} = ["V10.00","V10.05","V10.06","V12.72","V12.79","V16.0","V18.51","V70.0","V72.31","V76.41","V76.50","V76.51","V76.52","792.1","Z00.00","Z00.01","Z01.411","Z01.419","Z12.10","Z12.11","Z12.12","Z12.13","Z80.0","Z83.71","Z85.00","Z85.038","Z85.048","Z86.010","Z87.19","R19.5"])
)
and
(
{sample_data.COPAY_DUE} >0.00
and (not isNull({sample_data.COPAY_COLLECTED}) and {sample_data.COPAY_COLLECTED}>"0.00")

)
 then  "Y" Else "N"
sample-data.rpt
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Culwatrnca11Data AnalystAuthor Commented:
Raghavendra Hullur,

That is exactly what I was needing.  Works perfectly. Thank you..
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.