Begging for help on many-to-many relationships

[Lemur]

Sigh...I feel like I've been through the wringer!

I am using FM Pro 5.5 -- have been trying to get my head around this for a while, and I'm really just beaten down

I am essentially converting over a massive (40mb) Excel spreadsheet into database form. This database contains all the information for my boardgaming group.

I believe I need one db with games (that are owned); one for players; one for sessions. Each of these is set up already, and each has a unique id code (each one starting at "1" and growing incrementally).

From there, the sessions db will allow me to show which players attended. This way, I can track each individual players' attendance, and report those totals BACK to the Players' record in the players db.

The sessions will also show which games were played.

I had set up the games and players as value lists, so I can easily pull down the information and select a player or a game.

I think I need ANOTHER db, that lists all the games played, by date, with the players that played them. Again, the players would be a value list.

From this database, I need to track scores and ratings for each player that played the game. That information is crucial, as it flows into approximately 30 different calculations (again, one for each player).

From there, each players cumulative scores for all games the player played in will be reported back to the players database. Example: if Chris played 5 games, his totals (for the calculations) would be summed and reported back to HIS record.

Additionally, for each game in the games database, each individual's player score/calculation would be retained -- so, if in the group, there were 6 who played Game A, then, 2 who played Game A and then, 3 who played Game A, but the 2 in the second set played all 3 times, it would sum their score, and it would list each player and his overall rating for each game in the specific game record.

I understand - on paper - basically, what I want to do, but I lack the ability to set this up.

I've printed out - close to 1000 pages on many-to-many databases, join files, portals, etc. Nothing I'm doing works. I did find this post on this board: http://www.dbtalk.net/comp-databases...her-47282.html (Display in a Portal a related field from another file)

But, when I try to do what Peter recommends (add a name through the value list to the portal) I get an error that "this field is not modifiable".

I am essentially trying to recreate a standalone program I found here: http://www.goldraven.com/GameTracker/GT.html

But, this is protected (even tho it's free) so I cannot see the define fields, define relationships and layouts.

If anyone could show how the four files would be set up - either with actual files or on the board, you would save me tons and tons of time!!!

I can, additionally, send my files - some of which are merely 'samples' and some of which are my ORIGINAL intent (which used repeating fields and import scripts) to show what I'm looking to do.

Please help....Thanks!

Chris

[John Mark Osborne]

While this doesn't answer your question directly, the example file in the download demonstrates a many-to-many relationship. Hope it helps.

http://www.filemakerpros.com/joinprint.zip

[Lemur]

Hi John,

I actually can't open the file - as it's in FM7, and I only have 5.5

Can you resend? or save it in an older format?

Thanks...I'm interested in taking a look at it!

Chris

[Inky Phil]

Hi Chris

Sounds to me like this might help

http://www.fmforums.com/forum/showtopic.php?tid/175345/post/199473/hl/kachel/#199473Why

Phil

ps Why not use gametracker since it is free?

[Lemur]

Hi Phil,

Thanks for the link...

I'd love to use gametracker, but, my own Excel sheet includes about 15% different information (i.e., Gametracker has more than I need on some levels and less than I need on others).

If I could manipulate it, I'd CLEARLY stop trying to reinvent the wheel, and I'd just change what I needed for my purposes...but, that doesn't seem possible, so I'm left with trying to build something for myself...

It's a good thing I enjoy figuring this stuff out...sometimes... :

Chris