1 2 3 Previous Next 34 Replies Latest reply on Sep 13, 2011 4:49 PM by MikeProcopio

    Sub Summary Numbering



      Sub Summary Numbering


      I have a sub summary part on my layout and want to take the top 10 in each category. I have a basketball database and have my sub summary report for all five positions point guard, shooting guard, small forward, power forward, and center. I want to list the top ten in each based on a mumber rating that I created.

      I was wondering how to write the script to have a report with all five positions with ten records for each. I was thinking to do the find and constrain the find.


      ANy suggestions??

        • 1. Re: Sub Summary Numbering

          You could use a looping script that omits all records in each group that rank after the cut off point.

          You could also use a portal with just 10 rows, sorted by the ranking field or the data it's based on to limit the list to your top 10. (You'd use a related table for your layout where you have one record for each position.)

          • 2. Re: Sub Summary Numbering

             I tried some some scripts but all it did was omit after 10 and only got one group. Do yo have a script in mind??

            • 3. Re: Sub Summary Numbering

              I'd use the portal, myself as this requires little or no scripting to set up your report.

              You'd need Several nested loops, the outer loop to step through each position and the inner loops to selectively omit records one at a time after the 10th record for that group--with care taken to avoid issues that occur when you omit the last record in the last group.

              Sort [ Restore ; no dialog ] //sort by position, then by ranking data so that first 10 records in each position are the top 10.
              Go to record/request/page [first]
              Loop //loop through the positions
                Set variable [$Position ; Value: YourTable::PositionField ]
                Set Variable [$Keep ; value: 0 ]
                   #keep 10 best
                   Set Variable [$Keep ; Value: $Keep + 1]
                   Exit Loop If [$Keep = 11]
                   Go To record [Next ; exit after last]
                End Loop
                   #Omit any remaining for this position
                   Exit Loop if [$Position ≠ YourTable::PositionField ]
                   Exit Loop If [ Get ( RecordNumber ) = Get ( FoundCount ) //exit if last record in found set is reached ]
                   Omit Record
                End Loop
                Exit Loop If [ Get ( RecordNumber ) = Get ( FoundCount ) //exit if last record in found set is reached ]
              End Loop

              I haven't exhaustively tested this script. This is the type of script where it's easy for the script to be off by one or to produce different results for the last group of records so please test it carefully.

              • 4. Re: Sub Summary Numbering

                 OK so let me fully explain and tell me what you think,


                I have a scouting database that I am developing. THere are 40 or so events that have field names. Basically if a player will attend an event you go tot he field and place a yes.

                So the event in question lets call it tournament A has its on field for yes and no answer to see if they will participate. I have a TOurnament A Rating field which I can then produce rankings of players based on a 1-10 ranking. There might be 400 players participating in tournament A. Players gave different positions in which they play. In basketball there are 5. So I do have a field that has position listed. YOur options there are point guard, shooting guard, small forward, power forward, and center.

                Originally I made a script doing a find for {TOurnament A:Yes} and sorted by position and TOurnament A Rating.

                If I want to make a portal for this how would you do it?? That would mean I'd have to make a table for every event??


                I want to list the top 10 players in each event by position so a total of 50.

                • 5. Re: Sub Summary Numbering

                  THere are 40 or so events that have field names.

                  That's a major design issue right there, each event should be it's own record in a related table and not have its own field within the same record...

                  The table where you record the player's rating and position would be the same table, but with fields to identify the tournament and the position played as well as the ID of the player.

                  Then you set up a Positions table with one record for each of the 5 basketball positions.

                  Positions::PositionName = Ratings::PositionName.

                  Then if your set up your portal to Ratings on a Positions layout, with a filter that filters all Ratings except those for a specific tournament event, sort them by the rating in descending order with exactly 10 portal rows, a portal on the Positions layout will list only the top 10 players for each position for a specified tournament.

                  A filter expression such as:

                  Globals::gTournamentID = Ratings::TournamentID

                  where Globals::gTournamentID is a drop down list formatted field with global storage,

                  can be used to select the ratings/rankings for a specific tournament on this layout.

                  This script, triggered from a script trigger on this global field:

                  Commit Record
                  Refresh Window [flush cached join results]

                  Would update the portals to show the proper ratings for a newly selected tournament in this drop down.

                  • 6. Re: Sub Summary Numbering

                     The reason for each event having its own field for yes/no is because not every player participates in all the events. I wanted to easily identify the players that participated in an event. There are different reports that are distributed to clients. It may be an overall rating for players and reports for them, but there are also reports for individual events. In each layout for each event I have buttons directing you to the top 20 players overall in a particullar event, a button giving every player's info and report on them, as well as a report on the top 10 players at each position.


                    I know it seems odd to do, and I wish there was an easier wat to get it done which I am sure that there is. I'[m not very strong in relationships and sure theres an easy way to produce the info that I need.

                    • 7. Re: Sub Summary Numbering

                      THere is definitely a better way to track player participation. Consider what you do next year if you find you are working with a completely different schedule of tournaments. Will you then add/change your fields and otherwise redesign your database to work with the new schedule of events?

                      You would appear to need these tables:


                      Athletes::AthleteID = Event_Athlete::AthleteID
                      Events::EventID = Event_Athlete::EventID

                      A portal to Event_Athlete on a Athletes layout can be used to list each event in which that Athlete participates. A portal to Event_Athlete on the Events layout can list all Athletes that participate in that tournament event.

                      • 8. Re: Sub Summary Numbering

                         Phil you've been 1,000 % right with me from the start so I'll give it a try. I created another table and called it events. SO you are saying that I can name the events and assign an ID number to them, then pull the data from it and put it in a layout with the event name and participants?? OK I'll try it. Now the only thing is I'd like to be able to navigate to different layouts for different rankings for an event  I can still do this thorugh making portals??  Also How would I be able to have an active listings of events and ID #'s when inputing event ID#'s on tables?? Is there a way to list the event names and id#s in a value list??

                        • 9. Re: Sub Summary Numbering

                           What fields do I give the event table??

                          Name, Event ID, Location, Date. I'll link the Event ID on my original Table. Are there any other feilds that I should be making??

                          • 10. Re: Sub Summary Numbering

                            Is there a way to list the event names and id#s in a value list?

                            Yes, and there are alternatives to consider should you find this list of possible events is too long to conveniently work with in drop down list or pop up menu.

                            You can define a value list with the specify field option to list ID's from this new table in field 1 and the tournament names in field 2. Your value list can list both fields or you can specify that only the second field-the name of the tournament appear in the list. With the pop up menu option, this results in a field that stores the ID number, but displays the tournament name. With the drop down list option, you'd need to add the field from the tournament table to be placed next to the drop down to display the name after you exit the drop down. With any of these options, it's a good idea to specify that the tournament name field store unique values. If necessary, you may want to combine tournament names and additional data such as the year in one field in order to keep names of recurring tournaments unique. (If you enter the field and see the same tournament name twice, there's no way for the user to know which one represents this year's tournament and which is last year's tournament.)

                            Once you get the basic value list working, you can investigate ways to use conditional value lists to restrict the number of tournaments that appear in the list (You could limit them to this season's tournaments only, for example) to keep the list shorter to work with.

                            Now the only thing is I'd like to be able to navigate to different layouts for different rankings for an event  I can still do this thorugh making portals?

                            Yes that's all possible, but the details depend on knowing the exact structure of your tables and how they are related, this is something that I don't have a full picture of at this time so I can't go into specifics much.

                            • 11. Re: Sub Summary Numbering

                              So do I have to go to the event table and enter a player inot the event table to show their participation?? It might be tought to get player ID#s and then enter them. The one good thing is all I have to do now is go to a player and select yes for an event and that player is linked to it. Is there something as easy for setting the portal up??


                              I'm just trying to get a picture of it in my mind of how to construct this. 

                              • 12. Re: Sub Summary Numbering

                                Players would be entered into the Event_Athlete join table to show participation in a given tournament. With the correct value list setup, you can add athletes to an event in a portal to this table without having to know the ID number of a single athlete. You use the value list I described earlier so that you can select an athlete by name, but the value list actually enters the matching ID number. Please note that athletes with identical names (Say two athletes named "John Smith" will create problems for you unless you either modify the name of the second John Smith (John Smith II) or combine the name field data with other data in the Athletes table such as the name of their team or school (If this is amateur BBall...).

                                Here's a demo file you can take apart and look at. It matches "companies" to "contracts" ina many to many relationship just like I am suggesting you match Athletes to Events:  http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

                                • 13. Re: Sub Summary Numbering

                                   I sent you the file of my empty database for you to get an idea of the relationships. I created a table for events and made a relationship with eventid to the event table and main table. Having issues trying to enter data but will continue to try. I can enter the info into the event table but trying to put data into those indenticle fields on the player Table I am having an issue with

                                  • 14. Re: Sub Summary Numbering

                                     I downloaded the file you sent , see some of the similiar traits but still cant get my tables to mesh. I thought I could just put 10-11 copies of the event ID and name on each of the player's individual pages to then select events by event ID so I can select their participation on the player side where it will be easier to enter them in. THe only issue with doing it in the portal end is the reports wont be as clean as they would be if you script this, although scripting seems like a total suicide mission.



                                    1 2 3 Previous Next