Business Software & Enterprise Resource Management
Making the most of your business softwareArchive for Data Import & Export
Farming Off Supplier Price Maintenance To Less Technical Users
In principle, and in an ideal world, you can obtain a data file from a supplier and upload price file changes directly into your stock control system. This might work well if your pricing is based on a list or retail price set by your supplier or your pricing is based on a specific margin or percentage.
But what to do if your pricing is not as simple as this? Often a lot of manual editing and reviewing is required after price changes have been received, and there is just no way you can ask your computer software to automate this for you.
In a situation like this the best solution is to create a procedure for exporting the price data into a spreadsheet such as Excel, and then making the re-import as simple (and as fool proof) as possible.
The first step would be to create a process (in the case of CAPITAL, a report) to write a data file that can be loaded into Excel. Since Excel can read simple text files, there should not be any difficulty in producing a report like this. CAPITAL’s Report Wizard can be used to produce such a data file, and many other report writers or alternate software packages should have similar capabilities.
Once that’s been achieved, and the data file created and edited inside Excel, and perhaps resaved, the next issue to contemplate is to how to get that data back into your system?
Importing data can be potentially dangerous, particularly if the wrong data is imported! One of our users devised an interesting process to deal with this issue, which our consulting team helped him to implement, by helping him deal with some of the technical details.
First we created a generic card file containing two fields: the name of the user who was permitted to do the import and the user’s email address.
Next we added a menu option to the stock control area and attached a script that opened the card file and matched the user’s log in name to any entry in that table. If the user wasn’t found they couldn’t continue with the importing of pricing data. (Unless that user also had administration rights.)
Users working with price updates had to save their data file out of Excel into an agreed location on their computer, depending on their department/branch code.
The script we developed then compared the price info in their spreadsheet/data file to their department ID and if they agreed, the import was allowed to continue.
If there were any problems with the import, such as trying to update a product code that didn’t exist, or if a record was being edited by another user at the time of the price update, an email was automatically sent, reporting the problem or list of problem records.
Effectively, all the user had to do was:
(1) Click on a few buttons to produce a price update data file.
(2) Load the data in Excel and modify it to suite.
(3) Save it back to it’s original location
(4) Select “Import Price File” from the Stock menu.
What was typically a time consuming process for the computer administrator, could now be farmed off to users in each branch as the process was simple enough for non-technical staff to manage whenever they needed to.
