How did you create this layout? Did you place the date field from the secondary table directly on this Master List based layout or did you use a portal?
Since you describe this as a one to many relationship, I would expect a portal to be the means to make this work so that you can see all the related records--not just data from the first related record--which is what you'll get if you just put the fields from the secondary table directly on a layout based on the Master List table.
How did you create this layout?
I placed the field from the secondary table directly on the layout. I don't not want to use a portal as I wish to display just the latest date from the secondary table.
My understanding is that the relationship should pick up the first match and as long as a sort the table in date order, I should get the result that I want. However, the field in the layout is blank which is what I don't understand.
If you sort the records in descending order by date and this field is indeed of type date, then that should be the case. You'll have to do some investigating to figure out why this is not the case.
I have tried to resolve my problem but am struggling. I can see that the relationship is indentifying the correct stock based upon the SEDOL relationship. However, it is using a record that is not the latest and the particular record that it is finding does not have any information. However, if I sort the table in descending date order, it still does not change the record that it is finding based upon the relationship. I have tried to refresh the window to no avail.
What else can I check?
Make sure you are specifying the sort order in Manage | Database | Relationships and for the correct table occurrence.
You'd find the table occurrence from which you are specifying the field from the related field, then find the relationship line that links it to your layout's table occurrence (shown in "Show Records From" in Layout Setup...). Double click it and specify a sort order for the table from which your related field is selected.
(Table Occurrences are what we call the "boxes" found in Manage | Database | Relationships)
That solved it. Thank you. I did not realise that I had to specify the sort order in the relationship criteria.