3 Replies Latest reply on Apr 23, 2012 1:45 PM by philmodjunk

    Calculating paid commissions only based on the date range

    nickodm

      Title

      Calculating paid commissions only based on the date range

      Post

      Here's the scenario:

      Sales Rep A sells two jobs. His commission on job 1 is a total of $200. He gets paid $100 on 4/1/2012 and the other $100 on 4/15/2012. His commission on job 2 is a total of $1000. He gets paid $200 on 4/1 and the rest of $800 on 4/15.

      So if you were to pull a report for the rep it would read as follows:

       

      JOB FIRST HALF DATE FIRST AMOUNT

      SECOND HALF DATE

      SECOND AMT TOTAL
      Job 1 4/1/2012 $100 4/15/2012 $100 $200
      Job 2 4/1/2012 $200 4/15/2012 $800 $1000

       

      So here's the issue:

      How do I find out what the rep has been paid for a specific date range? Basically if I was looking for what the rep was paid from 4/1 to 4/14 the answer is $300. And if I was looking for what he was paid from 4/15 to 4/30 the answer is $900. And of course if I was looking for the entire month of April the total is $1200. 

      I have created the basic script to query the dates of both the first and the second and give me the results. The issue I am having is that the totals in the report are calculating the amounts that are there even if they are outside the date ranges I selected.

       

      PS: Also, the answer is not simply to check the first half date for the first half of the month and the second half for dates of 15th on since the rep can sell jobs at various times and get paid the different halfs on same pay periods. 

       

        • 1. Re: Calculating paid commissions only based on the date range
          philmodjunk

          How have you structured your table?

          What you show could be 4 records or just two records.

          If it's only one record with multiple fields, we have problems as that will really complicate this process. 4 Separate records is the simplest structure for your reporting, but I can also see where that may require a bit of extra effort in computing the comission payments in the first place.

          Are the payment dates always on the 1st and 15th of the month?

          • 2. Re: Calculating paid commissions only based on the date range
            nickodm

            Unfortunately it's one record with multiple fields: 

            Table - Contracts

            Fields used: JOBID (obviously), TotalCommission1stDate, TotalCommission1stAmt, TotalCommission2ndDate, TotalCommission2ndAmt, TotalCommission

            And the payment dates are not always the 1st and the 15th since we pay weekly. I was just putting them there for an example.

            The script I have queries the $DateRange of TotalCommission1stDate and then does a NewRecord/Request and queries the $DateRange of TotalCommission2ndDate. 

            I just don't know where to go from there since adding a sub-total element in my layout will just total up all the amounts shown which can be from outside the $DateRange.

            • 3. Re: Calculating paid commissions only based on the date range
              philmodjunk

              I'd use a related table for the commission payments so that you have one record for each payment for a given job record.

              Jobs::JobID = Commissions::JobID

              Then you can set up summary fields in Commissions that compute total payments and these can be used to compute totals for a given month or any range of dates needed.

              A portal to Commissions on your Jobs layout can be used to record commission payments by hand. And it's also possible to set up a script such that you enter the contract amount and a date and the script computes the commission payments/dates and logs them in the commissions table at the click of a button.