Price export and import

Donate to this project

Submitted by tanderson on Sun, 24/02/2013 - 15:25

Development Project Status: Completed

Total cost estimate (ex-Tax):

$3490

Due date for completion of this stage:

10/03/2013

Release:

1.7

Current Percentage Funded:

100.00%

Project description:

This project adds support for importing and exporting prices as CSV files.

This enables practices to apply complex pricing strategies in Excel or OpenOffice, and import the changes to OpenVPMS.

It adds two buttons to the Products|Information workspace, namely "Export" and "Import".

Export

When exporting, products may be selected using their:

archetype, e.g. "Medication"
name, e.g. starting with "Acepromazine*"
product type, e.g. "Medical"
classification e.g. "Vaccination"
target species, e.g. "Canine"

Product prices may be selected by:

the latest price
all prices (used to export the pricing history for product)
date range (selects prices active for a particular date range)

The export CSV file will include the following details:

product identifier
product name
product printed name
fixed price
unit price
fixed price start date
fixed price end date
unit price start date
unit price end date

Import

When importing prices:

all fields except the product identifier and product name may be updated. This reduces the likelihood of errors
the existing fixed/unit price end dates will be set to that of the new price start date - 1.
start/end dates that overlap existing start/end dates will be rejected
the same product may be listed multiple times with different price start and end dates. This can be used to:
- import a price history
- support temporary price changes

Update Review

When applying a price update, the workspace will prompt the user to review changes before committing them. This would:

display the matching products
display their previous and new prices. If multiple prices for a product are included, these would be displayed
display the date range that the prices apply to

JIRA: OVPMS-1354

Comments

Re: Bulk price update

Submitted by pstadler (not verified) on Mon, 25/02/2013 - 17:06.

As discussed here http://www.openvpms.org/forum/what-do-you-want-see-next-release-openvpms#comment-5459, our preferred method is CSV upload based. We perform price calculations that are a little more complex based on market demand, competitive data, varied cost changes, etc. After that, we apply custom rounding rules. We also have a price normalization project underway for bloodwork where we're transitioning an entire set of products to a standardized pricing formula, but it will take several more years before that's complete. So we need a way to bring into the system a long list of arbitrary numbers.

The solution above allows batch changes to sets, but doesn't allow for bulk change using arbitrary pricing data. The benefit to the above solution to us would be simply that we wouldn't need to go in and out of each product to change the price. In theory, we could simply search for all products and manually type them all in. That's something, but still a lot more work than uploading a pricing data file.

Cheers, Paul

Re: Bulk price update

Submitted by tanderson on Tue, 26/02/2013 - 09:13.

I've updated the project to include CSV import/export. In your original comments you mention that you want to be able to update the product description; do you mean the printed name node?

-Tim

Re: Bulk price update

Submitted by pstadler (not verified) on Tue, 26/02/2013 - 23:34.

Hi Tim,

I was thinking of the name and description fields in the entities table. I think this maps through to 'Name' and 'Printed Name' on the UI correct?

I think the way you've specified it is fine -- where the name must be the same to avoid problems in import. Perhaps it should be harder to change the name of a product. We did have a situation where we would have appreciated it though. We decided to append the supplier product code to the end of all outside bloodwork product names. We wanted to do that to make sure everyone in the office knew exactly which product it was that was being sold. It was only around 20 product names that we needed to edit however, so not a big deal.

I'd be interested to see how others feel about this one, and whether or not it's useful to allow mass editing of product names through this functionality. Frankly, it's not something that could be easily (I imagine) later on demand if it were bulit the "safe" way first.

Cheers, Paul

Re: Bulk price update

Submitted by tanderson on Wed, 27/02/2013 - 12:49.

The description field isn't used as such. For the products that may be imported (i.e. product.medication, product.service, product.merchandise, product.priceTemplate), the description just duplicates the name.

The Printed Name is a separate node. This could be updated.

Alternatively, a warning could be displayed if a product name doesn't match that being updated, to give you the option to reject or edit the update.

-Tim

Re: Bulk price update

Submitted by pstadler (not verified) on Sat, 02/03/2013 - 06:37.

Okay, thanks Tim.

Yes, I think it would be good to allow updates to the printed name node then. I guess the functionality to allow updates to the product name w/ a warning then accept or reject I would treat as a "would-be-nice" feature. I wouldn't expect name updates to occur with the volume that price updates do, and so I don't think it's absolutely required here.

Cheers, Paul

Re: Price export and import

Submitted by ASAP Laboratories (not verified) on Thu, 13/06/2013 - 14:01.

We would like to be able to offer our clients the abilty to upload a CSV file when we update our prices. Is there an interest for this? if so, we'd be happy to discuss development costs.

Re: Price export and import

Submitted by tanderson on Sat, 06/07/2013 - 18:15.

I'd actually like this to be done as an extension to ESCI, using the UBL Catalogue document.

This would enable:

OpenVPMS to fetch a supplier's catalogue on demand
suppliers to notify clients of price changes by posting the catalogue to a clients ESCI Inbox.

-Tim

Re: Price export and import

Submitted by EastsideVetEmerg on Sun, 14/07/2013 - 09:00.

We will fund 10% of this project and encourage others to contribute even a little to this important project.

Think about how much time would be saved by being able to apply bulk price updates!!!even if you donate $200 even the smallest practice would save much more than this the first time a price update was required.

Re: Price export and import

Submitted by PeterG on Fri, 19/07/2013 - 23:25.

A member has pledged $1,500 towards this project so as Adrian says, it just needs all of you who wish to make life easier for yourselves when updating prices to pitch in a small amount to make this happen.

Re: Price export and import

Submitted by activet on Fri, 26/07/2013 - 18:27.

I'm happy to contribute 5% to this project.

I operate as a single vet clinic and it's still a start up business being here for 2 years only and yet to make a decent profit to pay myself adequately. But I aim to contribute to at least 2-3 OV projects each year. I believe this is the spirit of open source software.

So for users out there who receive and read forum digests and had never contributed before, please show your support by donating to projects to help develop OpenVPMS and make it even better sooner! Please!

Kind regards,

Anthony (ActiVet)

Kind regards,

Anthony (ActiVet)