2 Replies Latest reply on May 22, 2013 8:29 AM by philmodjunk

    Trouble with relationships / relational data



      Trouble with relationships / relational data




           I'm sure there is an easy solution, but I have not been able to figure out this problem.  

           In short: I have a table with a relationship to another table, based on the name of the athlete.  A field then displays the matches that athlete participated in.  That's been acomplished.  What I haven't been able to do, is set other fields to the proper value, based on the specific match.  What is happening, because the tables are related based on the Athlete's name, is that it is only displaying the value for another related field (for example, number of touchdowns that athlete had in specific match) of the first matching result, based on the name, from the main table.....

           So, how do I specify that the value of a field is linked to the specific match?  I have not been successful for several hours, and ANY help is tremendously appreciated!


           Thanks so much!


        • 1. Re: Trouble with relationships / relational data

               Hello Ryan,

               It sounds like you have a "Player" table and  "Match" table where    Player::Name = Match:: Name.

               First please consider using an autoenter ID# as your key field.  Two players may have the same name...then you'd be stuck.

               Toward your question, it sounds like you want a portal.  (Look up portal in the help menus)

               On a layout based on your "Player" table, create a portal showing multiple records from your "Match" table.  This portal will show ALL of the related records, not just the first one.

               This is what you are looking for?

          • 2. Re: Trouble with relationships / relational data

                 And if you need to refer to date in a specific Match related to that player, you may need to use either a different relationship, a filtered portal or (if using FileMaker 12) a calculation field with ExecuteSQL and a SQL query that includes a WHERE clause...


                      Two players may have the same name...then you'd be stuck.

                 And that's just the tip of a very big iceberg of trouble that you buy for yourself when relating tables by name. People change their names due to marital status changes and other reasons. Different people enter the same person's name differently (I answer to Phil and also Phillip...) and sometimes people just enter a person's name incorrectly. All of those scenarios can result in situations where you need to modify the name for a given person in your database. If you have related tables linked by name, any such change breaks the link to the other related tables.