1 2 Previous Next 16 Replies Latest reply on Apr 12, 2014 10:26 AM by philmodjunk

    Relationship issues (new FM user)

    nwhawksfan

      Title

      Relationship issues (new FM user)

      Post

      Im in the process of just learning Filemaker, so please bear with me. Everytime I think I have a grasp on the process, I get humbled. So here is the latest:

      I am trying to create a report about baseball. My goal is to have a report by position and display all the stats for players that play that position. I have downloaded a .csm file that has every player since 1871 and their associated stats. So here is my thought process.

      First I created a table for Position:

      Position
      pos_name
           pos_id


      Next I created a table of players in that postition. I imported these players from a .csm file through the import records: Since I did not care about LF, CF, or RF (instead of), I created a field that if pos was lf, cf, or Rf it would automatically read of.

      Player_pos
      player_name
           player_nameid
           imported_pos
           pos
           pos_id


      Player_nameid, is a derived from a calculation to use the first two initials of a players name combined with his last name to uniquely identify said player

      The table positions is the parent and player_pos is the child and a succesful relationship was established.

      Now the problem!!!

      I created another table to import a players raw stats

      stats
      player name
      player nameidfk
      stat_1
      stat_2

      I was hoping that player_pos could act as a parent (as one player has one position) and the stats table the child (one player has many stats). When I try to establish the relationship I get a many to many relationship, and my macbook almost takes flight in my frustration  

      I know that I had a stat column with only the player_nameidfk and entered the data manually using a portal I could do this using an intermediate table. The idea of manually entering literally thousands of lines of data makes me sad. 

      What am I missing? I may be just having a fundamental filemaker concept error, so some expert help is appreciated. Thank You

      J

        • 1. Re: Relationship issues (new FM user)
          philmodjunk

               Inspite of all the info in this post, certain key details are vague:

               

          When I try to establish the relationship I get a many to many relationship, and my macbook almost takes flight in my frustration

               What exactly did you do and exactly what results did you get? (It's not uncommon for a relationship that works fine as a one to many relationship to look like it is many to many in your relationship graph due to the absence of a field option such as unique values, to clearly identify a primary key field as a primary key.)

               but I see a potential issue to think about:

               

          Player_nameid, is a derived from a calculation to use the first two initials of a players name combined with his last name to uniquely identify said player

          That isn't necessarily a unique identifier. You may be initially limited to that method if that's the only viable ID in your imported data, but I recommend against actually using it as a primary key in your relationships. (Use the player's full name if possible to set up a temporary relationship by name and then update an ID field to take the place of using the Name field as you primary key.)

          • 2. Re: Relationship issues (new FM user)
            nwhawksfan

                 I have a table with a players name and stats associated with that player.  When I try to use the Relationship tab in "Manage Database", I get a many to many relationship, and therefore when I link it to the Position table and attempt to display a players data using a portal for a various position, it does not show up.   I dont know if it helps, but here is an image of my table structure.   

            • 3. Re: Relationship issues (new FM user)
              philmodjunk

                   Just because FileMaker shows "crows feet" on both ends of the relationship line, this does not mean that you have a many to many relationship nor that you have the wrong relationship set up. You get the "one to many look" of a single connecter line only if that field is an auto-entered serial number or if it has a unique values validation specified for it. That's info that "tells" FileMaker that it has to be one to many.

                   So which of these Tutorial: What are Table Occurrences? represents your "stats" table? None of the fields listed in your screen shot seem to match your description of that table.

              • 4. Re: Relationship issues (new FM user)
                nwhawksfan

                     The table "home_away_splits" is contains the Stats..    

                     Overall my goal is to be able to create a report (sorted by position) of all players and their home/away stats.   I am trying to use the data to conclude who in MLB has the largest split of home/away statistics.   

                     I read a similar answer to yours on another forum, and so I attempted to make player_name_batters::player_nameid an auto enter calculation so filemaker knows it is a parent, but it still did not work.    I also did the same to player_pos:player_nameid and same results.

                     Am I incorrect to think that the only way to display related data on a parent table is via a portal?   If that is correct then how do I establish that relationship?

                     Here is my latest image of the relationship structure.

                • 5. Re: Relationship issues (new FM user)
                  philmodjunk

                       Making the player_name_idfk an auto-enter calculation doesn't change anything here.

                       Frankly, I'm still trying to figure out how you want this to work.

                       Using a portal on the parent record's layout is the most common way to show multiple records linked to a given parent record, but it is not the only way. Sometimes it works out better to set up a list view of the child table records on a layout based on the child table with fields from the parent header placed in the header and/or the footer. This approach, though, requires taking extra steps to make sure that the found set of child records are only those for a specific parent record.

                       In your home_away_splits table, is there one and only one record for a given player or multiple records? From what I see here, it would seem there are multiple records for a given player in that table. Does each record represent the stats for a given player in a single game in which they played?

                       And is there one record and only one record for each player in player_pos? (That is what you posted earlier, but not all players play in a single position for their entire career do they? )

                  • 6. Re: Relationship issues (new FM user)
                    nwhawksfan

                         I am sorry for the confusion.

                         That is what I am attempting to do, ithat is, s to create a portal in the position table, that will show all players and their home/away stats

                         in my home_away stats table, there are two records for each player.  One for home stats, and one for away stats (I just realized that I need to import something to determine which record is home and away (i.e, a home_away field).  Thank you.  

                         There is only one record in the player_pos.  Players do play multiple positions, but typically and OF will shift from LF, RF, CF.  A player that will bounce around the IF typically will not be playing enough to create a sample size worth analyzing.  Either way, it will not matter as the stats for that player are going to be imported regardless of the players position.  

                         I am going to try to make a report using player_pos table and sort by position.  I think you just made me realize that I was making this harder than it needed to be.  I probably can get rid of the position table all together if I do that.   I will report my success/failure in a minute :)

                          

                          

                          

                          

                    • 7. Re: Relationship issues (new FM user)
                      philmodjunk
                           

                                That is what I am attempting to do, that is, to create a portal in the position table, that will show all players and their home/away stats

                           The relationship that you have specified matches by 2 letters of the player's firstname combined with their last name.

                           If you put a portal to home_away splits on a Player_pos based layout, it will list the two records (home and away) for that player.

                           If you set up Player_pos as a list view layout and put that portal in the body of the layout, you will be able to list multiple players and show their home and away stats for each.

                           If that's not what you want to see in that portal, you'll need to set up a different relationship.

                           Hmmm, I seem to recall that Babe Ruth started out as a pitcher... wink

                      • 8. Re: Relationship issues (new FM user)
                        nwhawksfan

                             The home_away stats still failed to populate in the new report.  I used the player_pos for the report and all I got was the player name and pos...

                        • 9. Re: Relationship issues (new FM user)
                          nwhawksfan

                               I just used the home_away stats for the layout and added the player_pos:pos field and all is great. now just some formatting and I think I will have this!

                                

                               Thank you for your help.  So what I think I learned is that even though the table structure shows that I have a many to many relationship, in the above image, the parent is player_name and child player_pos.  Is that correct?  

                          • 10. Re: Relationship issues (new FM user)
                            philmodjunk

                                 That would indicate that player_nameid does not match to player_name_idfk

                                 Make sure that player_nameid has text selected as the result type if this is still a calculation field.If player_name_idfk is a calculation, it should also be of type text. Since you are matching text, all it takes is a one character difference--even a space or tab character that you cannot see, to keep the records from matching the way that you want them to.

                                 But since you have player name fields in both tables, you could just match by the full player name in both tables--which would be slightly safer than matching by these calculated id's.

                            • 11. Re: Relationship issues (new FM user)
                              philmodjunk

                                   BTW, what I see in your screen shot does not look like a portal, it looks like a list view of the layout--though maybe those details were removed when you cropped the screen shot.

                              • 12. Re: Relationship issues (new FM user)
                                nwhawksfan

                                     I just noticed that even though I thought I had it, it still did not work.  I have no idea what to do now.  I am going to verify the ids now.

                                      

                                • 13. Re: Relationship issues (new FM user)
                                  nwhawksfan

                                       yeah, I tried to make a list view and sort the related records that way.  That is where I am stuck now.  I just verified that the player_nameid matches for a given player_nameidfk... I am so lost.  

                                       I guess what I need is a way to take a name and auto generate a serial number based on the name.  The problem is that I get my stats from different sources, and the only thing constant is the players name.

                                       I have used the following calculation to give me the player_nameid:

                                       Left(player_name;2)&RightWords(player_name ;1)

                                        

                                  • 14. Re: Relationship issues (new FM user)
                                    philmodjunk

                                         Yes, but if you are going to match on the name, why use a calculation that extracts only part of the player's name to use as a match field instead of just matching by the name field? What problem does that solve for you?

                                         With the layout shown, where do the empty fields shown come from? are they actually fields from home_away splits? If they are, you'll only see data from one of the two records (home and away) that are in the related table. You'd use the portal tool to add a portal so that you can see both records on that layout. If they are fields from some other table, then I would expect them to be empty in this screen shot and that matches what you are showing. If you enter layout mode and check the field names, on the layout the fields from a related table will show a :: at the start of the field names and if you click one of these fields to select it, you'll see the full tablename::FieldName in the Display Data From box in on the inspector's data tab.

                                         But you may have missed another detail that I pointed out: The result types for your player id calculation should specify text, not number. If they specify "number", they won't match records in this relationship.

                                         PS. once you have your relationships matching records, I can describe a method that allows you to match data on player name when you first import the records, but then a script uses the name matching to assign a serial number value to the newly imported records so that you only match by name during the initial import of the data, but let's fix one thing at a time here.

                                    1 2 Previous Next