need a way to have one import go to two tables

[cali]

hello everyone, this is my first post. I've been reading for hours trying to figure out a solution and I'm having trouble. I've been using filemaker for almost a year on a pretty basic level. I use it to keep a database of players in an adult sports league. Up until now, there have been two sports, about 4 seasons each per year. I had one table: Players and in that table I had a field called Current Sports as well as "All Seasons". Current sports reflected the sport/season combo the player was currently participating in such as:

Fall 09 Basketball

Fall 09 Softball

"All Seasons" listed every season of every sport the player had played.

It's getting to be too big and I'd like to have an easy way to separate sports. I experimented with separate files for each sport, and now separate tables for each sport.

I need a way to have one import go to two tables. i.e Import new players into Volleyball but those players also show up in the "Players" table which reflects all players of all sports for all time.

What's the best way to accomplish this? Thank you SO much for any help. I feel like I'm close, but I'm having trouble with relationships because I don't have a real firm grasp on them.

[comment]

You don't need (and shouldn't have) separate tables for each sport. You should have one table for all players, one table listing all sports available, and a join table indicating who played what when.

[cali]

Thanks for the reply comment! I'm not sure why you don't think I should have sport tables. I also don't understand how that would make things much different than the way I have it set up. Can you elaborate please?

I was thinking about it more and it seems like having one main table that shows most Player fields would be best. Each field in the "Player" table should show the most recent result from one of the sport tables. There are a few fields that should be independent by sport. i.e. Bob Smith plays all sports all seasons, so I need a way to retain his "team" field and "previous teams" field for each sport without it being overwritten by the most recent sport. It seems like it would be easiest to work with separate sports tables, so that I can determine the number of uniforms to order, etc.

So if I import 4 players into a "basketball" table, I'd want the updated info, aside from team, previous teams, and requests, to be reflected in the main "player table" but not show up in the "football" table.

Am I overthinking this? I pretty much exclusively use table view for my projects so I can see and use more information at once.

Thanks!

Edited July 8, 2009 by Guest

[comment]

I'm not sure why you don't think I should have sport tables

Because you want to be able to add another type of sport without having to modify the structure. And because player's data should be stored in ONE location, where it can be modified without having to replicate the changes to other tables.

If I understand correctly, what you describe is very similar to a school where students enroll in various courses. This is typically handled by three tables: Students, Courses and Enrollments. This way nothing is ever overwritten, since a new enrollment record is created every time a student signs up for a course.

[cali]

Ok I think I understand you. How would that work for fields that are sport dependent? It seems like I want something similar to the actor/movies example here: http://fmprelationships.blogspot.com/2009/05/filemaker-join-tables-explored.html

which I think is what you're suggesting also.

Is there a way to do many to many without using portals?

So if Bob Smith plays in multiple sports, he should have multiple teams, one for each sport. When he signs up, he may have requests to be on a team with a certain player. I'd like that to be independent based on the sport, and not updated in the main player record. In your example, would I import the players into the Player table and assign a sport somehow? I'd like to work in table view all the time so I can utilize more information and for ease of use.

Thanks comment.

[cali]

Examples:

Basketball; Fall 09[;u] Bob Smith, Wildcats, wants to play with Bill, Betty Crocker, tbd, same team as Sue Johnson

Soccer; Summer 09; Bob Smith, Scorpions, no requests, Amy Sellers, tbd, same team as Jim Johnson

Bob has played two sports, one season each. I don't want Wildcats to override Scorpions because he may play fall soccer and I need to know which team he was on in Soccer. Also, Betty may play Soccer but want to request to be on another friends team because Sue Johnson isn't playing Soccer. I don't want to lose their team requests if they're playing more than one sport or season at a time.

I want to be able to work with Fall basketball (and each individual season of a sport), forming teams, ordering shirts, etc. but also have a full listing of all the players that have ever played, regardless of sport or season.

I understand not wanting to have Bob Smith in the database multiple times. I currently import registrations from csv files and have the import update by matching fields and add new records if there is no match.

How would I set up the join table in this situation?

Thanks!

Edited July 8, 2009 by Guest

[comment]

Yes, actors-roles-movies is another example of the same principle. In your case, it seems that you want to enroll players into teams rather than directly into a specific branch of sports, so perhaps a structure like:

Players -< SignUps >- Teams >- Sports

might be more suitable.

As for requests, that looks like another join table to me, probably a child of SignUps (e.g. when Adam signed up for Scorpions in Fall '09, he requested Betty and Cecil as teammates - two records in the Requests table).

Is there a way to do many to many without using portals?

Portals are a user interface element - they come AFTER your data model (if you need them). However, managing a many-to-many entirely from a table view is not going to be very convenient, IMHO.

[cali]

Most of the time a player will sign up without a specific team or a player request. I just import their info into the Players table and then have been adding the season (Fall 09 Dodgeball) in the Current Sports and All Seasons field. Then I perform finds for "Fall 09 Dodgeball" to work with all the signups for that season. I assign teams if they weren't requested and use find to check and make sure each team is balanced. Then I use show all records to see my total players all time. In the Current Sports field there may be a few listed, one above the next, in order for me to perform searches for each season or sport.

All Players - > Sports - > Seasons - > Players is kind of how I envision it. I can use Search and Find for teams pretty easily it seems like if I'm in a specific layout or table right?

[comment]

Most of the time a player will sign up without a specific team

That's OK, since a signup could be related to Sports too. A record in SignUps would probably look something like:

SignUpID (serial number)

PlayerID

Season

RequestedSportID

RequestedTeamID

AssignedTeamID

I'm afraid I didn't understand the rest of your post.

[cali]

So I would import the record from the registration download directly into signups and then assign a sport id? What would the record look like in Players?

Would there be a way to separate all the volleyball seasons under Sports from the basketball seasons? Or would they all be thrown in there: Fall 09 Basketball, Summer 08 Volleyball, Spring 09 Volleyball, Winter 08 Indoor Soccer, etc.

[comment]

So I would import the record from the registration download directly into signups...

Possibly - provided that the player is already in the Players table and that the source contains the correct PlayerID.

... and then assign a sport id?

I believe the SportID would also be selected by the player - you would only need to assign them to a team.

What would the record look like in Players?

It would contain all the information about a player (name, gender, DOB, address, etc.) and no information about a player's association with any team/sport/season. IOW, it contains the PERMANENT data of the player.

Would there be a way to separate all the volleyball seasons under Sports from the basketball seasons? Or would they all be thrown in there: Fall 09 Basketball, Summer 08 Volleyball, Spring 09 Volleyball, Winter 08 Indoor Soccer, etc.

I believe sport and season should be two separate fields. Separating records by either/both criteria should be very easy by either performing a find or by a relationship - it depends on the context.

[cali]

I tried to set up something based on what you told me Comment. Can you take a look at let me know what I'm missing? Thanks!

Xoso_Copy.fp7.zip

[comment]

This is closer to what I had in mind:

[cali]

are Sports 2 and Teams 2 Tables or TOs?

I really appreciate your help. The relationships idea is very new to me.

[comment]

Sports 2 is a TO of Sports; Teams 2 is a TO of Teams.

I think you may be making a mistake by trying to implement this in a file, before having completed your data model as a concept. The relationships graph is not an ERD, and it can be confusing - esp. with the TO vs. table business.

[cali]

I had things working well before trying to use the relationships. I would just import into Players and add the sports/seasons/teams manually. Then I would use search/find to narrow results.

Here's a new file. Do I have the fields set up right? I added a test record to players but nothing seemed to happen. I don't really know what else to do so I really appreciate your help! I suppose I could go back to how I was doing it, and just import players twice, one to a table for the sport, and once to the table with all players but it seems like there should be a way to only import players once and only have them listed once.

Thanks again Comment.

Xoso_Copy.fp7.zip

Edited July 9, 2009 by Guest

[comment]

Here's a VERY rough sketch of what I had in mind. I believe it's easier to start from scratch than to deal with all the extra baggage that comes with a template.

I had things working well before trying to use the relationships.

Well, then you shouldn't be needing any of this.

Sports.fp7.zip