Crystal reports - Formula Field code need assistance with code

What I want to do is create a formula that comments Y or N if the fields below contain any of the following. So if any of the listed CPT codes exist then look to see if in any of the following codes exist in DX1 - DX6 fields, if a match is found then look to see if Amount and Ins_Amounts are > 0. If so put a Y, if not then N.  but basically along with the CPT_CODE anyone of these other combinations could exist and if so to flag the amount would need to be > 0 at this time.



if {Command.CPT_CODE} startswith ["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} startswith ["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} startswith ["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} startswith ["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} startswith ["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} startswith ["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} startswith ["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.Amount} >0
or {Command.Ins_Amount} >0

 then  "Y" Else "N"
Culwatrnca11Data AnalystAsked:
Who is Participating?
 
James0628Commented:
Do both amount fields have to be > 0, or just one of them?

 Assuming that both need to be > 0, try this:

if {Command.CPT_CODE} startswith ["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} startswith ["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} startswith ["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} startswith ["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} startswith ["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} startswith ["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} startswith ["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.Amount} >0
and {Command.Ins_Amount} >0
)
 then  "Y" Else "N"

Open in new window


 I just added ()'s in the places in my previous post.  Technically, the ()'s aren't needed around the Amount and Ins_Amount tests in this case, since they're joined with an And, but they won't hurt, and may make the tests clearer.

 If you want "Y" if either Amount or Ins_Amount is > 0, change the And between those 2 tests back to an Or (and in that case you do need the ()'s around those tests).

 James
0
 
mlmccCommented:
What problem are you having?
Since AND evaluates first you probably need to have ( ) around the OR clauses

             AND ( ..... ) Then

mlmcc
0
 
Culwatrnca11Data AnalystAuthor Commented:
when it runs. I get the "Y" but no "N" . Is the use of "and" and "or" correct? I know there should probably be some () but not sure where should put them.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
mlmccCommented:
Put the ( after AND
Put the ) at the end

mlmcc
0
 
Culwatrnca11Data AnalystAuthor Commented:
So here is the updated code.. when I put in the () the way you described it tells me "The ) is missing".

if {Command.CPT_CODE} startswith ["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} startswith ["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} startswith ["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} startswith ["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} startswith ["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} startswith ["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} startswith ["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.Amount} >0
or {Command.Ins_Amount} >0

 then  "Y" Else "N")
0
 
mlmccCommented:
The ) goes before the THEN

mlmcc
0
 
Culwatrnca11Data AnalystAuthor Commented:
Here is the update.

if {Command.CPT_CODE} startswith ["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} startswith ["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} startswith ["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} startswith ["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} startswith ["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} startswith ["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} startswith ["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.Amount} >0
or {Command.Ins_Amount} >0)

then  "Y" Else "N"

The "Y" shows but there are no "N" showing up. Their blank
0
 
mlmccCommented:
Are you sure you have ones that should be an N?

mlmcc
0
 
Culwatrnca11Data AnalystAuthor Commented:
If I break it down and only focus on the 1st DX1 line then I get both Y and N..  but if I add all the other DX2 - 6 it does not work for me. I then get only Y and no N but the Y do not seem accurate.

if {Command.CPT_CODE} startswith ["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} startswith ["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.Amount} >0.00
and {Command.Ins_Amount} >0.00)

then  "Y" Else "N"
0
 
mlmccCommented:
In your last comment you changed the Amount comparisons to 0 to join with an AND not an OR.

Which is it?

mlmcc
0
 
Culwatrnca11Data AnalystAuthor Commented:
The actual field is 0.00 and with just 0 it was placing a yes. So I added >0.00 for the copay amount
0
 
James0628Commented:
Let's break this down.

 You're checking CPT_CODE, DX1 through DX6, Amount and Ins_Amount.

 How are these tests connected?  Are any of them independent?  For example, do you want "Y" if Amount or Ins_Amount is > 0, regardless of CPT_CODE and DXn?  I suspect that you want some combination of the conditions.  Maybe if CPT_CODE has one of those values, and any one of the DX1 through DX6 fields has one of those values, and Amount or Ins_Amount is > 0?  In abbreviated form (for clarity), that would be

if {Command.CPT_CODE} ... and
 (
 {Command.DX1} ... or
 {Command.DX2} ... or
 {Command.DX3} ... or
 {Command.DX4} ... or
 {Command.DX5} ... or
 {Command.DX6}
 ) and
 (
 {Command.Amount} > 0 or
 {Command.Ins_Amount} > 0
 )
 then  "Y"
Else "N"

Open in new window



 If you want Amount and Ins_Amount to both be > 0, then change the Or between those tests to And (as in the last formula that you posted).

 James
0
 
Culwatrnca11Data AnalystAuthor Commented:
Hi James,

The CPT_CODe exists then it checks for any of those codes in the DX line, if those are detected then it looks for the amount fields to be greater then 0.00. If all of these paremeters are detected then I need a Y.. if the first 2 are detected but the amount is 0.00 then it should be N.  Does that help..
0
 
Culwatrnca11Data AnalystAuthor Commented:
James,

think were almost there.   I am validating the output now will post something shortly..  one question. I have blanks between the Y and Ns. Wondering why the blanks are not pupulated with a N. Validating post results shortly.
0
 
James0628Commented:
For the "blanks", one of more of the fields is probably null.  Normally, if CR encounters a null field in a formula, it stops evaluating the formula at that point and you get a null result, which would appear blank on the report.

 If you're using any of the more recent versions of CR, there should be an option in the formula editor (a dropdown list at the top) to tell CR how to handle nulls in that formula.  Use the "default value" option.

 If your version of CR doesn't have that option, there are a couple of ways to handle nulls in the report.  But I'm guessing that your version of CR has the option that I mentioned above, so I won't get into the other options unless you need/want me to.

 James
0
 
Culwatrnca11Data AnalystAuthor Commented:
James, Thanks forked perfectly. Validated and yes, I am getting blanks because nothing is picked up in the DX lines but when there is and the amout is >0 I get Y if not >0 I get N which is perfect... Thank you..
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.