How to overcome a duplicity of date.

[Cazito]

Hi evry'body! I

[stanley]

Cazito:

Welcome to FM Forums, and to FileMaker. I think you're right - the "lineup" table is redundant. However, definitely avoid using repeating fields. They cause more problems than they are worth.

If you think about a football squad, there are eleven players on the pitch, plus five (or whatever) substitutes on the bench. Your best bet is to create eleven separate fields ("Player1", "Player2", etc) for the players selected for a match, plus how ever many extra fields for the total allowable substitutes ("Substitute1", etc). Enter the players in there.

Always have a serial number for every record - you'll discover they are indispensable for relationships.

-Stanley

[Cazito]

Wow! Thanks Stanley, this is unbelieavable! I mean, with Paradox I felt alone in the world. Now here I am, posting a message, and in less than five minutes I have a reply. This is great!

I guess I

[stanley]

Cazito:

Now you're getting into the realm of the real relational database. I didn't realize you wanted to track everything that happened in the match, but you do, so...

In your match table, you can track things that are constant to the match - i.e., who started for each team (or just your team), where it was played, how many attended, etc. Each record in the table (each "match") will have a serial number, auto-entered, non-modifiable. Let's call it "MatchNumber."

You can create a different table for goals, with a portal to it from the match table. When a goal is scored, you create a record in the goal table, with the "MatchNumber" being put in a field in the goals table. You can also put in the goal scorer and the time. That's all you need. Any further info on the player who scored the goal can come from a relationship. Likewise with yellow and red cards. You can have a card table, and each time a card is issued, you create a record, put in the "MatchNumber", put in the "PlayerNumber" and the time the card was issued, and now you can display it in a portal in the match table, and in the player table...

-Stanley

[Vaughan]

Or could the card and score table be one and the same... like a "game event" table?

[stanley]

Vaughan:

Excellent suggestion. A "Game Event" table is a better idea. You could also fit in substitutions, injuries, whatever. That's what I get for posting at the end of a 14-hour day.

-Stanley

[Ken Newell]

Remember 7 allows relationsips to be built on more than one field so you could easily have both the date and game number for a day (i.e. game 1, game 2 etc.) which would serve the same purpose as the match number.

[Cazito]

Thanks for all the suggestions. I guess I will adopt this "game event" table and the "game number" field and see how it works.