1 2 Previous Next 27 Replies Latest reply on Jan 31, 2013 2:08 PM by MichaelPalma

    Help with summing records from multiple portals

    MichaelPalma

      Title

      Help with summing records from multiple portals

      Post

           I have multiple portals that are used to sort records for individual sports events. Each portal is used to 'rank' the players and is working as expected. The problem I can't quite get my head around is adding these records together to get a 'total rank' for each participant. I don't want the 'place' (1,2,3,4) I want the total points. Below is and example;

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                Field Day
                          Participant ID                     Event 1                     Event 2                     Event 3                     Total
                          1                     2                     1                     1                     4
                          2                     1                     3                     2                     6
                          3                     4                     4                     4                     12
                          4                     3                     2                     3                     8

            

            

            

            

            

           Each event is actualy a calculation of many records (amller events) so that is why I'm using portals to sort them. So how would I get the final 'Total' column? I cannot figure out a way to get the 'Rank' out of each portal.

        • 1. Re: Help with summing records from multiple portals
          philmodjunk

               Please describe your database in more detail.

               Is this a list view layout with one row portals for each of the three events?

               Are the portals to different occurrences of the same table or completely separate tables? (I'd use different occurrences of the same table).

               If these are one row portals, do you use filtered portals or different relationships for each portal?

          • 2. Re: Help with summing records from multiple portals
            MichaelPalma

                 Each participant has a 'profile' records (table)

                 This 'profile' is made up individual event records (another table)

                 The event records contain data on how well they did on different activities. cones knocked over, laps completed, hurdles cleared, etc.

                 So (based on your ranking calculation Phil :) ) I have portals showing on another table called Event Totals, that rank the participants in decending order on accomplishemnts (laps run, hurdles cleared, walls climbed), and in ascending order for errors (hitting cones, hurdles, etc). Each portal sorts on one of these criteria. What I want then to do is add up the ranks for each participant. The lowest 'total rank' is the top the highest total rank is the bottom. I just need the totals, nothing fancy. I just can't figure out how to get those ranks that are visible in the portals totalled.

                 Does that make sense?

            • 3. Re: Help with summing records from multiple portals
              philmodjunk

                   It makes sense but doesn't fully answer my questions.

                   Is this a list view layout with one row portals for each of the three events?

                   Are these portals to different occurrences of the same table or completely separate tables? (I'd use different occurrences of the same table).

                   If these are one row portals, do you use filtered portals or different relationships for each portal?

              • 4. Re: Help with summing records from multiple portals
                MichaelPalma

                     My portals are not on a list view layout. This master Event records has 9 portals on it each portal has 1 line for each participant along with the corresponding criteria and rank.

                     The portals are pulling data from each participants profile, which is made up of calculated data from participant/event records.

                     these are not 1 row portals, there really is no limit to the # of records that the scrollable portal would contain.

                     if you would find a screenshot useful please let's know and thank you for your time.

                • 5. Re: Help with summing records from multiple portals
                  philmodjunk

                       I don't see how "9 portals" matches up to your initial screen shot. Is this screen shot an example of a single portal?

                       A screen shot of the relevant portion of Manage | Database | Relationships with commentary identifying which "boxes" represent the layout and which your portals would be helpful.

                  • 6. Re: Help with summing records from multiple portals
                    MichaelPalma

                         Sorry, that is the initial plan. I'm working with 3 to get it working before make it more complicated. I'll upload a screenshot shortly. Thank you again.

                    • 7. Re: Help with summing records from multiple portals
                      philmodjunk

                           Your screen shot looks like you have a single portal with 3 fields in it for ranking in events 1, 2 and 3 rather than 3 portals.

                      • 8. Re: Help with summing records from multiple portals
                        MichaelPalma

                             Perhaps this screenshot will be helpful. In this example there is a portal for each event that gives

                             Particpants #, Last Name, individual event total, rank compared to others.

                              

                             What I want to do is add the ranks from each event (only baskets, walls, and hurdles) to get a 'total rank' value.

                        • 9. Re: Help with summing records from multiple portals
                          philmodjunk

                               A screen shot of the relevant portion of Manage | Database | Relationships with commentary identifying which "boxes" represent the layout and which your portals would be helpful.

                               I think you have three portals to Participation Event Stats placed on a layout based on Event Profile, but would prefer not to guess on that.

                               Are you using portal filter expressions to limit basket stats to the first portal, Walls to the second and Hurdles to the third?

                               You do not need, btw, to put participant names into more than one table.

                          • 10. Re: Help with summing records from multiple portals
                            MichaelPalma

                                 Being new to FMPro I'm not totally sure of the lingo, but for example the portal is built like this:

                                 Baskets pulls from the profile page, which is a calculation of participant event records, of which there may be several. The portals are pulling information from both 

                                 These are on the Portal

                                 Field Name=: Participant Profile:ParticipantNumber

                                 Field Name: Participant Profile:Baskets = Sum ( Participant Event Stats::Baskets )

                                 Field Name: Participant Profile:RankBaskets

                            RankBaskets is determined by your formula on ranking I got from another thread.

                                  

                                 Let ( [ PrevRank = GetNthRecord ( RankBaskets ; If ( Get ( RecordNumber ) = 1 ; 0 ; Get ( RecordNumber ) - 1 ) ) ;
                                           PrevScore = GetNthRecord ( Baskets; If ( Get ( RecordNumber ) = 1 ; 0 ; Get ( RecordNumber ) - 1 )  ) ] ;
                                           If ( PrevScore ≠ Baskets ; Get ( RecordNumber ) ; PrevRank )
                                       )
                                  
                                 Each event will be pulled the same way, simply replacing Baskets with whatever is needed, with the result being adding the RankBaskets + Rank Walls + Rank Hurdles (etc) = Total Rank Value.
                            • 11. Re: Help with summing records from multiple portals
                              philmodjunk

                                   Just spent 20 minutes posting an answer only to see the forum software lose the entire post...angry

                                   From what you have posted, it appears that if you open Layout Setup.. for this layout, you will find "Event Profile" listed in the "Show Records From" drop down. And if you double click one of these portals while in layout mode, Portal Setup... will appear and "Participant Profile" will be shown in the "show related records from" drop down. I would also guess that you have specified a sort order for each portal.

                                   If this is not the case the rest of what I am about to post (again!) will need modification.

                                   The main problem with the ranking calculation is that it is an unstored calculation that is dependent on the current sort order of records. Change the ordere and the fields recalculate. This creates a major problem for your calculation because the sort order for each event must be different in order for the ranks to be computed correctly. Note that "Bubba" shows in Row 2 of the Baskets portal and in Row 4 of the Walls portal. What we will need to do is capture the rank for each event in a number field so that this value will not change when the records are sorted into different orders. We can use a script to do that.

                                   A secondary issue is that you have separate fields for each event for their total score and ranking. If you ever change the events, you'll have to redesign the Participant Profile table accordingly. I recommend the following design changes:

                                   Open Manage | Database | Fields and define a new number field, ParticipantID as an auto-entered serial number field. Presumably, participant number represents the number printed out and attached to each participant and you likely will use the same numbers for different EVENTS. (events are baskets, walls, hurdles. EVENTS are "spring field day" and other such...)

                                   Add a new table and modify your relationships to get:

                                   EVENT Profile-----<Participant Profile-------<participant events-----<Participant event stats

                                   EVENT Profile::__pkEventProfileID = Participant Profile::_fkEventProfileID
                                   Participant Profile::__pkParticipantID = participant events::_fkParticipantID
                                   Participant events::__pkPEventID = Participant event stats::_fkPEventID

                                   If my notation is unfamiliar, see: Common Forum Relationship and Field Notations Explained

                                   In Participant Events, add three fields:

                                   cEventScore--> define as a calculation to compute the total score for a given envent for a given participant.
                                   Rank--> number field
                                   eventName-->text field for identifying the event (baskets, walls, hurdles...)

                                   Filtered portals to participant events that filter for one specific event can be placed on your Event Profile based layout to show the portals in your examples.

                                   Participant events::eventName = "Baskets"

                                   would be an example of such a portal filter.

                                   See if that all makes sense and is something that you can create before I describe a script for ranking participants for a specified participant event.

                              • 12. Re: Help with summing records from multiple portals
                                MichaelPalma

                                     Ok Phil, I think I got it. I included a screenshot of my Relationships box to show where I am now. Thanks again for your time, and yes this forum seems to be really slow at times and unreachable at others.

                                • 13. Re: Help with summing records from multiple portals
                                  philmodjunk

                                       In Participant Profile, you can define this calculation field:

                                       Sum ( Participant Event::Rank )

                                       to compute the total score for that participant.

                                       A script for computing the rankings for one type of participant event:

                                       If [Not IsEmpty ( Participant Events::__pkEventID ) ]
                                          Freeze Window
                                          Go To Related Record [Show only related records; From table: Participant Events ; Using layout: "Participant Events" (Participant Events) ]
                                          Enter Find Mode [] ---> clear the pause check box
                                          Set Field [Participant Event::EventName ; "baskets"]
                                          Set Error Capture [on]
                                          Constrain Found Set []
                                          Sort [no dialog ; Restore] ---> Sort in descending order by Score
                                          Replace Field Contents [no dialog; Participant Event::Rank ; //Put rank calculation here]
                                          Go to Layout [Original Layout]
                                       End If

                                       Note that this scritp is identical for each participant event except for the text used in the set field step. It's possible to pass this text as a script parameter and then this one script can compute the rankings for any given participant event.

                                  • 14. Re: Help with summing records from multiple portals
                                    MichaelPalma

                                         I will try this out when I get home. I had one questions about your instructions.

                                         You typed
                                    Replace Field Contents [no dialog; Participant Event::Rank ; //Put rank calculation here]

                                    Is the rank calulation you mentioned the same one I used from you on another thread?

                                          

                                    TIA

                                    1 2 Previous Next