Pricing Updates

Prices in OpenVPMS can be updated:

• by editing products individually
• automatically when the supplier's price changes if the Auto Price Update option is set for the product/supplier
• via Export Prices and Import Prices

To update a large set of prices, the latter is the preferred approach. The process is as follows:

1. on the Products|Information screen, press the Export button to display the Export Prices screen
2. fill in the parameters to select the products to export and press the Export button
3. this will export the data in CSV format in a file named 'products-yyyy-mm-dd.csv' (where yyyy-mm-dd gives the current date)
4. use a spreadsheet program to make the required changes and save the new data in CSV format.
5. on the Products|Information screen, press the Import button to display the Upload window and upload the new data file
6. the Import Prices screen will then be displayed showing the products be updated and any errors.  If there are no errors, press the OK button to apply the updates. If there are any errors, these must be corrected in the spreadsheet before re-importing it.

Note that the format of the CSV file being imported must match that exported. You cannot have extra columns (such as 'Old Fixed Price') and the column headers must match.

A relatively simple way to achieve this is to copy all the data to a second sheet (called say Sheet1). On the top sheet you can then replace the original data by formulas where you want to do updates.  Thus inserting the formula:

=IF(Sheet1!E2>0,ROUND(Sheet1!E2*1.2,2),"")

in cell E2 of the top sheet and then copying this to all cells below it in the E column will:

• increase all fixed prices by 20%; and
• round them to 2 decimal places

where there is an existing fixed price.

You then save the top sheet in CSV format and import that.

This technique can be used to both create and update prices. To maintain a price history, it is recommended to create new prices.

Creating new Fixed Prices

To create new fixed prices:

1. Clear the Fixed Price Id column contents

Clear the D column, starting in cell D2. This ensures that new fixed prices are created rather than updating existing fixed prices.

2. Change the Fixed Price column

E.g. to increase fixed prices by 50%, add the following to the E2 cell, and copy to the remaining E column cells:

 =IF(Sheet1!E2>0,ROUND(Sheet1!E2*1.5,2),"")

3. Change the Fixed Price Start Date column

The prices should be given a new start date, so that they don't overlap existing fixed prices. The existing fixed prices will have their To Date set to the Fixed Start Date with time 00:00:00. Thus if the new start date is 2/May/17, the old price will apply until midnight on 1/May/17, and the new price will apply from 00:00:00 on 2/May/17.

E.g. to date the prices 30 days from the existing prices. add the following to the H2 cell, and copy to the remaining H column cells.

 =IF(Sheet1!H2>0,Sheet1!H2+30,"") 

The Default Fixed Price column can be used to specify if a fixed price is the default price. This only applies if multiple fixed prices are active for a product at a given time. Valid values are false and true.

Updating Fixed Prices

To update fixed prices, retain the Fixed Price Id column contents, and change the Fixed Price, Fixed Cost, Fixed Price Start Date, Fixed Price End Date and Default Fixed Price columns as required.

Creating new Unit Prices

To create new unit prices:

1. Clear the Unit Price Id column contents

Clear the L column, starting in cell L2. This ensures that new unit prices are created rather than updating existing unit prices.

2. Change the Unit Price column

E.g. to increase unit prices by 50%, add the following to the M2 cell, and copy to the remaining M column cells:

=IF(Sheet1!M2>0,ROUND(Sheet1!M2*1.2,2),"")

3. Change the Unit Price Start Date column

The prices should be given a new start date, so that they don't overlap existing fixed prices. The existing unit prices will have their To Date set to the Fixed Start Date with time 00:00:00. Thus if the new start date is 2/May/17, the old price will apply until midnight on 1/May/17, and the new price will apply from 00:00:00 on 2/May/17.

E.g. to date the prices 30 days from the existing prices. add the following to the P2 cell, and copy to the remaining P column cells.

=IF(Sheet1!P2>0,Sheet1!P2+30,"")

Updating Unit Prices

To update unit prices, retain the Unit Price Id column contents, and change the Unit Price, Unit Cost, Unit Price Start Date, and Unit Price End Date columns as required.

Updating Product Printed Names

To change the Printed Name of a product, enter a value in the appropriate Product Printed Name cell.

Product Price Templates

Exported fixed prices can include prices linked from Product Price Templates if the "Include Linked Prices" option is selected when exporting prices.

Where this occurs, a warning will be displayed in the Notes column. E.g.:

Warning: fixed price is linked from Basic Surgery Fee (1047)

Linked prices cannot be updated via the product that links them; the Product Price Template must be updated instead.

 

Price Groups

If you are changing the price group or adding new prices with a price group, remember that the Price Group is specified by its code, not its actual name.  For example, if its name is say 'Price Group 3' then its code will be PRICE_GROUP_3, and if you edit the price group to change its name to say 'Group 3', its code will remain unchanged.  For this reason it is best to use the Export facility to export some prices with the relevant prices groups so that you can ascertain what the actual codes are.