Subtract records that have a specified value in one of two fields. 1

[INTP]

Sorry if this is too basic a question for this forum. My CR education is limited to just one CR level 1 class (2 days), and that was many years ago (I think CR 9 was just coming out). I don't use it often but have been charged with creating a report for work and am stumped. It all sounds like it should be rather easy.

I have two fields in my DB that contain numerical codes that represent the types of services that we provided to our customers. I think the field is actually a string field, but the front end users enter a number to represent the service (1-9). I'll just call the fields service1 & service2. Most of the service2 fields contain no data.

What I need to do is to count all the service that were paid in a week (which is just a matter of counting the records). But I don't want to include the records that have two service codes (indicated by a value in the service2 field)in which one of the two fields contains the value 8 (representing a free service that was not charged to the customer). I'll try to illustrate it below:

record service1 service2
1 8 5
2 4 1
3 7
4 3 8
5 8


So what I want to do is to count the records (there are 5) check to see how many have 2 service codes (there are 3) and of these, how many contain the service code of 8 in either field(just 2). Then I need to count all of the records subtracting any records that have two service codes in which one contains a value of 8 (my result would be 3 because 2 have two services of which one contains the value of 8).

Anyway, I hope this makes sense. I have tried a number of things but cannot figure out how to do it.

 

[Turkbear]

Hi,
Create a formula in the detail section that tests for the 'do not count' condition:
@DoNotCount

If 
(
Not IsNull({service2})
and
{service2} = "8" 
)
OR
(
Not IsNull({service1})
and 
 {service} = "8"
)

then
0
else
1

Format that formula field so that is does not display ( supress) and insert a SUM of it to count the 'eligible' records..









To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[INTP]

Okay, this is good. It worked well except for one thing... which is my fault because I didn't mention it.

The 8 code is further defined based on whether the service was given free as a donation, a customer service compensation or a promotional. These are further defined as 8, 8d, 8p & 8c

so to not count these I'm assuming I would change the

{service2} = "8"

to something like

{service2} is like "8?"

Or is there a better way of doing this? Because when I try to save the formula, it highlights the "is" in "is like" and says that "the ) is missing"

Also, Just so I understand what you did in your suggestion...

I tried a similar formula but instead of placing the "Not IsNull" prior to the field, I tried to use IsNotNull after selecting the field. Why is the structure different here that is requires a "Not IsNull" prior to the field?

 

[INTP]

Well I should have just kept plugging away at it before asking that last question apparently, the corrected verbiage is not

"is like"

but rather, it's just

"like".

I also replaced the "?" with an "*" and it worked fantastically. Thanks for your help!

 

[Turkbear]

Hi,
Try thi sinstead, it may be fractionally faster:

If 
(
Not IsNull({service2})
and
{service2} startswith "8" 
)
OR
(
Not IsNull({service1})
and 
 {service1} startswith "8"
)

then
0
else
1

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[INTP]

Thanks. That worked as well and may be faster (fractionally). Is there a CR rule for putting the

"Not IsNull" before the selected field? Is that always the case or is it only when there is a "not"?

 

[Turkbear]

Hi,
It is always best to test for NULLs first if there is any possibility that some exist..CR stops evaluating the formula if is finds an unhandled NULL...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[INTP]

Aha! That makes sense to me. Thank you!