6 Replies Latest reply on Dec 24, 2013 10:34 AM by philmodjunk

    Help with a Summary

    fmpro_novice

      Title

      Help with a Summary

      Post

           In the attached screenshot.jpg, notice the red numbers. Those are entered manually into a field called "number." Then the numbers in the highlighted boxes are a result of a sort and conditional formatting.

           I need to create a field (shown by the black numbers, which I added to the screenshot in Photoshop to show you what I'm trying to do) to be the total of the two red numbers. Can someone tell me what the field should be, then how to make it display the totals?

      screenshot.jpg

        • 1. Re: Help with a Summary
          philmodjunk

               Which "two red numbers" are being added? I can't spot any pattern as to what values are being added to produce the black colored values.

               Is each row here a single record or a sub summary part based sub total?

          • 2. Re: Help with a Summary
            philmodjunk

                 Never mind on the "which two". I see the pattern now, but what kind of field is the highlighted field? How does "Then the numbers in the highlighted boxes are a result of a sort and conditional formatting." affect the value shown in that field?

            • 3. Re: Help with a Summary
              fmpro_novice

                   The highlighted field is the result of a sort that pairs the lowest red number of each player, with the highest red number of each player. It is a team number that is assigned as a result of the sort. The team listed first is team 1, and then each player is assigned A, B, etc. The A or the B is determined by the value of the red number.

                   It goes all the way through the list of records and then delivers "teams" where the total of the red numbers is as close together as possible. This is trying to create "equitable teams" based on each player's "value" which is set by the red numbers.

                   The black numbers are the totals of each team's red numbers.

                    

              • 4. Re: Help with a Summary
                philmodjunk

                     The highlighted field is the result of a sort that pairs the lowest red number of each player, with the highest red number of each player.

                     Am I correct then, that there are more players for each team than the two shown, the "highest" and "lowest" red number means that you are finding the player with the highest number and the player with the lowest number in that team?

                     Set up this self join relationship:

                     Players::TeamID = Players|SameTeam::TeamID

                     then Min ( Players|SameTeam::redNumberField ) + Max ( Players|SameTeam::RedNumberField )

                     will return the sum of the largest and smallest values for a given team.

                     Or:

                     Assuming that you have this data model:

                     Teams----<Players

                     You can define:

                     Min ( Players::redNumberField ) + Max ( Players::RedNumberField )

                     as a calculation field in Teams.

                • 5. Re: Help with a Summary
                  fmpro_novice

                        In this particular model, there are only 2 players per team. There could be other instances where there are 3, 4, 5 and 6 player teams. So, the summary total would need to be the total of all the "players" on each team, not just the lowest number and the highest number.

                  • 6. Re: Help with a Summary
                    philmodjunk

                         then Sum ( Players::RedNumberField ) will return that total.