Export Prices
The Export Prices window is used to export OpenVPMS product prices to a CSV (comma separated values) file.
The exported prices can be manipulated in a spreadsheet program such as Microsoft Excel or OpenOffice Calc, and imported using Price Import.
Note that prior to OpenVPMS 1.9, exported and imported prices were tax-inclusive. Now all prices are tax-exclusive.
Prices can be selected for export using the following criteria:
- Type - one of Medication, Merchandise, Product Price Template, Service, or All. Note that you cannot export Templates - if you want to do some editing of these, you have to do them one by one.
- Search - enables the products to be queried by name or identity
- Search Identities - if selected, products are queried by identity
- Include Deactivated - if selected, inactive products are included in the search
- Product Type - the type of the product. E.g "Desexing", "Euthanasia".
- Species - queries products by species
- Income Type - queries products by income type. E.g. "Medication", "Professional"
- Product Group - queries products by product group. E.g "Fees", "Grooming"
- Prices - one of:
- Current - exports the fixed and unit prices for a product that are active at the current time
- All - exports all fixed and unit prices for a product. This may be used to export the price history
- Range - exports all prices for a product active within the range specified by the From and To fields
- Pricing Group - exports prices by Pricing Group. One of:
- All - export all prices, regardless of Pricing Group
- None - export prices with no Pricing Group
- Group Name - export prices with the specified Pricing Group, or no Pricing Group
- From - used to set the price From Date, when querying prices in a range
- To - used to set the price To Date, when querying prices in a range
- Include Linked Prices - if selected, prices linked from Product Price Templates will be included in the exported data
Export File Format
The exported data is a comma-separated-values file, containing the following columns:
| Column | Required | Type | Description |
|---|---|---|---|
| Product Id | Yes | Integer | The product identifier. |
| Product Name | Yes | String | The product name. |
| Product Printed Name | No | String | The product printed name. If different to that stored, updates the product's Printed Name. |
| Fixed Price Id | No | Integer | The fixed price identifier, used when updating an existing fixed price. |
| Fixed Price | No | Decimal | The tax-exclusive fixed price. If unspecified, no fixed price will be created/updated. |
| Fixed Cost | No | Decimal | The fixed cost. Only used if a fixed price is specified. Defaults to 0.0 if unset. |
| Fixed Price Max Discount | No | Decimal | The fixed price maximum discount, expressed as a percentage. Required if a fixed price is specified, optional otherwise. |
| Fixed Price Start Date | No | Date | The date the fixed price starts on. Required if a fixed price is specified, optional otherwise. |
| Fixed Price End Date | No | Date | The date the fixed price ends on. If unspecified, the fixed price will have no end date. |
| Default Fixed Price | No | Boolean (true/false) | Indicates if a Fixed Price is the default price for the date range. Only applicable if there are multiple fixed prices. |
| Fixed Price Groups | No | String | A space separated list of Pricing Group codes that are associated with the fixed price. |
| Unit Price Id | No | Integer | The unit price identifier, used when updating an existing unit price. |
| Unit Price | No | Decimal | The tax-exclusive unit price. If unspecified, no unit price will be created/updated. |
| Unit Cost | No | Decimal | The unit cost. Only used if a unit price is specified. Defaults to 0.0 if unset. |
| Unit Price Max Discount | No | Decimal | The unit price maximum discount, expressed as a percentage. Required if a unit price is specified, optional otherwise. |
| Unit Price Start Date | No | Date | The date the unit price starts on. Required if a unit price is specified, optional otherwise. |
| Unit Price End Date | No | Date | The date the unit price ends on. If unspecified, the unit price will have no end date. |
| Unit Price Groups | No | String | A space separated list of Pricing Group codes that are associated with the unit price. |
| Tax Rate | No | Decimal | The sum of tax rates for the product, expressed as a percentage. This must be valid on import, but is otherwise ignored. |
| Notes | No | String | Price notes. At present, this is used to report if a price is linked from another product. |
Note that the markup and tax-inclusive prices are not exported as they can be derived.
To calculate the markup from the exported columns use:
- Unit Price Markup = (Unit Price / Unit Cost - 1) * 100
- Fixed Price Markup = (Fixed Price / Fixed Cost - 1) * 100
To calculate tax-inclusive prices, use:
- Tax-inclusive Unit Price = Unit Price * (1 + Tax Rate / 100)
- Tax-inclusive Fixed Price = Fixed Price * (1 + Tax Rate / 100)
See Pricing Updates for instructions on manipulating the exported data.
