Culwatrnca11
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","V 18.51","V7 0.0","V72. 31","V76.4 1","V76.50 ","V76.51" ,"V76.52", "792.1","Z 00.00","Z0 0.01","Z01 .411","Z01 .419","Z12 .10","Z12. 11","Z12.1 2","Z12.13 ","Z80.0", "Z83.71"," Z85.00","Z 85.038","Z 85.048","Z 86.010","Z 87.19","R1 9.5"]
or {Command.DX2} startswith ["V10.00","V10.05","V10.06 ","V12.72" ,"V12.79", "V16.0","V 18.51","V7 0.0","V72. 31","V76.4 1","V76.50 ","V76.51" ,"V76.52", "792.1","Z 00.00","Z0 0.01","Z01 .411","Z01 .419","Z12 .10","Z12. 11","Z12.1 2","Z12.13 ","Z80.0", "Z83.71"," Z85.00","Z 85.038","Z 85.048","Z 86.010","Z 87.19","R1 9.5"]
or {Command.DX3} startswith ["V10.00","V10.05","V10.06 ","V12.72" ,"V12.79", "V16.0","V 18.51","V7 0.0","V72. 31","V76.4 1","V76.50 ","V76.51" ,"V76.52", "792.1","Z 00.00","Z0 0.01","Z01 .411","Z01 .419","Z12 .10","Z12. 11","Z12.1 2","Z12.13 ","Z80.0", "Z83.71"," Z85.00","Z 85.038","Z 85.048","Z 86.010","Z 87.19","R1 9.5"]
or {Command.DX4} startswith ["V10.00","V10.05","V10.06 ","V12.72" ,"V12.79", "V16.0","V 18.51","V7 0.0","V72. 31","V76.4 1","V76.50 ","V76.51" ,"V76.52", "792.1","Z 00.00","Z0 0.01","Z01 .411","Z01 .419","Z12 .10","Z12. 11","Z12.1 2","Z12.13 ","Z80.0", "Z83.71"," Z85.00","Z 85.038","Z 85.048","Z 86.010","Z 87.19","R1 9.5"]
or {Command.DX5} startswith ["V10.00","V10.05","V10.06 ","V12.72" ,"V12.79", "V16.0","V 18.51","V7 0.0","V72. 31","V76.4 1","V76.50 ","V76.51" ,"V76.52", "792.1","Z 00.00","Z0 0.01","Z01 .411","Z01 .419","Z12 .10","Z12. 11","Z12.1 2","Z12.13 ","Z80.0", "Z83.71"," Z85.00","Z 85.038","Z 85.048","Z 86.010","Z 87.19","R1 9.5"]
or {Command.DX6} startswith ["V10.00","V10.05","V10.06 ","V12.72" ,"V12.79", "V16.0","V 18.51","V7 0.0","V72. 31","V76.4 1","V76.50 ","V76.51" ,"V76.52", "792.1","Z 00.00","Z0 0.01","Z01 .411","Z01 .419","Z12 .10","Z12. 11","Z12.1 2","Z12.13 ","Z80.0", "Z83.71"," Z85.00","Z 85.038","Z 85.048","Z 86.010","Z 87.19","R1 9.5"]
or {Command.Amount} >0
or {Command.Ins_Amount} >0
then "Y" Else "N"
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
or {Command.DX2} startswith ["V10.00","V10.05","V10.06
or {Command.DX3} startswith ["V10.00","V10.05","V10.06
or {Command.DX4} startswith ["V10.00","V10.05","V10.06
or {Command.DX5} startswith ["V10.00","V10.05","V10.06
or {Command.DX6} startswith ["V10.00","V10.05","V10.06
or {Command.Amount} >0
or {Command.Ins_Amount} >0
then "Y" Else "N"
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
Put the ) at the end
mlmcc
ASKER
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","V 18.51","V7 0.0","V72. 31","V76.4 1","V76.50 ","V76.51" ,"V76.52", "792.1","Z 00.00","Z0 0.01","Z01 .411","Z01 .419","Z12 .10","Z12. 11","Z12.1 2","Z12.13 ","Z80.0", "Z83.71"," Z85.00","Z 85.038","Z 85.048","Z 86.010","Z 87.19","R1 9.5"]
or {Command.DX2} startswith ["V10.00","V10.05","V10.06 ","V12.72" ,"V12.79", "V16.0","V 18.51","V7 0.0","V72. 31","V76.4 1","V76.50 ","V76.51" ,"V76.52", "792.1","Z 00.00","Z0 0.01","Z01 .411","Z01 .419","Z12 .10","Z12. 11","Z12.1 2","Z12.13 ","Z80.0", "Z83.71"," Z85.00","Z 85.038","Z 85.048","Z 86.010","Z 87.19","R1 9.5"]
or {Command.DX3} startswith ["V10.00","V10.05","V10.06 ","V12.72" ,"V12.79", "V16.0","V 18.51","V7 0.0","V72. 31","V76.4 1","V76.50 ","V76.51" ,"V76.52", "792.1","Z 00.00","Z0 0.01","Z01 .411","Z01 .419","Z12 .10","Z12. 11","Z12.1 2","Z12.13 ","Z80.0", "Z83.71"," Z85.00","Z 85.038","Z 85.048","Z 86.010","Z 87.19","R1 9.5"]
or {Command.DX4} startswith ["V10.00","V10.05","V10.06 ","V12.72" ,"V12.79", "V16.0","V 18.51","V7 0.0","V72. 31","V76.4 1","V76.50 ","V76.51" ,"V76.52", "792.1","Z 00.00","Z0 0.01","Z01 .411","Z01 .419","Z12 .10","Z12. 11","Z12.1 2","Z12.13 ","Z80.0", "Z83.71"," Z85.00","Z 85.038","Z 85.048","Z 86.010","Z 87.19","R1 9.5"]
or {Command.DX5} startswith ["V10.00","V10.05","V10.06 ","V12.72" ,"V12.79", "V16.0","V 18.51","V7 0.0","V72. 31","V76.4 1","V76.50 ","V76.51" ,"V76.52", "792.1","Z 00.00","Z0 0.01","Z01 .411","Z01 .419","Z12 .10","Z12. 11","Z12.1 2","Z12.13 ","Z80.0", "Z83.71"," Z85.00","Z 85.038","Z 85.048","Z 86.010","Z 87.19","R1 9.5"]
or {Command.DX6} startswith ["V10.00","V10.05","V10.06 ","V12.72" ,"V12.79", "V16.0","V 18.51","V7 0.0","V72. 31","V76.4 1","V76.50 ","V76.51" ,"V76.52", "792.1","Z 00.00","Z0 0.01","Z01 .411","Z01 .419","Z12 .10","Z12. 11","Z12.1 2","Z12.13 ","Z80.0", "Z83.71"," Z85.00","Z 85.038","Z 85.048","Z 86.010","Z 87.19","R1 9.5"]
or {Command.Amount} >0
or {Command.Ins_Amount} >0
then "Y" Else "N")
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
or {Command.DX2} startswith ["V10.00","V10.05","V10.06
or {Command.DX3} startswith ["V10.00","V10.05","V10.06
or {Command.DX4} startswith ["V10.00","V10.05","V10.06
or {Command.DX5} startswith ["V10.00","V10.05","V10.06
or {Command.DX6} startswith ["V10.00","V10.05","V10.06
or {Command.Amount} >0
or {Command.Ins_Amount} >0
then "Y" Else "N")
The ) goes before the THEN
mlmcc
mlmcc
ASKER
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","V 18.51","V7 0.0","V72. 31","V76.4 1","V76.50 ","V76.51" ,"V76.52", "792.1","Z 00.00","Z0 0.01","Z01 .411","Z01 .419","Z12 .10","Z12. 11","Z12.1 2","Z12.13 ","Z80.0", "Z83.71"," Z85.00","Z 85.038","Z 85.048","Z 86.010","Z 87.19","R1 9.5"]
or {Command.DX2} startswith ["V10.00","V10.05","V10.06 ","V12.72" ,"V12.79", "V16.0","V 18.51","V7 0.0","V72. 31","V76.4 1","V76.50 ","V76.51" ,"V76.52", "792.1","Z 00.00","Z0 0.01","Z01 .411","Z01 .419","Z12 .10","Z12. 11","Z12.1 2","Z12.13 ","Z80.0", "Z83.71"," Z85.00","Z 85.038","Z 85.048","Z 86.010","Z 87.19","R1 9.5"]
or {Command.DX3} startswith ["V10.00","V10.05","V10.06 ","V12.72" ,"V12.79", "V16.0","V 18.51","V7 0.0","V72. 31","V76.4 1","V76.50 ","V76.51" ,"V76.52", "792.1","Z 00.00","Z0 0.01","Z01 .411","Z01 .419","Z12 .10","Z12. 11","Z12.1 2","Z12.13 ","Z80.0", "Z83.71"," Z85.00","Z 85.038","Z 85.048","Z 86.010","Z 87.19","R1 9.5"]
or {Command.DX4} startswith ["V10.00","V10.05","V10.06 ","V12.72" ,"V12.79", "V16.0","V 18.51","V7 0.0","V72. 31","V76.4 1","V76.50 ","V76.51" ,"V76.52", "792.1","Z 00.00","Z0 0.01","Z01 .411","Z01 .419","Z12 .10","Z12. 11","Z12.1 2","Z12.13 ","Z80.0", "Z83.71"," Z85.00","Z 85.038","Z 85.048","Z 86.010","Z 87.19","R1 9.5"]
or {Command.DX5} startswith ["V10.00","V10.05","V10.06 ","V12.72" ,"V12.79", "V16.0","V 18.51","V7 0.0","V72. 31","V76.4 1","V76.50 ","V76.51" ,"V76.52", "792.1","Z 00.00","Z0 0.01","Z01 .411","Z01 .419","Z12 .10","Z12. 11","Z12.1 2","Z12.13 ","Z80.0", "Z83.71"," Z85.00","Z 85.038","Z 85.048","Z 86.010","Z 87.19","R1 9.5"]
or {Command.DX6} startswith ["V10.00","V10.05","V10.06 ","V12.72" ,"V12.79", "V16.0","V 18.51","V7 0.0","V72. 31","V76.4 1","V76.50 ","V76.51" ,"V76.52", "792.1","Z 00.00","Z0 0.01","Z01 .411","Z01 .419","Z12 .10","Z12. 11","Z12.1 2","Z12.13 ","Z80.0", "Z83.71"," Z85.00","Z 85.038","Z 85.048","Z 86.010","Z 87.19","R1 9.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
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
or {Command.DX2} startswith ["V10.00","V10.05","V10.06
or {Command.DX3} startswith ["V10.00","V10.05","V10.06
or {Command.DX4} startswith ["V10.00","V10.05","V10.06
or {Command.DX5} startswith ["V10.00","V10.05","V10.06
or {Command.DX6} startswith ["V10.00","V10.05","V10.06
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
mlmcc
ASKER
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","V 18.51","V7 0.0","V72. 31","V76.4 1","V76.50 ","V76.51" ,"V76.52", "792.1","Z 00.00","Z0 0.01","Z01 .411","Z01 .419","Z12 .10","Z12. 11","Z12.1 2","Z12.13 ","Z80.0", "Z83.71"," Z85.00","Z 85.038","Z 85.048","Z 86.010","Z 87.19","R1 9.5"]
and {Command.Amount} >0.00
and {Command.Ins_Amount} >0.00)
then "Y" Else "N"
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
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
Which is it?
mlmcc
ASKER
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 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
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"
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
ASKER
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..
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
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
ASKER
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
James
Since AND evaluates first you probably need to have ( ) around the OR clauses
AND ( ..... ) Then
mlmcc