After a key employee left, the Service Database needed upgrading, support and administration. The original system was made up of numerous databases that supported the following users:
Service Reps & Managers: Maintaining Plant Equipment
- Writing reports of call (ROCs)
- Writing return goods authorizations (RGAs)
- Documenting machine parts and replacements
- Documenting plant contacts
- Submitting ROCs for invoicing to accounting
- Monitoring when goods are being returned
- Managing customer relationships
- Reviewing ROCs
- Initiating and approving RGAs
- Billing of service rep time & expenses
- Handling Return RGAs
- Executing debit/credit authorizations (DCAs)
The Service Database was built in Microsoft Access databases. Because of the nature of the work, there were multiple versions of the system for sales reps, sales rep managers, sales, accounting, and plant managers. Data updates were emailed back and forth and were often up to two weeks old due to the manual nature of updates.
Phase I of the solution targeted two main benefits:
1. Improving the movement of data through automation
2. Supporting all user types by Combining database front-ends
To improve the movement of data, Woodmark implemented a SQL Server database to replace Microsoft Access. The Access front-end would be maintained to leverage existing forms, reports and logic. By utilizing SQL Server Merge Replication, data is automatically synchronized between remote users and a central database every time they system is launched with internet network access.
Benefits & ROI
By automating data updates, the central database is much more current and manual effort is reduced related to circulating updates. Using SQL Server tools, there is better tracking and understanding of who is using the system in a timely manner and there is more oversight of service reps. The prior update method was to have service reps transmit their reports of call on a weekly basis. The data is now synchronized in realtime when the service rep and maanger have internet access. The SQL Server platform allows for remote connectivity using data replication over a secure web connection which does not require VPN software but is equally as secure.
Another benefit of the project is that multiple database front-ends were merged into one front-end using customized menuing by user type. This presented immediate benefits since multiple versions of similar functionality now only need to be updated once instead of multiple times. In addition, users could be granted access to new functionality that was available in other versions, but had not been in their version.