Using OR in record selection

[c8ltgkue]

Hello all

Using CR XI

I am trying to use the following in my record selection but it doesn't seem to work. The link to the tblCourtReport is a full outer join.

((isnull({tblCourtReport.CourtReportTypeRef})) or ({tblCourtReport.CourtReportTypeRef} <> 1))

My dataset doesn't seem to reflect the above filter. Any ideas why ? I am going cross eyed...!!!

Many thanks
C8

 

[lbass]

What results are you getting? What results do you expect? Are you only using two tables?

-LB

 

[c8ltgkue]

I am using tblCase and tblCourtReport.

I am trying to find all cases where there was either
1) No report or
2) If there was a report then the report type was other than 1.

Case to CourtReport has one to many relation.

I get the result of (1) by the full outer join but don't know how to achive (2)

 

[lbass]

I don't know what fields are in which table, but if you are only testing for the presence of a report in CourtReport, then you should be using a left join FROM Case TO CourtReport, and then your current record selection formula should work appropriately.

-LB

 

[c8ltgkue]

May be I am not explaining myself very well. Let me try again.

As I mentioned, Case to CourtReport has one to many relationship. CaseId is the unique identifier field in both tables(tblCase and tblCourtReport) and CourtReport and CourtReportTypeRef are the fields in CourtReport table. (tblCourtReport).

Here is the example of my data

Scenario 1

CaseID CourtReport CourtReportTypeRef
#1111 S7 1
#1111 Addenda 2
#1111 Intrim 3


Scenario 2

Case ID CourtREport CourtReportTypeRef
#2222 Intrim 3


Scenario 3

Case ID CourtREport CourtReportTypeRef
#3333 NULL NULL

I am trying to create a record selection formula with will only show the cases where there is no S7 report i.e. Scenario 2 and 3. Cases where there is no report or Cases with ReportType other than 1.

The reason why I have full outer join from tblCourt to tblCourtReportType is because I want to include cases where there is no Court report. i.e. Scenario 3

I hope it's a better explanation than my previous attempt. The other way to do it, is of course using the formula to identify the case where there is a S7 report and suppress it, but I was hoping to create a report with minimum complexity.

 

[mrdjsm1th]

I think the suggestion posted by lbass is correct. You should look at Database -> Show SQL Query to confirm that your SQL looks something like:

Select
tblCase.CaseID
, tblCourtReportType.CourtReport
, tblCourtReportType.CourtReportTypeRef
From tblCase
Left Outer Join tblCourtReportType on tblCase.CaseID = tblCourtReportType.CaseID

Where

(tblCourtReportType.CourtReport is null or tblCourtReport.CourtReportTypeRef <> 1)

 

[lbass]

There are two steps involved here. First, you need to follow my first suggestion to use a left join. A full outer join is not called for, since the caseID is never null in the cases table. However, you need to omit the record selection formula I first recommended, and instead do the following:

First insert a group on {case.caseID}. Then create a formula:

//{@type1}:
if {tblCourtReportType.CourtReportTypeRef} = 1 then 1

Then go to report->selection formula->GROUP and enter:

isnull({tblCourtReportType.CourtReport }) or
sum({@type1},{case.caseID}) = 0

-LB