5 Replies Latest reply on Jul 24, 2014 8:34 AM by philmodjunk

    Summary field in portal

    tomo17

      Title

      Summary field in portal

      Post

           I am trying to add to the complexity of a current database that I have which is a carnival management program.  Currently I am only able to record a single carnival but I would like to be able to have a database that records all of the carnivals that we run.

            

           I think I have the nuts and bolts of it worked out but I have so far hit a wall with a historical record of points for an athlete. I am wanting a portal on the athlete page that shows a history of the carnivals they have competed in and how many points they accumulated for that carnival. Currently I have the portal based on the Carnival Info table.  I have tried using a summary field and a calculation field but to no joy. all i get is there overall points accumulation.

            

           Any help will be appreciated

           My current set up is 

      Screen_Shot_2014-07-19_at_7.45.08_pm.png

        • 1. Re: Summary field in portal
          philmodjunk
               

                    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?

          • 2. Re: Summary field in portal
            tomo17

                 Hi Phil

                 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

            • 3. Re: Summary field in portal
              philmodjunk

                   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?

              • 4. Re: Summary field in portal
                tomo17

                     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?

                      

                Thanks again

                • 5. Re: Summary field in portal
                  philmodjunk

                       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:

                       Atheletes>-----<TeamMembership----<EventResults

                       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.