Solve Database Problems & Booking System

The Problem

Our client had an existing in-house developed database that was used to track appliance repairs. Although the existing database could manage much of their business, it was not able to support more complex monitoring and billing requirements for their business.

The most urgent database problem to solve was the underlying data structure of his existing system. Our client had a number of ideas for enhancements to the system but needed a more robust data model in order to do this.

Job management was not straight forward. Several visits to a customer's premises may be required, and manufacturers might need to be consulted for technical information. In addition, parts and spares may need to be ordered before the job can be completed and billed.

Our client had a manual booking system for engineers. It was not easy either to see the current allocation of engineers, or to record the time spent on jobs.

The billing process was complicated due to the nature of the parties involved. For instance, the final cost of the job may have to be split between the end consumer, the original product manufacturer and/or the extended warranty insurance company. In addition, parts and labour need to be accounted for separately.

There was an additional requirement for the export to Sage of invoice information. This was not straightforward as different elements of the invoices (such as parts and labour) had to be allocated to different Sage accounts.

The Solution

We re-engineered the data model to more accurately reflect the system requirements. With the underlying database problems solved, this now forms a more solid platform upon which new features can be built.

An improved job management system now includes an active service jobs dashboard. This shows the status of all jobs with their priority. This links to an improved job detail area which brings together the customer, product, timesheet and billing information in a single place.

The custom booking system incorporates a booking form showing upcoming scheduled engineers' appointments in a consolidated diary view. This can also be used to navigate quickly to current engineer time and job sheets.

The billing section was re-written to streamline the generation of invoices. This now accurately calculates charges relevant to the customer, manufacturer and/or the warranty insurer. This also tracks the cost of parts, with charges allocated to the correct party.

Finally, with the improved data model it was possible to incorporate a customised export module to produce and export data to Sage Accounts. This includes the correct nominal codes for the relevant invoice charges.