Bad_Fish
asked on
Crystal Reports Record/Group Selection
I'm in need of some help with Crystal Reports (Ver. 2008). I have two tables...Company, and Notes. Company has a one to many relationship with Notes and each note has a "created date." An example view would look like this...
ABC, Inc.
Note 1, 11/14/2010
Note 2, 11/12/2010
Note 3, 10/30/2010
XYZ, Inc.
Note 1, 11/02/2010
Note 2, 10/01/2010
Note 3, 09/15/2010
I want to print only those Companies that have not had a Note added in the last ten days. Using the example above, ABC, Inc should not appear in the report...but XYZ, Inc should. Here are the steps I've taken so far...
1. Joined the tables
2. Created a report group that groups by Company.Comp_Name.
3. Created an "AsOfDate" Parameter field (that defaults to "today").
4. Created a "CompareDates" Formula Field that says...
whilereadingrecords
if {Notes.Note_CreatedDate} >= ({?AsOfDate}-10) then
formula=False
else
formula=True
end if
5. Created a Group Selection formula that says "{@CompareDates} = True."
In a sense, this is working...but not the way I need it to work. Again using the above example, instead of omitting ABC, Inc from the report because one (or more) "Note" records do not meet the criteria, it includes it because "Note 3" does meet the selection criteria.
How can I get this report to omit "Company" records that have any "Note" records that do not meet the criteria?
Thanks for your help.
ABC, Inc.
Note 1, 11/14/2010
Note 2, 11/12/2010
Note 3, 10/30/2010
XYZ, Inc.
Note 1, 11/02/2010
Note 2, 10/01/2010
Note 3, 09/15/2010
I want to print only those Companies that have not had a Note added in the last ten days. Using the example above, ABC, Inc should not appear in the report...but XYZ, Inc should. Here are the steps I've taken so far...
1. Joined the tables
2. Created a report group that groups by Company.Comp_Name.
3. Created an "AsOfDate" Parameter field (that defaults to "today").
4. Created a "CompareDates" Formula Field that says...
whilereadingrecords
if {Notes.Note_CreatedDate} >= ({?AsOfDate}-10) then
formula=False
else
formula=True
end if
5. Created a Group Selection formula that says "{@CompareDates} = True."
In a sense, this is working...but not the way I need it to work. Again using the above example, instead of omitting ABC, Inc from the report because one (or more) "Note" records do not meet the criteria, it includes it because "Note 3" does meet the selection criteria.
How can I get this report to omit "Company" records that have any "Note" records that do not meet the criteria?
Thanks for your help.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're welcome. Glad I could help.
James
James
ASKER
James0628, thank you. Your statement did the trick nicely...and I learned a little too.
Thanks again to both of you.