Advanced Mailing and Data Transformation System

Design a generic system to help with the day to day operation of the Direct Mailing Division. System should use Excel as it’s primary interface and needs to provide the following functions:

• Job control and management
• Audit functions and reporting for both client and internal use
• Postal zoning of address information
• Address cleaning functions
• Name transformations (Shorting names, all upper case, joint holders etc
• Handle hold instructions in the correct manner
• Identify potential duplicates
• Random address salting (to assist with 3rd part auditing / quality control)
• Proof of concept for Neopost Integration providing OMR (Optical Mark Reader) support

Additionally the system should be easy to use for the user supporting both ad-hoc jobs and jobs where a fixed data exchange format has been agreed in advance.

The solution we choose for this project was to create three distinct areas of functionality, the first and most important was the creation of a common "back-end".  This back-end system implemented as a series of DLL's and custom controls wraps all the job control, audit and data transformation functions in a reusable form. 

Next we had to implement a custom Excel 2003 add-in to expose the functionally previously built in the shared back-end DLL and controls.  Additional client validation and warnings (using colour coding to allow the operator to better visualise issues with data sets) where then added to complete the Excel portion of the project.

The final delivery was a proof of concept application providing support for the Neopost OMR marks using a pre-defined data set.  This was developed as a standard Windows Forms application again built atop the common back-end system.  This application when provided with the correct data is capable of parsing, zoning, setting OMR marks and applying the selected data transformations to thousands of documents a minute.