Start a conversation

How can I set commission type for items in mass?

WARNING: The following assumes you are already familiar with running scripts using Store Operation Administrator. As with any SQL script, you should backup your RMS database beforehand and be prepared to restore the backup if results are not as expected. For that reason we suggest running scripts after business hours when RMS is not being used for anything else.

There are 3 Commission Types. Here are the types and the table values associated with them:
0 = No commission
1 = Base the commission on a rep's schedule
2 = Base the commission on the item's schedule
If the last (type 2), you can also set one of the 4 fields that apply. The name of these fields are:
CommissionAmount, CommissionMaximum, CommissionPercentProfit, and CommissionPercentSale. Percentages are entered as nn.nn. For a value of 10%, input 10.00. Yes, include the decimal values even if they are zeros.

To apply only to specific departments. First find the DepartmentID of the desired department by running this query:

SELECT ID, Name, Code from Department

Jot down the ID value of the desired department.

To set the commission type:

UPDATE item SET CommissionMode = «number above» WHERE DepartmentID = «the ID value from department»

To set Commission Type 2 AND set the schedule, include additional SET clauses separated by commas:

CommissionMode = «number above» ,
CommissionAmount = 0.00 ,
CommissionMaximum = 0.00 ,
CommissionPercentProfit = 0.00,
CommissionPercentSale = 0.00
WHERE DepartmentID = «the ID value from department»

The extra carriage returns above are for clarity. You may use them or leave them out. You do not need to include any fields you do not want to set. You would only set one extra field for Commission Type 2. So, to set the Commission Type to the item's schedule and have that schedule be based on 12.34% of the profit from the item, issue this query:

UPDATE item SET CommissionMode = 2 , CommissionPercentProfit = 12.34 WHERE DepartmentID = «the ID value from department»

Remember to use only one comma between CommissionMode and the other field. Use no comma if you only want to set the Type, and not fill in a schedule value.

Note:  To undo everything and set the Commission Type back to none for all items:

UPDATE item SET CommissionMode = 0

without any WHERE clause or use WHERE clause to update selected data.

Choose files or drag and drop files
Was this article helpful?
  1. Dave J

  2. Posted