Help with tennis database set up and calculations
I am new to FileMaker Pro. I have a flat file database that I use to track 10-12 players two times a week. We usually have one or two courts, as needed. I schedule who plays with whom and who makes a court reservation with the club and brings new balls. I simply post this on Google docs. So, I have date, matchID, P1, P2, P3, P4, and reservation. For P1-4 and reservation, I use the last names.
I would like to display the number of times a player made reservations and brought balls. This needs to be a ratio compared to the number of times they played.
I would also like to create a table that shows a who played with whom. We like the variety of combinations that can result from our number of players. I don't want to keep putting the same four guys together. When someone signs up, I want to know who they have played with the most and the least. Ideally, this would be a table with their initials across the top and down the sides. The intersection of each player would equal the number of times they have played.
In trying to do this, it is easy to find who made reservations as they are in a single field. Calculating how many times each has played requires a find with their name in each P1-P4 fields. Is that the best way? After that, I am confused. Do I need a calculation field or is this solved with a number of scripts?
I am hoping someone can follow the logic of my request and suggest the structure and tools that I need to use to solve this problem.
Thank you in advance,