on 01-07-2008 10:25 PM
Post Author: rlivermore
CA Forum: Formula
(CR v10 Pro) How can I display only the lastest record if there are multiple entries for a specific job number (SO number). I have 2 groups {tblSO.SONumber} and {tblSONotes.LastModified}. I tried sorting LastModified by descending order and putting all of the fields into the tblSONotes.LastModified Group and hiding the Details section and Group Footers to no avail. Help?
Below are the fields in my report...
{tblSO.DateOpened} {tblSO.SONumber} {tblSO.Status} {tblSONotes.LastModified} {tblSONotes.Topic} {tblSONotes.Notes}
Sample Data
1/12/08 124113 Scheduled 1/1/08 Dispatch Technician scheduled on 1/15/08.
1/12/08 124113 Scheduled 1/15/08 Dispatch Job has been completed.
In this example I'd only want the last record (1/15/08) to display on the report, suppressing the one on 1/1/08.
Post Author: Jagan
CA Forum: Formula
Sorry
Unlike SQL, a group in Crystal implies ordering (that's my understanding anyway).Here's two different ways to get the most recent record per job:
1)Group on Job. Group on date. Use the group selection formula to get the most recent date per job, i.e.{table.date} = maximum({table.date}, {table.job})
If your date fields are unique (e.g. timestamp rather than just a date) then you'll only get one record per job so you can use the details section. If not you'll need to add another field / formula to sort on to determine which of the multiple records to show using the date group header / footer to display.
2)Group on Job. Order by date. Suppress the details, print in the job group header / footer depending on the date ordering. Same process for non-unique dates.
There are advantages / disadvantages to both, e.g. the latter means that all other records are available to running totals etc. which is good/bad depending on what you want the report to do.
Is that better???
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Post Author: rlivermore
CA Forum: Formula
Jagan,
I appreciate your help but I'm having trouble understanding what exactly your suggesting. Are you saying I should remove the LastModified group and put the fields back into the Details section and not do any type of sorting? 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: Jagan
CA Forum: Formula
In fact, I'd ditch the date group and use ordering. Date grouping has its quirks as to how they are grouped (weekly, daily, hourly etc.)
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: Formula
You're trying to both group and order on the date. Delete the ordering and use the group expert to change the group order.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.