Solved

Crystal reports - Formula Field code need assistance with code

Posted on 2016-10-20
17
81 Views
Last Modified: 2016-10-25
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"
0
Comment
Question by:Culwatrnca11
  • 8
  • 5
  • 4
17 Comments
 
LVL 100

Expert Comment

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

             AND ( ..... ) Then

mlmcc
0
 

Author Comment

by:Culwatrnca11
ID: 41852924
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 41852968
Put the ( after AND
Put the ) at the end

mlmcc
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:Culwatrnca11
ID: 41853026
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 41854166
The ) goes before the THEN

mlmcc
0
 

Author Comment

by:Culwatrnca11
ID: 41854172
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 41854186
Are you sure you have ones that should be an N?

mlmcc
0
 

Author Comment

by:Culwatrnca11
ID: 41854313
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 41854399
In your last comment you changed the Amount comparisons to 0 to join with an AND not an OR.

Which is it?

mlmcc
0
 

Author Comment

by:Culwatrnca11
ID: 41854473
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
 
LVL 34

Expert Comment

by:James0628
ID: 41854485
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
 

Author Comment

by:Culwatrnca11
ID: 41854490
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
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 41854508
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
 

Author Comment

by:Culwatrnca11
ID: 41854534
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
 
LVL 34

Expert Comment

by:James0628
ID: 41854549
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
 

Author Closing Comment

by:Culwatrnca11
ID: 41858976
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
 
LVL 34

Expert Comment

by:James0628
ID: 41859460
You're welcome.  Glad I could help.

 James
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question