I have a problem with a sorted relationship that is not working as expected. I've tested it various ways and nothing seems to fix the problem. I can even get evidence that the relationship sort works if I open a new window for the relationship (the records appear in the order expected).
Here is what I have done thus far:
1. I have a Date table that is related to all the records from Habits. I can then create a DateHabit record based on a specific Habit.
2. When a Habit is added to a Date, I use a filter to exclude those that have been added.
3. I have added a new relationship between AllHabits and the DateHabits (LatestDateHabit4AllHabits) that are related to them. This relationship is sorted in descending DateID order.
4. I have added a relationship between LatesetDateHabit4AllHabits and Date so that I can display the date. The relationship is based on Serial Numbers. This relationship works so that I can show the date in a ToolTip, but the wrong date appears. The first date in the relationship appears. I want the latest date.
The relationship between AllHabits (Habit table) and LatestDateHabit4AllHabits (DateHabit) is sorted by descending DateID (the Serial Number in Date). If I open a new layout and show the records, they appear in descending DateID order.
When I show a ToolTip however, the date that appears is the first one in the relationship. All the ID's are numbers. I've changed the sort in the relationship and tested it with the new layout and it shows the records in the expected order. But the ToolTip doesn't work. The calculation for the tip looks like this:
AllHabitsFrequency::Description & ¶ & "Most recently occurred on " & PreviousAllHabitsDate::Date
I would swear that I have gotten this to work in other places in my application, which is what is so curious about this instance. I've been a FM developer since version 3 and am currently using 11 (not Advanced). I've done all the reasonable things I can think of. Is there something obvious I might be missing?