cancel
Showing results for 
Search instead for 
Did you mean: 

Subset only selection

Former Member
0 Kudos

Post Author: rookie10

CA Forum: Data Connectivity and SQL

I have three linked tables and am trying to select record ID's which were modified by somone with role of only F. I keep getting records also modified by Joe as F is included in his set of roles. I tried selecting on all but F but that doesn't seem to work either. Any help would be appreciated. I am using Crystal Reports 10.

Rookie

Table 1

Person

Role

Cathy

A

Cathy

B

Cathy

C

Cathy

D

Joe

A

Joe

B

Joe

F

Dave

F

Table 2

Person

User ID

Cathy

100

Joe

200

Dave

300

Table 3

Record ID

Last modified by

1001

100

1002

200

1003

200

1004

300

1005

300

Accepted Solutions (0)

Answers (5)

Answers (5)

Former Member
0 Kudos

Post Author: rookie10

CA Forum: Data Connectivity and SQL

Thanks. That's a great trick I can use for other things too. It worked!

Former Member
0 Kudos

Post Author: Jagan

CA Forum: Data Connectivity and SQL

It's not that hard really - do the suppression in Crystal. Create a formula that sorts the roles so that 'F' comes last. e.g. if {table.role} = 'F' then 2 else 1

Group on person and sort on this formula.

Check the role in the person group header. If this record has a role of 'F' then you know that this person ONLY has the 'F' role and no others. e.g. print in the group header and conditionally suppress if not 'F', or conditionally suppress the details for the group if the first record in the group isn't 'F' etc. etc.

See, simple!

Former Member
0 Kudos

Post Author: rookie10

CA Forum: Data Connectivity and SQL

I guess this one is as hard as I thought it was. Any suggestions?

Former Member
0 Kudos

Post Author: rookie10

CA Forum: Data Connectivity and SQL

This still pulls in records for Joe, because he has roles of F and also A and B. I want to exclude those with additional roles and limit to F and only F role, i.e.only Dave.

More help, please? Thanks

Former Member
0 Kudos

Post Author: Charliy

CA Forum: Data Connectivity and SQL

Geoup on Record ID

Out everything in the Group Footer, suppress Group Header and Detail

Put a Variable in the group header NumberVar OnlyF := 0

Put a formula in detail: NumberVar OnlyF; If {table1.role} <> 'F' then OnlyF := 1 else OnlyF := OnlyF

Suppress the Group Footer if Onlyf = 1