Selecting One record from a one-to-many relationship 1

[Suzette]

I need to connect two tables and return a single value for each project. However, the tables have a one-to-many relationship.

The first table has a single record for each project.

The second table is a status history table and contains multiple records for any single project. This table stores project_id, status_change_date and status_code.

How can I select only the most recent status_change_date for each projects and then return only the associate status code for that project? My final report needs to be a listing of all projects and their most recent status code.

 

[jolamb42]

Not 100% sure this will work, but worth a try!

In CR, you could insert a group for status_change_date - which would sort the records in ascending date order. Then, format the details section to Suppress if:

Not OnLastRecord

Then only one line will print for that project - the most recent date and status code.

Hope that helps!

 

[Suzette]

Thanks for responding so quickly. I tried using the solution you proposed and ended up with only the last record in the report. In doing so, however, I checked the Crystal Help for OnLastRecord and found the section on PrintState Functions. Is there a way to use Previous or PreviousFld to screen out duplicates?

 

[jolamb42]

Try grouping by project_id first, then group by date. Then, using the suppress I mentioned earlier on the details section - It will suppress all but one line per project # (the most recent date). I think that's what you need.

 

[DjangMan]

Try suppressing the detail section using:

{table.field}<>maximum({table.field})

DjangMan

 

[Suzette]

Hi - I must be doing something wrong - I tried both solutions (grouping by project id, then date & suppressing) and suppressing using the formula. I am still getting only the single record for the project with the most current change date. I'm selecting the Suppress (No Drill Down) from the Section Expert panel. Any additional suggestions are welcome!!!

 

[jolamb42]

Aha!

Try suppressing the details section if:

{fieldname} = Next ({fieldname})

(Keep the groupings by project id and date.)

 

[Suzette]

Yee ha! Worked like a charm. Thank you.

 

[1963520]

Try this: Group by ID, then put date filed in the detail, use summary(Max) it to the Group footer.

lucy