Currently I have the portal based on the Carnival Info table.
And what is the layout based upon?
Do you create one record in Carnival Info for each time that you hold a carnival? Or does one record in this table represent something else?
For a given athelete, so you want to see a total score for each carnival in which they competed in the portal or do you want to break down the scores by individual events in which they competed?
Thanks for your response. Yes - each carnival has a new record created in the Carnival Info table. This record just contains information such as date and venue. The portal is located on an Athlete layout which is based on the Athlete table. Within the portal i just want to see how many total points they accumulated for that carnival. From there the user will be able to click on a button that has a script to take them through to the carnival with their event summary if they require more specific information.
I have attached an image of the layout to illustrate what i am attempting to achieve.
Thanks for your input
What version of FileMaker are you using? If you have FileMaker 12 or newer, you may want to use a calculation field with ExecuteSQL instead of a summary field to get the total score for each carnival in which that athlete participated.
Without SQL, you'll need to use some scripting and a global field updated by a script to get to the correct set of results records for each carnival. But I ran into a problem mapping that out in terms of relationships. The results recorded appear to be specific to a team not an individual. Is this correct? And is an Athlete only a member of a single team for each carnival?
I am using Filemaker advanced 13.
"The results recorded appear to be specific to a team not an individual. Is this correct? And is an Athlete only a member of a single team for each carnival?"
Hmm - i though the results were linked to the athlete not the team. Yes each athlete is only on one team for each carnival. Athletes in general should stay in the same team for the calendar year
"you may want to use a calculation field with ExecuteSQL instead of a summary field to get the total score for each carnival in which that athlete participated."
I have up to this point avoided SQL as my knowledge is not very strong in this area - but now is as good a time as ever. Any suggestions on where to start learning SQL or how to implement what you have suggested?
First, we have to resolve this issue:
Hmm - i though the results were linked to the athlete not the team.
What I see in your relationships is:
The match fields in question are:
TeamMembership::__pk_TeamMembershipID = EventResults::_fk_AthleteID
Upon taking a closer look, this isn't really a case of matching to results by Athlete, nor by Team. It matches an athlete to all results for that athlete but specific to their participation on a particular team. If they participate in two carnivals, they participate in two teams and thus match to event results for each team.
Hmm that suggests a very simple approach.
An unfiltered portal to TeamMembership "Should" list all the teams from all the carnivals in which the athlete has participated. A calculation field (I see one named cPoints that might already do this) can use Sum to sum all the points in EventResults for that athlete's membership ID.
This same portal can include a field or two from Teams if you also want to show the name of the team.
There is one key (Pun intentional) flaw in your design here.
You match to TeamMembership by name instead of an ID number defined in Athletes. This means that very athlete must have a unique name. If two athletes named "John Smith" participate, they'll see each other's participation in this portal. Changing this relationship to match by an ID would eliminate that issue.