The Problem

A roofing company required a very customized quote to be created for their clients. The original quoting process was executed as follows;

  • An admin employee would utilize an Access application that was built in-house to lookup customers and create a quote. While the customer was stored in the database, all of the line items on the quote were manually inputted. They would then use an Access Report to print a professional looking quote. They would then scan the quote to their email in PDF format.
  • The employee would generate a specifications document using a PDF form and export that to be part of the overall quote.
  • Any number of CAD drawings and text files provided by the engineers would then need to be combined along with the line item quote and specs document into a single PDF which would be the final quote.
  • Finally, this quote would be sent to the client via email.

This overall process was very manual and time consuming. Much of this process was automated in a series of phases detailed below.

The Solution

Phase 1

The first thing we did is prevent the user from having to print and combine the PDFs. We setup the Access Report with a small amount of VBA to automate the Print to PDF process. An Accessory Command Line App was built using an open source PDF library to combine the PDFs. The VBA would call the accessory app, which would prompt the user to select any number of files to combine. Once the files were combined, the would be attached to an email template through Outlook. The specs document was still required to be completed manually at this point.

The result of Phase 1 was a dramatic decrease of time spent in the quoting process. One issue that remained however is that the Access App was not originally designed to be used by multiple users. An attempt was made to break out the backend database, but it was unsuccessful.

Phase 2

The next phase of this project involved a custom designed WPF application that was tailored to their specific quoting process. SQL Server was used as the back end datastore. In order to preserve historical data, I created an SSIS package which imported data from the existing Access database and mapped it into a table structure I designed for the application.

Custom modules were created to track customers, contacts, product info, quotes, and spec documents. During the quoting process, the application would prompt the user to import any number of CAD drawings or text files submitted by the engineers. The application would use the open source PDF library to combine all documents into a single PDF and attach it to an email.

I also designed a process by which the user could export a list of products from their primary Line of Business app into an Excel documents, and import it into the Quoting App so as to prevent manual entry. (There was no public API for the LOB App.)

The result of Phase 2 was a another dramatic increase in productivity as the application was more streamlined and more intuitively designed. I also designed it with multiple users in mind, which eliminated the restriction that the app could only by used by one person at a time.