1 2 Previous Next 16 Replies Latest reply on May 14, 2013 8:56 AM by philmodjunk

    Adding up Portal Rows.

    MikeProcopio

      Title

      Adding up Portal Rows.

      Post

           I have an Basketball Scouting Database. 

           I have a player table as well as a team table. On the team table I have a roster of players and what they make for salary. I enter the salary on the Player Table  year by year from 2013-2017. I have a calc field that adds up how much money is remaining on the contract. On the team table I have the total salary of the team year by year.

           Here is where it gets dicey. In the NBA there is something called cap holds. What a cap hold is after the last year of the contract a number which is 1.5 times the previous year's salary is put on the following year for the team and it is a number that needs to be added. THe thing is it is a fictional number as the team can cut this player and not be owed this money until he signs a contract with the team or is released. I can just put the number on each player's salary the only problem is then it will be added in the money left ont heir contract when it isn't real.

           Any recommendations for this?

        • 1. Re: Adding up Portal Rows.
          philmodjunk

               Can you take another cut at explaining that one? I can't quite follow how that math is supposed to work.

               What changes take place in your database when the player is cut, then signs a contract or is released....?

               1.5 times the team total is simple, but you'll need some kind of status field on the player records that excludes their salary from the total when that is needed to get the correct value.

               That might require a calculation field in the player table something vaguely like this:

               If (PlayerStatus = "Playing" ; SalaryAmount )

               Then you compute the total of this calcualtion instead of SalaryAmount. That way, you can drop a player's salary out of the total used to compute this figure by changing the value of PlayerStatus.

          • 2. Re: Adding up Portal Rows.
            MikeProcopio

                 Phil,

                 OK so the cap hold doesnt need to be figured out by the solution.

                 I have each year salary in a different field so 2012 Salary, 2013 Salry, etc all have their own fields connected to the player table.

                 THe team then lists all of the players etc and I have a salary sheet section that has all the players on the team and the years and what they make in each particular year. 

                 I have a calculation that takes each year and adds it up for the team total salary for each year. I also have one that adds up the money remaining on each player's contract.

                 THe salary Cap is 58 million dollars for each team. FOr example this summer if a team had 15 players this season but on July 1 12 players will become free agents. THe 3 players they have make a combined $15 million dollars, but if the other 12 hav cap holds of $3 million dollars each that is $51 million in total salary on the books until they decide to sign or cut their free agents.

                 The way I have it set up is even when a player's contract ends on say July 1 and they become a free agent, until they sign with another team, the same team, or gets released by the team I still have them on the same team's roster and salary books. Is there a way on the player table to make a cap hold field and put that number in there for each player. Have another field that is made to say either yes/no if a current year cap hold is in effect. Have a Portal on the team table that is filtered by players with Yes selected on the current year cap hold and add only those player's cap hold to the total team salary.

                  

                 Does that make sense??

                  

                  

                 Michael Jordan  

            • 3. Re: Adding up Portal Rows.
              MikeProcopio

                   What I was going to do is just add the cap hold to the payer's salary and put it it highlighted numbers which is fine, but a couple of things happen there. FIrst that number gets added to the player's total salary which I dont ant because then when someone asks me what is left on a specific player's deal I'd have to subtract that highlighted number. Also that number is added to the team's total salary. What I want is for example in the column above in 2012 there is a number 64 million and change well I would want that number under it I ould want a field that calculates how much under or over the $58 million dollar salary cap and for 2013 I would want that cap number. In 2013 it has the cap holds plus salary I would want that number underneath the field that calculates how much under or over the slary cap that the team has so essentially3 fields under neath the individual player's salary.

              • 4. Re: Adding up Portal Rows.
                MikeProcopio

                     It sounds a little confusing I'm jut not sure the best way to go about it 

                • 5. Re: Adding up Portal Rows.
                  MikeProcopio

                       Phil is there a way to set it where you can add a field only if another feild equals a specific value?

                  • 6. Re: Adding up Portal Rows.
                    philmodjunk
                         

                              Phil is there a way to set it where you can add a field only if another feild equals a specific value?

                         If you go back to the last post, that was what I was describing.

                         But I wouldn't set up a separate field for each year's salary--that requires adding a new field and updating calculation fields with every new year. I'd set up a table of related records for yearly salary data.

                    • 7. Re: Adding up Portal Rows.
                      MikeProcopio

                           So you would setup a table for salary connect it to the player table and have feilds for salary and year? Can I then connect the player table with team and use the salary data from the salary table?? Not sure if this is what you meant

                      • 8. Re: Adding up Portal Rows.
                        MikeProcopio

                             So you would setup a table for salary connect it to the player table and have feilds for salary and year? Can I then connect the player table with team and use the salary data from the salary table?? Not sure if this is what you meant

                        • 9. Re: Adding up Portal Rows.
                          MikeProcopio

                               I used the calculation and it worked. Can I use that calculation and use an "or" to add another statement? like  If(Have a Cap Hold?="Yes" ; Cap or if 2013 Salary >1; 2013 Salary)

                          • 10. Re: Adding up Portal Rows.
                            MikeProcopio

                                 I'm sure you are gone for the weekend, but wanted to ask this question.

                                 If I had a seperate table for salary can I connect it to players which I am sure you can. Then I would have to connect these salaries to the teams.

                                 This gets a little tricky because players have player options or team options througout their contract. So I would then have fields for year, salary, Title(which could be full salary, or player option, team option, cap hold, etc)

                                 For the team layout in which I uploaded can I list the player names and yearly salaries like I did?

                                 Can I set the text color of the salary depending on what the title of that year salary was?

                                 If I add up the columns of this can I set the calculation not to add the total up if the title is a certain word. SO for example add it up if the title is full salary, Player OPtion, Team Option, but NOT if it is a cap Hold?

                                 I would do it even though it is a lot of work just need to see if it makes sense and how to do it etc.

                            • 11. Re: Adding up Portal Rows.
                              philmodjunk

                                   Yes.

                              • 12. Re: Adding up Portal Rows.
                                philmodjunk

                                     Sorry, couldn't resist that last post, but the answer to all your questions over several posts is yes, they are all possible. That doesn't mean that they will be simple but they should all be something that can be set up. Nothing like complex compensation contracts--I'm guessing that each one might be different in some unique way, to make this a challenge, but it doesn't look impossible to do.

                                • 13. Re: Adding up Portal Rows.
                                  MikeProcopio

                                       Haha I see you have jokes!!

                                       There wil be no complex contracts. Basically contracts are Full, Team Option, Player Option, ETO, and Rookie Contract.

                                       The way I am thinking about it as far as Salary having their own table is that I wont be able to have a team salary sheet with all of the years maybe the next 4 laid out in front of me. It seems like I would have to have a portal with only 1 year's contract at a time.

                                        

                                  • 14. Re: Adding up Portal Rows.
                                    philmodjunk

                                         I don't see why you would have such limitations. Maybe you are unaware of the horizontal portal trick and how to set up related records in a 'cross tab' format.

                                         You can put a set of one row portals on your layout, each set, via filters to show the salary data for the current player for a specific year. This then gives you rows and columns of data, one row for each player and in that row you have a column for their name and several columns for different years.

                                    1 2 Previous Next