Relationships while importing data from .csm files

[Jason Hefley]

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

[comment]

I don't follow your description that well. I believe you need to have two one-to-many relationships:

 

Positions -< Players -< Stats

 

Ideally, these relationships would be based upon matching:

Positions::PositionID = Players::PositionID

and:

Players::PlayerID = Stats::PlayerID

but I guess with imported data you work with the ids you get (as long as they are unique).

 

 

Now, if this is the arrangement you have, then what exactly is the problem?

 

 

stats
player name
player nameidfk
stat_1
stat_2

 

This part is also not clear: what does stat_1 and stat_2 stand for? IMHO, each stat value needs to be an individual record in the Stats table - but perhaps I misunderstand the nature of the data you have.

[Jason Hefley]

stat 1 and stat 2 were used for example only.  On the stats table I have 10 different stats.   I am trying to use a portal on the position table to display all players that play that particular position and their statistics (from the stat table)

 

The data that I am importing is a spreadsheet that has a players name, a player id and about 10 different stats.  When I attempt to create a relationship between the stats table to a player, I am getting a many to many relationship. 

 

Here is a pic of my table structure if it makes it more clear.  Thanks for your patience as I am new to Filemaker, and very eager to learn

[Jason Hefley]

I do have those unique identifiers as stated in your reply.  For playername_id I have ensured a unique identifier for each player by performing a calculation to make player_nameid and player_nameidfk to be the first two letters in a players first name and his last name (i.e Mike Trout = MiTrout)

 

Thanks again.

[comment]

For playername_id I have ensured a unique identifier for each player by performing a calculation to make player_nameid and player_nameidfk to be the first two letters in a players first name and his last name (i.e Mike Trout = MiTrout)

 

I am not convinced that this ensures uniqueness, since another player named "Miles Trout" will produce the same id; and why couldn't there be two players with exactly the same name?

 

Anyway, the crowfeet you see on the relationship graph do not indicate a many-to-many relationship. They just show that Filemaker is not aware that this is a one-to-many relationship. This cosmetic flaw can be fixed by making the id field a Text field, with auto-entered calculated value (using the same formula) and validating the field as unique.

 

Validation is also the correct tool to ensure uniqueness - although you need to consider what should be done (by the user) when the validation fails.