Another attendance question...

[20fret]

I thought I had a handle on Filemaker relationships but this "simple" problem has me stumped. I have reviewed some of the other attendance sheet threads, gone through a missing manual, and a bible, but none have quite worked (they've probably worked, I just can't seem to wrap my head around relationships). So here is my problem..

 

I'm working on a database for my soccer team and want to track attendance (among other things). I obviously have a player table the details each player, using a unique id. I would like to view their attendance info on this page, but don't want to create a field for every practice. So I have created a practice table in which each record is a different practice, and then record either absent of present for each player.

 

It is my understanding that I can display this data via a portal, but I need to link these two together with an attendance table which would have multiple player listings (one for every practice) - and this is where I have the problem. How do I create a record that takes both the PlayerID and PracticeID and combines them into one record. Can this be done with the "create new record via the relationship" or do I need to write a script?

 

Since I'm a visual learner I diagrammed a simple comma-seperated table for two players.

 

PLAYER TABLE

PlayerID

1

2

 

PRACTICE TABLE

PracticeID, Date, Player1Status, Player2Status

1, 2/1/08, Present, Present

2, 2/2/08, Present, Absent

 

ATTENDANCE TABLE

PlayerIDfk, PracticeIDfk, StatusFk

1, 1, Present

1, 2, Present

2, 1, Present

2, 2, Absent

 

Any thoughts?

Thanks

[LingoJango]

You can use the "create new record" option, but I would advise allowing that only from the table that you're going to enter the attendance data from, which I assume is the Practice table.

 

Set up a portal based on the Attendance table in a Practice layout. Put in Attendance::PlayerID, Attendance::Statusfk, and, if you have a Players::PlayerName field in the Players table, put that in, too (note: PlayerID has to be the field in the attendance table for this to work). Make Player ID a drop-down value list displaying the player's name as the second field.

 

Should work.

 

I wouldn't have separate fields for each player in the practice table, though. You can calculate the names or IDs of the absent and present players if you need to.

 

Since you're visual, if you join the forum you could post a picture of what you want your layout to look like and get input on how to get there. I'll be joining soon!

[Databasic]

Hi

 

i think the simplest way to do this is to set up an attendance table with four fields - pk_AttendID, fk_PlayerID, Practice date, status. The relationship is of course PlayerTable:pk_PlayerId and AttendanceTable:fk_PlayerID. Then you create a new record for each player attendance. Now you can set the practice date to copy the entry from the previous record and the attendance ID will be automatic, so you only have to enter the Player ID (dropdown list) and the status ( radio button )on each record. If you have ( say) 20 players or less that is not too much data entry. You need to remember to change the practice date on the first record created on a new practice date - then each new record will show the same date until you change it again. Then you can create a portal in the Player layout that will show all the related records from the attendance table giving you a listing of the attendance record for an individual player . Of course if you have 100 players you will need a different approach.. Cheers