Making ends meet

Accounting software is a mature product, and packages can bulge at the seams with functionality. Now that B2B trade is moving up several notches, how well the package integrates with other internal systems has become a top-of-mind issue.

Accounting software is a mature product, and packages can bulge at the seams with functionality. Now that business-to-business trade is moving up several notches, as companies ready for the internet and forge even closer links with customers and partners, how well the package integrates with other internal systems has become a top-of-mind issue. Andrea Malcolm finds out how far we’ve come along the integration road.

Auckland-based Independent Liquor, a manufacturer and exporter of alcoholic drinks, was happy enough with its Exchequer Enterprise accounting software but decided it needed more warehouse management functionality.

It wanted to integrate a new inventory management system with Exchequer and began searching for a mid-tier, off-the-shelf solution that would fit well. After three months, the search is almost over. The company is about to put Axalta from Danish company Navision Damgaard through its paces to see if the two systems are a close enough fit.

IT manager Alex Paterson says it’s been a difficult process finding a product that will integrate well — some inventory management software vendors said they wouldn’t supply a system because they had their own financials, some said they would but didn’t know where to build the interfaces between the two systems.

But having spent a decent period of time looking, Paterson believes the two systems chosen will be easy to integrate. Both products are modern, based on Windows NT and use XML (extensible mark-up language), DLL (dynamic linking libraries) and COM (component object model) tools for communicating with each other.

Changes must be made in real time, not in delayed batch-mode processing, and the systems will have a two-way interface so changes can go in both directions. Each will be the “master” for certain information. The debtors/creditors and general ledger will maintain the master files for financial information and all inventory information will be ruled by Axalta.

Auckland software developer Co-Sys is doing the integration. Co-Sys director Geoff Roberts says software integration is more cost-effective than it used to be. There are an increasing number of third-party products providing links to accounting packages.

Fellow director Steve Jubber says it’s important to choose packages that provide tools for developing links to other systems. “The degree of availability of those sorts of things will depend on the amount of money you spend on your accounting package.

“If I was a company I’d be looking for products with toolkits for DLL, ODBC [open database connectivity], e-commerce and e-business capability and OLE [object linking and embedding].”

It’s all in the planning

Steve Marriott, technical director of Omega Financial Solutions, a Wellington software house, says that when it comes to integrating software, planning is paramount.

The first thing OFS want to know is which system will be the master, so if there is a data conflict, they know which system will prevail.

“We get clients to nominate a master system and which fields will have data going across. We ask whether it will be two-way synchronisation or whether data will flow one way only. We want to know how frequently data has to go between systems. Does a name and address need to be there instantly, can it wait 15 minutes, or can it wait overnight? Often when someone takes an order, the invoice doesn’t have to be produced until a week later.”

Marriott says these business rules are often in place but aren’t formalised until this process is gone through.

In Invercargill, Sycamore Print is integrating its Profax accounting system with a job costing and quoting module.

The module, called Print IT, was developed in Jade by local developers Resource Solutions (Southland) (RSL).

Managing director Graham Sycamore says the company, which does commercial printing, was using a specialist printing program that was DOS-based. The cost of upgrading to Windows was prohibitive, so it chose to get a Windows module written to integrate with its Windows-based accounting package. The aim is to use the information in job sheets to generate invoices from the accounting system.

The two systems will be tightly integrated, as RSL has worked closely with Profax in developing the program. The companies will also co-ordinate upgrades to ensure they are consistent with each other, says RSL business analyst Danny Watson.

When it comes to keeping upgrades in synch and consistent, using modern packages with technology such as DLL toolkits for the software developer also helps, says Jubber of Co-Sys. “Tools today will always support former functions so that if you upgrade the system, the tool will support what has been done before. A call will always stay the same, even if the data structure changes.”

Linking old and new

Jon Donald, from financial solutions developers Amtrax, says custom-written modules usually perform specific functions related to the processing of customers’ requirements (such as plant hire systems and job booking systems). Although these systems are often standalone and may generate invoices, for more efficient operation they are often partially integrated with an off-the-shelf accounting system either by batch export/import, or fully integrated with an ODBC link.

Older or entry-level accounting systems generally use a CSV (comma separated values) format for exporting files, perhaps with an Excel spreadsheet as an intermediate step for editing data. Usually the operational software will be a newer, Windows-based system, which is able to export data in the required format.

Donald says the batch export/import method is cumbersome and time-consuming, and relies on the user’s skill to ensure it is done correctly. Controls must either be built into both systems to ensure batches are not omitted, or duplicated, or manual records must be scrupulously kept to ensure no problems occur. Another disadvantage is that duplicate databases need to be maintained in each system.

When both the accounting and the operational package are based on 32-bit Windows, each database may be linked, says Donald.

“For example, when a user creates a job in the hire system, the look-up for the customer is linked to the accounting debtors ledger. This means only one database needs to be maintained for the customer. If a new customer is added in either system, it will automatically be added to the other system. ODBC linking requires that appropriate ODBC drivers are available for both systems. This is not a problem for mainstream packages such as a MS Access operational package linking to, say, a Sybiz accounting system, but it’s a problem with lesser known, proprietary or legacy systems.”

Donald says as a halfway measure, some accounting systems have a read-only database. This will allow the specialised package to read a customer record in the debtors ledger but not change, for example, the phone number or delivery address, and certainly not add a new customer or write a transaction to the debtor files. This is often done to protect the financial integrity of the accounting system.

An open database can be changed via the ODBC link, but there needs to be specific controls built into the process to ensure data integrity.

“When developing the integration, users need to be aware, and to specify what the controls must be, as a programmer on his own generally does not understand the accounting principles involved. An ounce of prevention here may be worth the proverbial ton of cure later.”

Put something in between

Marriott from OFS is strong on having an intermediate database between systems.

“The biggest problem we come across is when a customer has bought two systems that don’t use the same database or database methodology, for example, a point of sale [POS] system with a propriety database and an accounting system which uses either MS SQL or Btrieve. The problem is keeping the two in step when you’re transferring data from one to the other. Everything goes nicely until they have a problem in one system, which requires it to be restored. If the box that the accounting system is sitting on corrupts the database and you have to restore, how do you know which are the transactions that the POS system has given you that day? That by far is the most time consuming problem we find.”

When OFS does integrations it usually has a database that sits in the middle, so that the interface is then a two-step process.

“You read the transactions from the POS into the intermediary database and check for duplicate transactions there. Then there’s a second process which goes through and posts them to the accounting system, and flags them as posted at that point so you can see which day transactions came from the POS. One drawback is that intermediary database gets very large.

“We’re looking at it from [the perspective of] making sure transactions that come into the accounting system are correct. The database is usually MS Access because it’s free, readily available and easy to use. Plus it’s an open database so you can use all sorts of things to report to it.”

When it comes to maintaining compatibility between system upgrades, Marriott says it depends on the degree of the upgrade.

“For example, with payroll systems if it’s just an upgrade for a tax change it will have no effect on the interface. Often vendors will leave their standard interfaces alone and won’t upgrade them unless it’s important. There maybe some new features in the package but the data is essentially the same and exported in the same way.”

Rob Clarke, an accountant and consultant with Auckland-based Foundation Independent Computer Services, says functions commonly merged with accounting systems are contact management and operational functions such as production or manufacturing.

“It can be complex, depending on what it is. You’re heavily restricted by what your other systems are written in and what databases they use.”

Having a package with an open database (using ODBC) makes it easier. “By using a database that’s common you have ODBC drivers in Windows, which are important for report writing,” he says.

XML a saviour?

In theory XML may make a difference, says Clarke. “XML is a way of packaging the message but not everyone will necessarily do things the same way inside the message. It will make one layer of the process more simple, but it will still be tricky.

“It potentially removes the need for a commonality between databases, because you have a method for sending data back and forward between disparate systems — not only your own systems but also those of your suppliers and customers.”

Auckland-based training resource company Training Point.Net deliberately steered away from integrating different systems by choosing an accounting product which has contact management built in.

Training Point.Net has 11,000 people on its database, a quarter of whom are active customers.

Managing director John Davies says the company chose exo-net because it was an integrated accounting and contact management system.

“We had three legacy systems that we had to extract data from. Taking old data and integrating it with the new system was a real challenge. We did it in-house and hired in temps. It took five months. Effectively we ended up with a major Excel spreadsheet database which was able to be lifted up into exo-net.”

The hard work has paid off though, says Davies. “In the last two months we’ve done direct marketing. Where as in the past we would have mailed to 11,000 people, we’ve only sent to the 3000 people who we know are interested.”

Join the newsletter!

Error: Please check your email address.

More about DamgaardExcelOmega FinancialSybiz

Show Comments
[]