cancel
Showing results for 
Search instead for 
Did you mean: 

Display only latest record

Former Member
0 Kudos

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.

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

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???

Former Member
0 Kudos

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.

Former Member
0 Kudos

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.)

Former Member
0 Kudos

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.