Data Join - Only show latest record

[djieon]

Hi All,

Probably a really simple one for you.

I have a table on the left called RM_COMMON_ATTRIBUTES which has the one record and a table on the right called RM_NDT which can have many records. They are linked on a field called item_uid
In my many table I can have 2 or 3 records for each of the one records on the left, and each of the many records has a date. I only want to see the latest record based on date.

so in summary I want to see one record on each side, with the latest record on the right sorted by date.

Any ideas on how to do this?

Thanks in advance.

David.

 

[lbass]

Go to report->selection formula->GROUP and enter:

{RM_NDT.date} = maximum({RM_NDT.date},{RM_COMMON_ATTRIBUTES.ITEM_UID})

This will display only the most recent date, once you group on item_uid. However, the non-group selected records are still IN the report, just not displayed, so you would need to use running totals for any calculations across groups.

Another approach would be to use a SQL expression to return the most recent record. If you want to try that, you need to specify your CR version, your database, and also it would help to see your current SQL query (database->show SQL query).

-LB