I've been stuck on this problem for about 8 hours now and I am at a loss on how to get what I imagine is a simple task working. Here is what I am trying to achieve.
The database tracks movie data. Users assign a director using a pop-up menu. I have a table of people and roles (a person can have many roles). I have a working conditional value list that returns a list of all people with the role of director and the menu displays correctly. However, if the director is missing, the user can navigate to a new layout to create a new person or assign the role of director to an existing person. When they nagivate to the previous layout to assign the director again, the menu doesn't display the newly assigned director.
I've paired down the situation into the attached test database.
In my example, the movie is Gran Torino. The director is incorrectly assigned to John Ford. The menu correctly shows only John Ford and Sergio Leone, despite the people in the database include Clint Eastwood and John Wayne, so the conditional value list appears to be working. Nagivate to the PersonRoles layout and assign Clint Eastwood the role of Director. Navigate back to Main and try to assign Clint Eastwood as the director. He is not in the list. Click each Refresh button (every combination) and the list will not refresh. You can close the database and open it and Clint Eastwood will be in the list. Additionally, any changes made to the relationships graph which requires a save will actually cause the list to refresh.
Here are all the things I tried:
Refresh Window (all combinations of flush cached results and external data) - just to test as I have read about the negative impact of this method.
Cartesian joins between all the tables I could think of.
Regular joins between the appropriate tables and fields.
I'm a seasoned software engineer and I've only been working in FMP for about a month now. I must be doing something wrong, but I can not see what it is.
Any help is greatly appreciated!