Ommitting Record detail- Please Help

[jobillborf]

Crystal Report Version 9
MICROSOFT SQL SERVER DATABASE.
For this problem we have two tables.

Table 1 - Complaint table link - logkey
table 2 - Log table. link - logkey field question of LogType


the tables are linked by logkey. The field in the log table my question is about is logtype.

I would like a listing of all complaints with the linked log types. But would like to exclude the complaints that has a logtype of SDOT. There can be multiple log entries for each complaint.

In other words if the log table has a logtype of SDOT associted with a complaint. The complaint must be ommited from the report.

Thank YOu

 

[lbass]

If you mean that you want to omit the entire complaint if there is a logtype = "SDOT", then first insert a group on {complaint.logkey} and then create a formula:

//{hasSDOT}:
if {log.logtype} = "SDOT" then 1

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

sum({@hasSDOT},{complaint.logkey}) = 0

-LB

 

[jobillborf]

Thank you. I forgot to include one thing. Some complaints do not have a link to the logtable link logkey.
We would like to include all records that do not have records in the log table.

In our database if there is no link to the log table the logkey in the complaint table will have a 1 in the field.

In other words. Include the following

complaint table
logkey = 1



thanks again for your help

Sincerely

Bill

 

[lbass]

Use a left join from the complaint table to the log table, with no selection criteria on the log table. Then change the group selection formula to:

isnull({log.logkey}) or
sum({@hasSDOT},{complaint.logkey}) = 0

-LB