on 04-23-2007 4:55 PM
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
Post Author: rookie10
CA Forum: Data Connectivity and SQL
Thanks. That's a great trick I can use for other things too. It worked!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Post Author: rookie10
CA Forum: Data Connectivity and SQL
I guess this one is as hard as I thought it was. Any suggestions?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.