4 Replies Latest reply on Feb 24, 2009 3:20 PM by alubiw

    Find using field of related record



      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. 

        • 1. Re: Find using field of related record

          Howdy alubiw,

          Thanks for the post and welcome to the Forum.


          What table is your layout based on?  Your search should be done on a table based on Current Players.  If your layout is based on Players, you'll be searching All Players.  If your Table is based on Current Players...you'll only be looking through the Current Players that have a linked "violin" field.


          If you want to find only people who are current, and played violin in 2009, you'll have to do a search for both "Violin" AND "2009" since your current player DOES have a violin relation to the AllPlayers Table, it just happens to be from 2008...they will show up unless you limit the search by adding the second search criteria.


          Did I make sense?

          • 2. Re: Find using field of related record

            Thanks for the reply.


            It still seems counter-intuitive to me.


            My layout is based on Current Players, and that's where I'm doing the Find.  I understand that if I search for "violin" AND "2009" I'll get the right thing, but I don't even have the year displayed in Current Players because it's the same for every record.  Yes, I can write a script.  But it just seems weird that a search for "violin" gives back records with "flute" as the instrument.  How is a more naive user of my database going to cope with that?


            Have I designed things poorly?  Perhaps I should copy over the Instrument field from Players to Current Players, but that seems to me to defeat the whole point of relational databases. 

            • 3. Re: Find using field of related record
                 I am not following your structure too well, but a couple of points stand out:

              1. When you search a related child field, you will find all parent records that have at least one child that meets the criteria.

              2. A global field cannot be indexed, and therefore cannot be used on the "other" side of a relationship. IOW, the relationship is not valid in the direction towards the global matchfield.

              3. I don't see the advantage of having two tables for the same thing.

              • 4. Re: Find using field of related record

                Ah ha!  The problem is using a global field in a relationship.  OK, that's easy to change. Thank you.


                (The point of the two tables, by the way, is that I want to store extra information for Current Players, but don't want to have all those fields for all Players.)