I need to suppress a section dependent upon activity criteria 1

[BreffniK]

Thanks for taking the time to read my question.

I'd like to be able to suppress a section on my report if a certain 'approval' activity has not been performed against the record. Please note, there will be many activities performed against any given record.

At first I tried to suppress the section if...

activity_type.name <> 'approval'

...but that is not working. The section is always being suppressed on my report regardless of the existence or non-existence of the 'approval' activity.

I am able to select the records using SQL as shown below, but can anyone help me translate this for Crystal 9?

SELECT
RECORD.id
FROM
RECORD, Project
WHERE
Project.id = RECORD.project_id AND RECORD.id
NOT IN (
SELECT
record_id
FROM
record_activity
WHERE
record_activity_type IN (139)
)

 

[BreffniK]

I should clarify that record_activity_type IN (139) is the id for the activity that I would like to base my conditional suppress upon.

 

[lbass]

Do you have the option of using "Add Command" as a datasource? Then you could write the SQL query directly there.

Another way to do this is to group on {Record.ID}, and then create a formula {@139}:

if {record_activity.record_activity_type} = 139 then 1 else 0

Then go to report->edit select formula->GROUP and enter:

sum({@139},{record.ID}) = 0

-LB

 

[BreffniK]

The formula works perfectly, thank you very much lbass!!!