Link to home
Start Free TrialLog in
Avatar of Culwatrnca11
Culwatrnca11Flag for United States of America

asked on

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"
Avatar of Mike McCracken
Mike McCracken

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

             AND ( ..... ) Then

mlmcc
Avatar of Culwatrnca11

ASKER

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.
Put the ( after AND
Put the ) at the end

mlmcc
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")
The ) goes before the THEN

mlmcc
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
Are you sure you have ones that should be an N?

mlmcc
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"
In your last comment you changed the Amount comparisons to 0 to join with an AND not an OR.

Which is it?

mlmcc
The actual field is 0.00 and with just 0 it was placing a yes. So I added >0.00 for the copay amount
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
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..
ASKER CERTIFIED SOLUTION
Avatar of James0628
James0628

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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
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..
You're welcome.  Glad I could help.

 James