How To Update Commission And Rates In Mass

In the event that you renegotiate a new commission rate with your client, or your tax rates for your local area change, you can update your clients and debtors in mass. Several hidden fields on the Client and Debtor forms enable you to access tax and commission rate fields directly within the report writer. You can set commission and tax percentages and their breakdown settings on many accounts at once using a Client or Debtor loop and the @EDIT report writer command. You can also filter the list of accounts using criteria to determine which to include.

Commission Rate Field Codes For Client

The report codes for the fields on the Client's Commission Rates form are:

The report code for the commission breakdown check boxes is: @cl.cro

The code @cl.cro is actually a combination of bit values for all the check boxes on the Commission Rates form. It is easy to figure out the value and then use it in a write back report. How to do this is described later in this document.

Commission Rate Field Codes For Debtor

The report codes for the fields on the Debtor's Commission Rates form are:

The report code for the commission breakdown check boxes is: @de.cro

The code @de.cro is actually a combination of bit values for all the check boxes on the Commission Rates form. It is easy to figure out the value and then use it in a write back report. How to do this is described later in this document.

Tax Rate Field Codes For Client

The report codes for the fields on the Tax form are:

The report code for the tax breakdown check boxes is: @cl.tro

The code @cl.tro is actually a combination of bit values for all the check boxes on the Tax form. It is easy to figure out the value and then use it in a write back report. How to do this is described later in this document.

Tax Rate Field Codes For Debtor

The report codes for the fields on the Tax form are:

The report code for the tax breakdown check boxes is: @de.tro

The code @de.tro is actually a combination of bit values for all the check boxes on the Tax form. It is easy to figure out the value and then use it in a write back report. How to do this is described later in this document.

Commission Rate Field Codes For Transaction

The report codes for the fields on the Commission Rates form are:

The report code for the commission breakdown check boxes is: @tr.cro

The code @tr.cro is actually a combination of bit values for all the check boxes on the Commission Rates form. It is easy to figure out the value and then use it in a write back report. How to do this is described later in this document.

Tax Rate Field Codes For Transaction

The report codes for the fields on the Tax form are:

The report code for the tax breakdown check boxes is: @tr.tro

The code @tr.tro is actually a combination of bit values for all the check boxes on the Tax form. It is easy to figure out the value and then use it in a write back report. How to do this is described later in this document.

Accessing The Bit Values For Check Boxes

Before you run your report on a series of clients or debtors, you need to obtain the bit values for the options. Once you know what the values are, you can just do a write back and set them for all your accounts. To do this, go to one debtor and set up the Commission Rates and Tax exactly as you want them to be, switching ON check boxes with a check mark as needed. Then, go off the Debtor form to commit the settings. Then print to screen @de.cro and @de.tro. This will give you two numbers, for example. 3843 and 290 or 16386 and 258. The numbers won't seem to make any logical sense, but actually they are a combination of switch values and Collect! knows exactly what they mean. In your debtor loop, you can write these values and this will set the same switches for each of your debtors.

You can do the same for the client @cl.cro values. If you are setting the same values at the Client level, you can use the same value that you obtained for @de.cro. They are the same behind the scenes. The same number sets the same switches.

Set Rates And Check Boxes For Clients

To set rates and switch on commission rates and tax settings for each Client, create a write back report as follows.

Whenever you have several fields to edit, you use @SET for every entry except the last one. This registers all the settings and then writes once to the database with the @EDIT line to commit all the settings at once.

After this report runs, you will see that each Client has the same Commission settings and switches. The rates will match the rates you entered in your report for each one of the rate fields. The check boxes that are switched ON will match the Client you used to set this up.

Set Rates And Check Boxes For Debtors

To set rates and switch on commission rates and tax settings for each Debtor, create a write back report as follows.

Set Rates And Check Boxes For Transactions

To set rates and switch ON commission rates and tax settings for each transaction, create a write back report as follows.

For simplicity, you can combine this into a single report with a Start on of Client. All you would need to do is fill in the correct value for the client you are modifying, then open up the Client from the Browse\Clients menu, and run the report.

You will also need to fill in the current values of the fields that you are not modifying to prevent them from being overwritten.

After this report runs, you will see that each debtor has the same Commission and Tax settings and switches. The rates will match the rates you entered in your report for each one of the rate fields. The check boxes that are switched ON will match the Debtor you used to set this up.

SQL Version

Yes, it is possible to run this via SQL. The only part that won't work is Transactions. The query will update the Commission and Tax rate values, but it will not recalc the Transaction. This part needs to be done via @EDIT in a report loop.

You can use SQL to store the applicable Row ID's, return them to the Report Writer, then use @WHILE to loop through and update the Transactions.