Sage 200 Universal Stock Import

Company: EBC Brakes

Requirement: To Drive Frequent Stock Updates from Excel

 

Initial Situation

Our client, a successful world-wide manufacturer and distributor of brake pads and disks for cars and motorcycles, needed to adjust product groups, prices and other stock information on a near-daily basis. As the stock list run into the tens of thousands record-by-record update was time consuming and prone to error.

Challenges

The customer was happy to maintain the stock details including prices and other details on a series of master Excel spreadsheets. However, the standard Sage 200 import was insufficiently sophisticated to read these sheets as they could involve changes to product groups, warehouses, units of measure or other fields that cannot be addressed by the built-in Sage 200 stock importer.

Solution

The Paradise Development team examined the options and determined that the most direct solution was to write an entirely new stock importer with much greater capability than the built-in routine.

The importer was written in C# .Net to read an Excel spreadsheet containing some 30 columns to include 5 price list values in different currencies, warehouse and bin locations, minimum stock levels, product groups and analysis code values.

The importer runs extremely quickly taking only seconds to read in thousands of records, either updating existing stock records or adding new records when new product codes are encountered.

An audit report is then produced (and can be emailed to specific staff) that details any issues encountered by the importer, including failures to update due to incorrect data or impossible combinations, such as negative prices or invalid warehouse locations.

Outcome

The customer was pleased with the result and added additional features on a regular basis, constantly adding new fields and capabilities to the importer. In 2012 they began discussions with Paradise to re-author the entire mechanism to add a whole additional level of functionality, including the ability to remove or disable stock items and remove and shit-down warehouse and bin locations.