Quick Jump Menu
Info Ask a Question
Advanced Search Search the Knowledgebase - We RECOMMEND the ADVANCED SEARCH option
Price List Update
Author: Reference Number: AA-00737 Views: 803 Created: 11/09/2013 10:01 Last Updated: 21/12/2017 15:58 0 Rating/ Voters


Price Grid Update


This tool allows users to set up a grid of amendments that will be made to pricing grids.  

This grid can optionally be saved for future re-use.  

When the ‘Generate’ button is clicked, the system generates 2 SQL script files: one to update the relevant pricing grid lines with the new rates, and one to restore them back to their original rates.

The tool is found on the Tools menu:

The initial screen will look like this:


1: Before entering anything in the grid, you need to decide what you want to do:

  • If you don’t intend to save the grid, just leave the ‘Profile Name’ drop-down blank.
  • If you want to call up an existing grid, use the ‘Profile Name’ drop-down to select it – once selected, the grid is automatically loaded.
  • If you want to enter a new grid, type a new name in the ‘Profile Name’ drop-down – once you move off the drop-down, the grid is automatically cleared.
  • Enter the amendment details within the grid, ensuring that you supply an equipment code (item, subgroup or group) plus an IType, along with the adjustments to whichever of the 3 rates you need. Note that leaving a rate cell blank will mean that those particular rates won’t be affected in the pricing grids.

The rates can be amended in 3 different ways:

  • Adjust by positive or negative amount (e.g., add £1.00 to the existing rate)
  • Adjust by positive or negative percentage (e.g., subtract 2% from the existing rate)
  • Overwrite the rate (e.g., change the existing rate to £12.99)
Once you’ve entered the details within the grid, you can optionally save it by clicking the ‘Save’ button (provided that the ‘Profile Name’ is not blank).  
For reference, this information gets stored in the dedicated grid_update table in the MCS-rm database.

2: Provide a filename for the SQL scripts that will be generated within the ‘Filename’ textbox by either typing it directly or by using the ‘...’ button.

  • Once a filename has been provided, the ‘Generate’ button is enabled. When clicked, the 2 SQL files are created:
    • The first is named using the supplied filename, and will alter matching lines on all price grids in the database, with the exception of those whose 'Exclude from Price List Update' checkbox (in the 'Header' tab of the 'Maintain Pricing Lists' form) is ticked.
    • The second is named using the supplied filename but also incorporating a ‘_recover’ suffix; this will undo the changes made by the first SQL file.

This means that you can generate the scripts in advance and apply them at the precise moment you want to switchover to the new pricing.

Points to Note

  • The amendments only work to 2 decimal places, whereas the grids can handle 4 decimal places.
  • It’s not compatible with tiered rates.
  • It can only amend Rate 1, Rate 2 and Rate 3 – it can’t amend the meter rate, CDW, excess, meter allowance, DW1%, discount percentage or Minimum Rate 1 figures in the pricing grids.
  • When you specify an item/subgroup/group to change, the system treats it literally – i.e., if you want to change the prices of subgroup ‘LOADERS’, the generated script will only match against price grid lines for subgroup ‘LOADERS’; if you had any price grid lines against item codes belonging to the subgroup, they won’t be affected.
  • The ‘recover’ script is effectively backing up the pricing at the time of generating the script – so you need to remember that manual changes to price grids can be lost if they are made between running the update script and running the recover script.