This Should NOT be that hard

[K4TAY]

I have an issue I cannot quite find the answer for. I have 2 tables.

SCHEDULE (ID,DATE, TIME,LOCATION,HOME_TEAM, AWAY_TEAM)

TEAMS (ID,TEAM_NAME)

I want to create a Layout for the TEAM and have a portal that shows the schedule for that team in Chronological order but I cannot get the relationship link right.  I can obviously link team to home team and the portal shows all home games or link it to away team and it will show all away games. How do I link it to show ALL games?

[David McQueen]

There are probably a pile of ways of doing this.

Here, you will take advantage of the fact that FileMaker will read a return delimited text list in a field as a series of discrete keys.

1. Make sure that your Team ID is of the form Text.

2. Add a field to your schedule record of type text, indexed. You can call it TeamsKey if you like.

3. When forming a record in the schedule, cause TeamsKey to be populated wityh a return delimited list of the home and visiting team ID's. The contents of the field would look like this:

Home Team ID: 3

Visiting Team ID: 5

TeamsKey contents:

3

5

Relating the Team ID to TeamsKey will give you all games for that team whether they are visiting or home team.

HTH

dave

[K4TAY]

I'm close I can feel it but something is still wrong.

Is this the correct List syntax?

List(

HOMETEAM;

AWAYTEAM

)

Does it matter the Schedule table actually has the Names of the Teams in each record instead of the TEAMID?

Current Schedule Entry

DATE HOMETEAM AWAYTEAM TEAMSKEY

10/27/2010 Toronto New York (Toronto,New York)

Current TEAMS Entries

ID TEAM

1 Toronto

2 New York

I can go change all the values to correspond to the TEAMID in the Schedule table, but I assumed it did not matter.

Edited November 17, 2010 by Guest

[comment]

Does it matter the Schedule table actually has the Names of the Teams in each record instead of the TEAMID?

Yes, it does. A team name can be misspelled, or even change in time. This will break your links if they are not based on IDs. Your schedule's fields should be:

• ScheduleID

• Date

• Time

• Location

• HomeTeamID

• AwayTeamID

• cTeamIDs Calculation = List ( HomeTeamID ; AwayTeamID )

Then define three relationships:

Teams::TeamID = Schedule::cTeamIDs

Schedule::HomeTeamID = Teams 2::TeamID

Schedule::AwayTeamID = Teams 3::TeamID

To display the team names on a layout of Schedule, use the related fields from Teams 2 and Teams 3.

[reelsteve]

Key the two tables together using ID to ID.

Set the relationship connector to cartesian ("X" symbol).

This will now display all records in the portal.

(clarifying: ur Layout uses TEAMS as the primary table.

SCHEDULE is the portal on the layout).

In the portal settings, select portal filtering.

Filter by calculation:

Schedule::away_team = teams::team_name

Or

Schedule::home_team = teams::team_name

In portal settings,

Select sort,

select game date field and choose sort incremental.

Enjoy.

SCHEDULE (ID,DATE, TIME,LOCATION,HOME_TEAM, AWAY_TEAM)

TEAMS (ID,TEAM_NAME)

[comment]

(clarifying: ur Layout uses TEAMS as the primary table.

SCHEDULE is the portal on the layout).

In the portal settings, select portal filtering.

Filter by calculation:

Schedule::away_team = teams::team_name

If I follow this correctly, you'll end up with a portal that shows all away games of a team - something that the OP has already "achieved" by basing the portal on the Teams::Team to Schedule::Away_Team relationship.

Portal filtering could be employed in the opposite direction, but IMHO it wouldn't be quite as efficient as an explicit link.

[reelsteve]

Perhaps you missed my "or" operator and the second condition which brings both names to the equation.

I did an example file which uses portal filtering approach.

As far as inefficiency, I would challenge that statement given this usage. This is what portal filtering was designed to do.

It is on my list to do some testing to compare "snappiness" when filtering is done thru the relationship versus the portal filter.

hope this helps.

TEAM_schedule.fp7.zip

[comment]

I am afraid you are missing the point: your "relationship" (i.e the filter) is based on team names. You are storing the teams' names in the Schedule table - this is not good practice (as already mentioned earlier).

Moreover, your file cannot show any team data on a layout of Schedule. As I said, this could be solved by portal filtering - but if you wanted to calculate the odds of a match based on the teams' stats, it would become very complicated (i.e. less efficient).

[reelsteve]

I read this in the initial post

" can obviously link team to home team and the portal shows all home games or link it to away team and it will show all away games. How do I link it to show ALL games? "

agreed - if you let this solution evolve into a league tracker, there are areas where using name would lose records. As you know, this approach can be modified to remedy the use of name, replacing with an ID structure (but remain portal filter mechanism).

given the posts specific request, are you not happy with this solution ?

[comment]

If this discussion is limited to the relative merits of:

A:

Teams::TeamID = Schedule::cTeamIDs

versus

B:

Teams:[any field] x Schedule::[any field]

with filtering (based on Team IDs) applied at the portal level

then I would say that:

• A relies on indexed and cached data; B must be calculated in real time every time the record/layout/related data is changed or refreshed.

• A is bi-directional; B requires individual filtering to be applied on both sides.

• The results of A can be used by calculations, merged fields, etc.; with B, even a simple merged text such as "<> vs. <>" becomes a major undertaking.

These points could be expressed jointly as: A works on data level; B works on presentation level. A further effect of this is that B needs to be applied separately on every layout where it is needed.

• A is backwards compatible with older versions.

Now, if ALL that it came to were displaying all games of a team, then B could eliminate ONE calculation field. Given the above, I don't think the trade off is worthwhile. And it's never just about one particular issue. Solutions grow and present new requirements as they evolve.

See also:

http://fmforums.com/forum/showtopic.php?tid/214642/post/355862/#355862

[reelsteve]

This post is from entry level user trying to make a quick and easy tool.

There are many ways to accomplish this.

Yes - the fact that the teams are identified in the teams table and then "re-entered" in the schedule creates an environment not ideal IN CERTAIN situations.

You could also argue that the 2010 schedule shouldn't change if the team name changes later because it accurately reflects the game played on that day.

Further, the delimited list approach has baggage and is less desirable from the perspective of component scripting (reusable code).

I'm using a drop down on the schedule's home and visitor team names which pulls from the teams table so there is no chance that it will be entered incorrectly.

I will respectfully button up and leave the original poster with an offering that they are free to do with as they see fit. My advice build it both ways, learn both techniques - they will both have uses during your FM journey.

respect.

[comment]

This post is from entry level user trying to make a quick and easy tool.

True, but I feel that beginners are better helped when their questions are answered in a wider context.

leave the original poster with an offering that they are free to do with as they see fit.

I have no quarrel with that - as long as the merits of each approach are made clear so that they can make an informed decision.