display a comment field (filtering on the date field)

[jewelsan]

Note Table
__________
NoteID int 4
NoteDate datetime 8
CreatorContactID int 4
IsWorkOrderNote tinyint 1
NoteText varchar 1000
CurrentStatus varchar 20

I am creating a report that lists workorders based on buildings. The report is looking great. However all the notes for each workorder dispay. One workorder can have multiple notes (NoteText). I would like the report to list the last note only (the most recent note). The Note table is shown above. Is there a way to have Crystal reports sort the notes by date and list the recent note on the report (not the date)?

Thanks for the help.

 

[GaryRogers]

Yes there are a couple of ways to do this.

The most efficient way is to modify the SQL Query to get the most recent data by adding a Sub Query something like this "Where NoteDate = (Select Max(NoteDate) From NoteTable).
This should retrieve only the latest records.

You can also accomplish this in Crystal. One way would be to either Sort or Group NoteDate by ascending and then Conditionally Suppress the detail section with something like this “{NoteDate } < Previous ({NoteDate })”
Gary Rogers CrystalDesk (Report Scheduler)
Crystal Reports training, consulting, and software interface development.

http://www.crystaldesk.com/

 

[kenhamady]

1) group by WO
2) sort by date within the group (ascending).
3) put the comment in the Group Footer

You should only see the last one. You can hide the details if you don't want to see them. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/