Find using field of related record
I am new to FileMaker, using version 9.
The short form of my question: Performing a Find on a field that comes from a Related table is giving me weird results - it seems to Find all records in the Related table and then match them back to the current table without respecting the relationship.
In more detail: I have an orchestra database with related tables, Players and Current Players. The idea is that Players stores info for players of all years (current and previous), and Current Players stores extra info for this year's players. The two tables are joined when two fields, PlayerID and Year match. Current Player only has Year=2009 (global).
In Players I have records:
player1 violin 2008
player1 flute 2009
(note that the same player can play different instruments in different years).
Current Players has a record
player1 2009 extra-info
I have a Layout of Current Players that displays Players::Instrument so it shows the record as
player1 2009 extra-info flute
When I do a Find in Current Players with Players::Instrument[violin] it returns the player1 record. This is not what I want, and seems like a bug.
I see how to get around this with a script that explicitly goes to the Players table, performs the search and then goes to related records in Current Players, but this seems clumsy.