Stock export and import
Donate to this project
Development Project Status: Completed
This project adds support for exporting and importing stock quantities as CSV files.
This enables practices to use external stock management tools to determine stock quantities, and import the changes to OpenVPMS.
It adds two buttons to the Products|Stock Managment workspace, namely "Export" and "Import".
Export
The Export button launches a Stock Export dialog. This displays:
- filter criteria, used to select the stock to export
- a Find button, used to display the stock matching the criteria
- a Zero Negative Quantities checkbox, used to determine how negative quantities should be handled. It is selected by default
- an Export button, used to download the matches as a CSV
The stock may be filtered on:
- Stock Location - a dropdown listing all active stock locations, in alphabetical order. The stock location linked to the current practice location is selected by default
- Type- a dropdown listing All, Medication, Merchandise. All is selected by default indicating that both Medication and Merchandise products should be listed
- Name - the product name. Empty by default. Supports wildcards e.g. Acepromazine*
- Income Type - a dropdown listing product income type classifications e.g. Merchandise. All is selected by default, indicating that all income types should be listed.
- Product Group - a dropdown listing product group classifications e.g. "Vaccination". All is selected by default, indicating that all product groups should be listed.
Of these, only the Stock Location is required.
Stock Display
When Find is pressed, matches will be displayed in a table including:
- Product Identifier
- Product Name
- Selling Units
- Quantity
File Format
The export CSV file will include the following details:
- Stock Location Identifier
- Stock Location Name
- Product Identifier
- Product Name
- Selling Units
- Quantity - the current stock on hand at the stock location
- New Quantity - the new stock on hand at the stock location
If Quantity is positive or Zero Negative Quantities is unselected, New Quantity will be the same as Quantity.
If Quantity is negative and Zero Negative Quantities is selected, New Quantity will be set to zero.
The export file will be named stock-<location name>-<todays date>.csv to help differentiate it from prior exports.
Import
- When importing the file, only the quantities may be changed. All of the other values must correspond to existing stock location and product data.
- The file may only contain data for a single stock location.
- If there are any errors, they will be reported with the line number of the input file, and the import aborted.
- If the imported file is valid:
- a single Stock Adjustment transaction will be created, containing a Stock Adjustment Item per imported record where the Quantity and New Quantity are different
- The Stock Adjustment Item Adjust Quantity By field will be calculated as the difference between Quantity and New Quantity. This may be negative
- The transaction will have In Progress status, to allow the user to review it
- The new Stock Adjustment transaction will be selected in the Stock Management workspace, to allow users to Edit or Finalise it.
Configuration
The existing Practice configuration option "Reminder Export Format" that determines if reminder export files are exported tab or comma separated will be renamed to "File Export Format" and used to configure the format for both reminder and stock export files.
JIRA: OVPMS-1511
Comments
Re: Stock export and import
Export file should also identify the stocktake units, this would presumably be the product Selling Units?
It is unclear from the above whether the stock adjustment transaction will be a single transaction for all the adjustments, or one transaction per adjustment line. The latter would not be practical.
Is it possible for the export file to be an Excel file formatted for printing, rather than a csv?
Will the filters used for export be from drop-down lists or will be user need to type the criteria?
Re: Stock export and import
I've updated the project to incorporate your questions and comments.
OpenVPMS uses CSV export for reminders and products, so it would be inconsistent to use an Excel format here. Excel imports the CSV produced for reminders and products quite well, as does OpenOffice.
Re: Stock export and import
This project has been funded - thanks very much for your support!
Re: Stock export and import
Tim A - I apologise for not commenting earlier - I had not realised that this was in the pipeline.
The summary says "This enables practices to use external stock management tools to determine stock quantities, and import the changes to OpenVPMS."
In fact, since the only thing that can be imported is the stock quantity, this is really just a stocktake data entry facility - and that is wonderful - I can throw away my kettle facility.
The other part of stock management is setting the Critical and Ideal quantities, and it would be nice if the import/export facility covered these also. Adding these to the exported data is obviously very simple. Adding them to the import side means that the code needs to update the product data - and also have an option as to whether or not to do this.
If you really want to have a complete stock management facility, you need to add useage data to the exported information and a usage period to the Export options (so that you can get the usage for say the last 6 months). From the usage and the vendor lead times, it is possible to calculate the Critial and Ideal quantities.
Regards, Tim G
Re: Stock export and import
The export/import only supports quantity changes at present, as this is handled through the creation of Stock Adjustments.
Critical and ideal quantities would need to be handled differently, although it could be done via the same import file. There would need to be a review facility added similar to the Import Prices dialog.
Usage data could be determined by looking at charges over the period.
All of these requirements would need to be incorporated into a new project.
Regards,
-Tim A