Price export and import

Donate to this project

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 funding: 

Public pledges can be made to this forum topic or email me directly by clicking here (link only works in the forum).
Development will not commence until fully funded.

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

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Bulk price update

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

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

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

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

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

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

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

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

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

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)

Re: Price export and import

Thanks very much to everyone who has pledged to this project, we now have this really valuable feature 80% funded.

Re: Price export and import

Thanks to some donations by members of our user group this project is now funded to 93% .

Re: Price export and import

This project is now fully funded and development is about to proceed. Thanks to all of the supporters of this project.

Every OpenVPMS user will benefit from your contributions.

Re: Price export and import

I am yet to make this application work for us. 

Thought I would have another crack and think I have followed the above instructions but still get the following error.

 

Unrecognised document: speys may 2016.csv

 

Any thoughts?

Syndicate content