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

# Calculating paid commissions only based on the date range

### 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

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

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

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.