Finding a reliable alternative to MS Access

Western Urethane Systems (WUS) provides urethane foam insulation. WUS sells urethane for insulating pools, hot tubs and spas.

Project Background

When we got in touch with WUS they had an old MS Access database which they were relying for their entire business. It was a vital part of their production process. If something in the system failed, the entire business came to a screeching hault. The database itself was not reliable. WUS spent a lot of energy putting out fires whenever the database didn’t work as intended. These were anxious and delicate times!

Key Challenges

As most manufacturers, WUS deals with hundreds of orders each week. They need to record this information so that they can generate reports and share information with their staff and clients. Recording and reporting information is key in keeping their business running smoothly. The main problem WUS faced was entering production information on a weekly basis. When entering data, there were issues with data consistency and integrity. Also, it was hard for them to do it efficiently.

Aside from that, it was difficult for WUS to make pricing changes. Each year they change their pricing based on last year’s production and costs. Their old system wasn’t flexible nor transparent enough when trying to make these changes. It was scary process for them. They didn’t know how it would affect the rest of the system.

Because of the amount of data their business records, they rely on reports which total and combine data from many sources. In fact, all their business decisions hinge on their production reports. Which is why, it was vital for them to be confident that the data shown was correct.

Playing Detective

Aside from improving the reliability and performance of the system, we wanted to make it foolproof to use. Since WUS spent hours each day using it, we wanted to find out how we could further help them be more productive.

We spent time understanding their current process and how they did things with their current system and why. The why for us is the most important part because it let’s us know the intention of the action. It’s the springboard we use to create new ways to solve problems. Once we came up with a solution, we had WUS test and confirm that the design would work for them.

Rinse and Repeat

Our approach is the same with most of our clients. We sketch out new ideas, review the mock ups, implement the feature and review. The review part is key because we’ve found that some people can’t fully capture an idea until they can interact with it. Even though our mockups are interactive, they are not like the real thing. This review always leads to a few more iterations. Tweaking and adjusting to ensure it’s just right.

Solution

The solution we created was a web application which ran within their intranet (not accessible to the entire internet). We built it using AngularJS, Yii 2 and SQL Server. Using these technologies allowed our team to triple the performance and reliability of the system. Their new system is much powerful allowing them to run more reports with more data than ever before. Not only that, their old system restricted their staff from accessing the system at the same time. Our solution allowed for that without decreasing the performance.

Another side effect of switching to a web based solution is how we can make changes and deploy updates to their system. In the past with MS Access, it took coordination and lots of time and effort to send new files over email. Now, we can upgrade their system without them even knowing it and only in a matter of seconds!

A few hiccups

Not everything went smoothly when creating this new system. We learned the hard way that integrating with a FTP file server from a Windows server using PHP is not possible. After hours of work, we had to change our approach and provide a different way to handle the import of sheets.

The other issue that came up was creating a consistent, simple UI. WUS staff isn’t necessarily the most tech savy users. So we wanted to make sure that whatever we created would be simple enough for them to work with. We wanted to create an interface that was not intimidating and would allow them to accomplish tasks as quick as possible. We envisioned creating a system where most tasks could done from one screen. As we delved deeper into this, we realized that it was easier said than done. As a result, we had to compromise on some areas.

The last issue we had to deal with was the lack of communication from WUS. For any software project to be successful, it is key to be in touch at all times. Throughout the project there were times when they were either away or quite busy. This meant some things fell through the cracks or were forgotten which lead to repetitive conversations. We tried to counter this by asking as many questions as possible whenever we had the chance. We also made sure we answered any concerns they had right away.

Because it was a new system, they were a bit hesitant at first, but due to the iterative nature of our approach they got on board fast. Trying out the system as we build it is a great way to not only learn how to use it but also mold it exactly for your business.

What did the end result look like?

Find and edit

Unlike their old system, the new one allows WUS to easily find old entries. Being able pull up old entries is invaluable in correcting and preventing mistakes.

Enter new sheets

The main goal for us was to reduce the time it takes to enter new sheets. How did we go about it? Enter new sheets without having to take your hands off the keyboard.

Import sheets

Entering new production sheets took days. On top of that, every week there were unable to enter a new sheet for one reason or another.

Our solution was creating an import system that allows WUS to import sheets directly from their clients. This new system has sped up the recording of sheets drastically. On top of that, it is able to detect missing information and recommend appropriate actions to the user. The end result is a more independent user that requires less daily support.

Pricing changes done right

Before we implemented this new system, pricing changes were a mystery. Now, through a visual timeline, WUS knows exactly when pricing changes take effect.

Create and email reports

Before reporting was a slow and tedious task. Emailing them was a 3 or 4 step process. Now, they can generate reports within seconds and send them in an email to their clients with a click of a button.

Looking Back

Generating PDFs can be quite frustrating when dealing with large sets of data.

Our biggest doubt was how they might react to a newer platform and modern design. It took a bit of getting used to, but they are quite comfortable now. It was a nice surprise!

I wished we’ve had more time and a bigger budget to work with. We could have looked more deeply into their process and suggested areas where they could use software to automate tasks.