Any way to enter a fixed value for a commission? Like if it was a $5,000 bonus for the sale, but the the sale total was an amount such that no 2-digit rep rate would give you exactly $5,000.
For example: Line totals $97,570.49. The rate to give you exactly $5,000 would have to be 5.1245%. Since you can only enter 2 decimal places, you choices are:
There is a Commission Amount field for each Rep. Not sure if it is editable or if it would be overwritten by other transactions, but that would be a good place to start.
This in the DB or just on the UI forms?
The field is hidden on the UI and is fully available in the DB.
Jason Woods
http://LinkedIn.com/in/jasoncwoods
I’m seeing RepRate and RepSplit on the OrderDtl and InvcDtl tables, but nothing indicating an amount. Am I barking up the wrong tree?
The fields are SRComm* in both OrderHed and OrderDtl
Okay, I see them in OrderHed, but not OrderDtl (nor OrderRel). I’ll play around with OrderHed.SRCommAmt1.
That SRCommAmt field is just for reporting. Can’t really use it for invoice lines.
But I did do some math to figure out how to calculate the Rep Rate to get a value with the minimal error from my target. It works by using the same rep twice, with two different Splits and Rates.
Calculate the ideal commission rate in % (call it V).
V = DesiredCommission / CommissionableAmount * 100%
V will have more decimals that the 2 allowed by the RepRate fields.
Calculate Rate1 (call it R1).
R1 = Floor(V*100,1)/100
The 100's are because RepRate has two decimal places
Calculate Rate2 (call it R2).
R2 = Ceiling(V*100,1)/100
Calculate Split1 (call it S1)
S1 = Round((R2 - V) * 10000,1)
The 10000's are because we want the 3rd and 4th digits (100 x 100) .
Calculate Split2 (call it S2)
S2 = Round((V - R1) * 10000,1)
The way it works is to find the smallest “whole number” below the ideal value (V), and the next larger “whole number” above V. The Floor and Ceiling functions do this. The difference between these values and the ideal value is an indicator of how far the R1 and R2 values are from the ideal value. The sum of (R2 - V) and (V - R1) is always “1”.
This is used as a weighting factor between the two, and applied as the Split value for each. But since the closer value should have the higher weight we use the distance from V to R2 as the split weight for R1, and vice versa.
examples:
| Example 1 | Example 2 | Example 3 | |
|---|---|---|---|
| Commisionable Amount | $12,150.08 | $12,150.00 | $321,456.78 |
| Desired Commission | $1,500.00 | $1,500.00 | $1,500.00 |
| Ideal Rate | 12.3456 | 12.34567901 | 0.466625716 |
| Ideal Rate (V) | 12.3456 | 12.34567901 | 0.466625716 |
| Rate 1 (R1) | 12.34 | 12.34 | 0.46 |
| Rate 2 (R2) | 12.35 | 12.35 | 0.47 |
| Split 1 (S1) | 44 | 43 | 34 |
| Split2 (S2) | 56 | 57 | 66 |
| S1 Portion of Line Amount | $5,346.03 | $5,224.50 | $109,295.31 |
| S2 Portion of Line Amount | $6,804.04 | $6,925.50 | $212,161.47 |
| Comm at R1 of S1 | $659.70 | $644.70 | $502.76 |
| Comm at R2 of S2 | $840.30 | $855.30 | $997.16 |
| Tot Commission | $1,500.00 | $1,500.00 | $1,499.92 |
| Error | $- | $0.00 | $(0.08) |
You will start to get a difference from the ideal when the commissionable amount and desired commission get very large.