Project Goal
Hayes Shutters, who manufactures furniture-quality plantation shutters in their facility in Ellijay, Georgia, hired nSiteful to streamline their ordering process.
The Problem
Previously, most of Hayes’ customers (wholesalers and retailers who sell to and service homeowners) submitted orders by downloading and printing out a PDF of a 130-field form, filling in dimensions and other specifications by hand, and then faxing it back.
Upon receiving each fax, someone from Hayes — usually David Hayes, the owner — had to transfer all of the data into a master Excel spreadsheet that automatically generates cut lists and calculates costs for each order. It should be no surprise that the potential for problems — namely, data that is either invalid or illegible or both — was great.
The Solution
nSiteful’s job was to create an online form for order entry; programming scripts for validating submissions and transforming them into files to be imported into Hayes’ master Excel spreadsheet; and an administrative notification system and interface for downloading orders. As a second phase of the project, we built a customer portal through which customers can view, print, and clone saved orders. Hayes personnel use the portal to view, print, search, download, and update the status of orders.
The customer portal and form are integrated into Hayes’ existing WordPress Web site, thanks to the site’s developer, who brought nSiteful into this project. Scripting is done in PHP; customer and order data are stored in and served from MySQL tables in a database external to the WordPress database.
Screen Shots of Online Order Form
Validating the Online Order Form
One noteworthy — and challenging — aspect of the project was validating form submissions. Some of the factors adding to that challenge:
- Many of the dimensions have to be entered free-form as Imperial units (inches with optional fractions).
- Those optional fractions have to be separated from whole inches by at least one space and expressed in halves, quarters, eighths, or sixteenths of an inch, reduced to their lowest terms. (Examples: 1 1/4; 22 7/16; 30 1/2)
- Several of the dimension fields have minimum and/or maximum constraints.
- Some of the dimension fields accept specific sets of optional non-numeric values.
- There is a multi-row section of the form, where the customer enters dimensions and specifications for up to 10 shutter panels.
(In another blog post I wrote about my foray into Regular Expressions to address the inches-fractions validation requirement.)
Converting Submissions from Online Order Form to Importable Files
Another aspect of the project worth noting was converting form submissions into files that can be imported into David Hayes’ master Excel spreadsheet.
(You might be wondering why the project didn’t include the creation of a new back-end system to replace that spreadsheet. If you were to inspect that extremely sophisticated spreadsheet, you’d understand why we took the “Don’t Fix It If It Ain’t Broke” approach.)
What the system does is generate a single-row Master CSV file for all of the basic order data (customer information and manufacturing specifications that apply to all shutter panels) and a Details CSV file that contains a row for each shutter panel ordered. The system then combines the two files for each order into a single downloadable zip archive. The contents of each individual file is mapped to predefined cells in the master spreadsheet so that all cut-list and cost computations are automatically performed immediately upon import.
Summary of Benefits
As of this writing, more than 30 active registered users of the online system have submitted more than 1,000 orders for more than 6,000 shutters.
The online system eliminates virtually all of the data-entry errors that the paper-based order entry method admits. Our client reports that each order placed through the system saves so much administrative processing time that they have been able to reduce turnaround time dramatically — benefitting everyone.
More and more of Hayes’ customers are converting from the old method to the new system every week, and these online customers are thrilled with the system’s convenience and efficiency. In fact, several of Hayes’ online customers actually submit orders at the point of purchase — in the homes of their customers, accessing the system from their tablets in living rooms and at kitchen tables all over the country.
HEY! I need this form too!! We have out of town customers that need to submit measurements and this would make it SO MUCH easier for them to submit measurements on-line rather than our “old way” of printing and faxing!!
Let’s convert!! Thanks Jeff!
Nancy
Yes, indeed, Nancy. We shall do.