Help with related fields/sorting
I'm having sort of a complex issue. I think I know how it should work but am not having any luck. I have two tables related through a key field. This relationship is working fine in many of the areas I'm using it. Here's what I'm trying to accomplish:
I have a "report" layout that is displaying in List View. It consists of maintenance records for specific building/room locations. I have a field called Maintenance Log ID in one table that is a running unique serial number for each maintenance log created. The other table, for room locations, is the table associated with this report list. The problem occurs once I have multiple maintenance log records for a single room location. The room location info populates in the list just fine, but I need the MOST RECENT maintenance log ID to be associated with each room record. Right now I'm getting the FIRST maintenance log ID created for each room in my list. Because these logs are time-specific and the purpose for logging them is to know how much time until the next maintenance needs to be done, I need to have the most recent record showing.
I've tried setting up the maintenance log ID field in a portal and setting the sort order both ascending and descending, but with no luck. I thought that through the two tables' relationship, using a portal, I would essentially have access to all the maintenance logs for each room location. That doesn't appear to be the case as I still wind up with the first record created in my portal.
I've tried to come up with other ways to achieve this but haven't had any luck. Is there something I'm missing on how to use a portal in this situation, or how to set the default "sort" to make the latest record auto-populate instead of the first record? If you need more info I'd be happy to dialogue. I know this is complex and is rather difficult to describe here in this post. Thanks for any help!
In the image I've uploaded, the field on the left is the field I'm trying to work with. You will see that it is displaying record '0023' which is the first maintenance log created for AG 00202. What it should be displaying is '0025' which is the most recent maintenance log created for AG 00202. Hopefully that provides a little clarity to what I'm trying to achieve.