Displaying most recent related record
I have two tables, one called Employee and another called Project. Each employee may have multiple projects. Every project has a date. I have a layout based on the Employee table, and I want to display information about the most recent project. I have an idea of how I might do this, but I feel like there is probably a better way. Right now, I am creating a portal which is sorted by date, and I am only showing one record. This works, but if i decide to pull in more fields from the related project, and if I want to show them on different areas of my layout, then I will have to create a new portal for each field, which seems messy.
My other thought was to create a new occurrence of Project, and define a new relationship between Employee and the new Table Occurrence, but I am not sure how I would define that relationship.
Does anybody have advice about the best way to do this?