Relationship Basics

[Rupert85]

Hi All -

Hope you don't mind a newbie landing here. Looking for a pointer in the right direction for the best place to learn about tables and relationships in filemaker so I can press on with a couple of projects. The internet is littered with FM learning areas - can you recommend any in particular?

I'm very familiar with IT and logic but not so much databases and in particular, filemaker. Aiming to build a couple of databases in my spare time so downloaded a trial of FM Pro 11.

The first database is for my football team; I want to build a table to record information on football matches for my team throughout the season:

-basic info on match: ie date, time, season, competition, opposition, home/away)

-Our squad, for each match: formation, who plays, how long for, who was suspended for the match, who was injured, who wasnt in the 20man squad (i want a record for every player in my squad at the time of each match)

-goals: for or against, who scored, any assists, nature of goal, who was at fault

In order to achieve this I reckon I need a few tables:

1. MySquad (ID, Name, Join Date, Leave Date, DOB, Height)

2. Staff (ID, Name, Position at Club, DOB, join Date, Leave Date)

3. Teams (Opposition teams: ID, Name, Home Ground)

5. Referees/Officials (ID, Name, DOB, Hometown)

6. MatchInfo (ID, Date, time, venue, competition, opposition, goals for, goals against, formation home team, formation away team, referee, officials, and importantly - a record for each player who is in my squad on the day of the match)

**** Where I struggle to get my head around is creating table 6. Any advice?

If the advice is give up now, that is fine!

[comment]

Where I struggle to get my head around is creating table 6

I think you are trying to cram too much information into one table. The basic Matches table should contain only fields describing the match itself - IOW, things which one match has one of, e.g.:

Matches:

• MatchID

• Date

• Time

• Venue

• OppositionTeamID

• HomeOrAway

...

Things like goals, referees, officials - i.e. things which one match can have many of - need to go into related tables, for example:

Goals:

• GoalID

• MatchID

• PlayerID

• Time

...

If the advice is give up now, that is fine!

I wouldn't say that - but keep in mind your specs are pretty ambitious.

[Rupert85]

Thank you.

What if I strip it back to a simpler db, I think the relationship construction would be easy enough. Would you agree?

My challenge is the player record table.

How would I be able to create a GUI that, for each match and in recognition of the date of that match, would allow me to record the 'Status' of every player in my squad at that time (will need to reference the LeaveDate in the players table). Is that something that is complex?

-Squad Table

• Player_ID
  
• PlayerName
  
• JoinDate
  
• LeaveDate
  
• DOB
  
• Height
  

-Matches Table

• Match_ID
  
• Date
  
• Time
  
• Venue
  
• Competition
  
• Opposition
  
• GoalsFor
  
• GoalsAgainst
  

-Player Record Table

• Player_ID
  
• Match_ID
  
• StatusList
  

**Status List:

Not Available (Injured),

Not Available (Suspended),

Not Available (Out on loan),

Not in Match Day Squad,

In Squad (not stripped),

Substitute (not used),

Substitute (used),

Starting (did not play whole match)

Starting (played whole match)

[comment]

What if I strip it back to a simpler db, I think the relationship construction would be easy enough. Would you agree?

Yes, I would. Although I would rename your Squad table to Players (or TeamMembers) and Player Record to Roster (or MatchPlayers).

Now the question is, do you really need to record the status of EVERY player for EVERY match? I see two categories in your list of values:

[a] did not participate at all (injured, suspended, etc.);

did participate in some way (incl. sitting on the bench).

[Rupert85]

Thank you.

Yes its really important I record what's happening with EVERY player.

And at any one time, there are about 25 players, +/- 2.

[comment]

Yes its really important I record what's happening with EVERY player.

Well, then you must also create around 25 records in the join table every time you create a new match record (this is not difficult to do by scripting). In the end, you will have a portal on the match layout showing a row for every player - and you can then select the status for each.

[Rupert85]

Well, then you must also create around 25 records in the join table every time you create a new match record (this is not difficult to do by scripting). In the end, you will have a portal on the match layout showing a row for every player - and you can then select the status for each.

Thank you. Would creating this number of records a number of times over cause any problems going forward?

[comment]

Would creating this number of records a number of times over cause any problems going forward?

Not sure what you mean by that. What kind of problems, and going forward to where?

[mpau]

Hi Comment,

How can I get in touch with you about doing some consulting work for me? Your posts are always the very helpful to me and I'd love to have you take a look at a database I've built.

thanks,

Michaele

[Rupert85]

Not sure what you mean by that. What kind of problems, and going forward to where?

I mean will creating a number of records in the same table cause any problems as the database ages?

That said, I'd imagine we would only populate it with around 50 matches a year, 50x25 = 1250 records per year

[comment]

I don't think it should. In any case, I don't see that you have a choice here - if you need the data, you must record it. If it gets too bulky, say in 50 years or so, you could archive the early years.

[comment]

Hi Comment,

How can I get in touch with you about doing some consulting work for me? Your posts are always the very helpful to me and I'd love to have you take a look at a database I've built.

thanks,

Michaele

Hi,

I've sent you a private message.

[Rupert85]

doh - ignore that

[Rupert85]

Next simple question, if you can be so kind.

Table - Matches

• Match_ID
  
• Club_ID
  
• MatchDate
  

Table - Clubs

• Club_ID
  
• ClubName
  
• ClubCountry
  

On my Match layout - I have various fields, one of which is the "Club Name". Beside this, I want it to show the "Club Country". Ideally I want the Club Name drop down to be filtered by the selected country, and have a default country selected for new records.

I have set the country's within the Table:Clubs - ClubCountry as a valuelist.

And on the layout mode I have inserted a field, selected Table - Clubs, and then selected the field: Club Country.

I had thought, that if I then selected the club as being Manchester United the "Club Country" field in the layout would reflect the country of Manchester United from the Clubs Table. But it doesn't.

Any advice?

As much as I appreciate help on this, is there somewhere you recommend I read to learn?

[comment]

If I understand this correctly, there are two issues here: [a] how to select the club for a match, and how to display the selected club's data (name, country, etc.).

To select a club, you must enter the club's ClubID into the ClubID field in Matches. The simplest way to do this is to define a value list showing values from field Clubs::ClubID, also displaying Clubs::ClubName. Set the field Matches::ClubID to use this value list.

Narrowing the selection to clubs from a selected country is a bit more complex - I'll leave it aside for the moment. The important thing to remember is that once you have selected a club, all of club's data becomes related through the relationship based on matching ClubID. So once again, you only need to place fields from the Clubs table on the Matches layout (it's best to make them non-enterable there, so that you don't edit them by mistake).

[Rupert85]

If I understand this correctly, there are two issues here: [a] how to select the club for a match, and how to display the selected club's data (name, country, etc.).

To select a club, you must enter the club's ClubID into the ClubID field in Matches. The simplest way to do this is to define a value list showing values from field Clubs::ClubID, also displaying Clubs::ClubName. Set the field Matches::ClubID to use this value list.

Narrowing the selection to clubs from a selected country is a bit more complex - I'll leave it aside for the moment. The important thing to remember is that once you have selected a club, all of club's data becomes related through the relationship based on matching ClubID. So once again, you only need to place fields from the Clubs table on the Matches layout (it's best to make them non-enterable there, so that you don't edit them by mistake).

Thank you. I hadn't set up the list correctly with both fields. Only issue I have now is once selected from my drop down list, the field displays the ID rather than the name. any way around this?

[comment]

Place the related name field (non-enterable!) over the ID field.

Or change the field from drop-down to pop-up (you can also set the value list to show only the second field - provided the club names are unique).

[Rupert85]

Place the related name field (non-enterable!) over the ID field.

Or change the field from drop-down to pop-up (you can also set the value list to show only the second field - provided the club names are unique).

Yes the club names are unique, but for some reason I cant seem to show only the second field. It works fine to present the drop-down but upon selection of the team it shows me the TeamID.

[comment]

It only works with a pop-up.

[Rupert85]

It only works with a pop-up.

Thanks. Of course this is only relevant if I want the related field, like country, on the same layout. Am I best aiming to leave out the selectable country option?

[comment]

Am I best aiming to leave out the selectable country option?

No, not if you want it. I just put it aside until you get the basics.