Solved

Crystal reports - Formula Field code need assistance with code

Posted on 2016-10-20
17
36 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now