Geode Software

The existing Truprint EPOS (Electronic Point Of Sale) system was enhanced by moving from multiple MS Access databases to a single SQL Server 2000 database. We implemented a VPN to link each shop to Head Office and used "merge replication" to synchronise the databases. Next we integrated the EPOS system with Harrier's ERP/ERM system (Microsoft Navision) to make it easy for them to add new products, set the selling prices and so on.

Introduction

Truprint is one of the best known brands for mail order photo processing. The brand name is owned by Harrier LLC, based in Devon. There, the company runs a major photo processing plant. Harrier have recently expanded into retail, coming into direct competition with existing photo shops like Boots and Klick. Currently Harrier own eight shops in the South West of England, with hopes to expand across the UK.

An important part of retail is being able to measure how successfully the shop staff sell, and in the photo processing business there are two distinct opportunities: once when the customer calls in to drop off film and again when he/she returns to collect their prints. Harrier had already developed their own EPOS system to keep track of these orders and sales separately in a Microsoft Access database.
Geode Software was contracted to develop the system further to make it more reliable, more automated, more scalable and easier to administer.

Original System

The original system was not designed to scale to many shops. It was intended to be used in one shop and was later expanded to cope with more, but there were a number of problems with the way this was done. At the start of the project the system relied on many manual processes in order to transfer the data between Head Office and each shop. The shops were not networked to Head Office, although they did have dial-up Internet access.

At that time, changes such as new products or price changes were requested by Marketing or Retail Management, but made by the I.T. Department. The method was to manually create a file and to attach this to an email for each shop. The file contained special SQL scripting to update the database in the shop. The shop manager received the email, saved the attachment, then loaded it into a special program to do this. When new products were added, they needed to be added to the company ERP/ERM system as well, which in this case is Microsoft Navision. So in some cases, the I.T. Department also sent the data for the new products by email to the Finance Department to be keyed into Navision.

There was a lot of software written in Visual Basic 6.0 that was used to automate some of the processes in the shops. For example, one process was set to run each night to create a backup of the database, although this was not moved off-site. Another process was used to extract the new sales and orders from the database and attach these to an email to send to Head Office.

Changes

The new EPOS system was designed to cope with 100+ shops and to be far more automated.

Database Design

The most fundamental change was to update the database from Microsoft Access to SQL Server 2000. The structure was changed to improve performance and to reduce the size of the database, achieved mainly by normalising the tables and moving from text to integer based fields where possible. One common structure was designed to be used in all the shops and Head Office. The existing till software was changed to link to the new database running on SQL Server.

Networking

Each shop was upgraded from dial-up to broadband Internet access through a Speedtouch 608 modem. The shops were linked to Head Office using the VPN capability of the modem, giving I.T. staff the ability to diagnose and fix any problems remotely instead of having to visit the site.

Database Synchronisation

To replace the manual updating of the shop databases with an automatic system, we chose to use standard SQL Server technology; merge replication. This updates each shop with any new products, prices and discounts and updates the Head Office database with each sale made at the tills.

ERM/ERP Integration

Harrier use Microsoft Navision for their Enterprise Resource Management/Planning (ERM/ERP). This system runs on a SQL Server 2000 database, which allowed us to integrate the EPOS platform with it using standard SQL Server technology. All product information, from category, barcodes, selling prices, offers etc. are extracted from the Navision database and translated into the EPOS database using remote queries. All sales and returns are written back into the Navision database.

Browser-Based Software

The till software is used only on the till machines. In addition, there is a PC in each shop that is used for other functions, such as entering the banking figures, running reports and controlling a stocktake. All this software is now written as a set of ASP pages, allowing a standard browser to be used in the shop. This design idea also allowed the stocktake to be carried out using the browser on a wireless PDA, requiring no additional software to be installed on it.

Technology

• SQL Server 2000. Design, Replication, Security, Batch Jobs
• VPN using Speedtouch 608/610 modems
• Microsoft Navision. (Integration to Finance Systems)
• Visual Basic 6. (EPOS Till Software)
• ASP
• IIS

Benefits

• Vast reduction in in-house software to maintain.
• I.T. staff can update shop software without site visits.
• Data is moved reliably between Head Office and each shop.
• Data is backed up as part of the standard I.T. process.
• The system now relies on standard and supported technology.
• Consistent GUI for shop software, browser based.
• "Live" sales information available to Head Office.
• Easy for Marketing Department to add new products, promotions, discounts, and to change prices.
• Browser based reports show statistics per employee, per shop and over the whole group.
• Customer information is available to each Truprint shop, so the customer can use their Collect Card in every shop.