How to integrate your web site, EPOS or other systems into Sage 50
Integrating different systems doesn’t have to be a pain as long as you have looked at the data components and have a strategy in place for integrating them together.
The first thing to decide is which will be the ‘master’ system controlling the generation of new records (e.g. new customers, new products, new orders ….). Having decided this, the next step is to consider what data can be imported into the ‘slave’ recipient systems. You need to make sure that the mandatory fields are all mapped between the 2 systems and that optional fields are catered for if needed for reporting (e.g. Sage 50 must have a customer reference when importing an invoice but it does not have to have a customer order reference)
Having decided the data flow, the next step is to decide on the method of communication. In most scenarios, a batch job will be adequate allowing data to be exported from the master system to a file (e.g. Excel, Comma Separated Values (CSV), text ….) and then imported into the slave system periodically (e.g. import web site sales once per day). For this, it is customary to use a folder to save and retrieve the file from and is sometimes referred to as an ‘FTP site’ (File Transfer Protocol). These are normally secured using a user name and password and sometimes require a special program to carry out the file transfers.
In other circumstances, batch processing is not ideal and ‘real time’ updates is what is required. This was quiet complex to plan until fairly recently. With the advent of ‘web services’ (i.e. you can get to the data using the internet when you like) and synchronisation tools (e.g. Microsoft SQL Server has a ‘publish’ and ‘subscribe’ feature which automatically replicates entries in one table into a copy table) it is now much more practical to implement ‘real time’ updates.
Some of these tools are free but others can be quite expensive so you will need to select the most appropriate option based on volumes of transactions, frequency of updates and features required.
A popular database is MySQL http://www.mysql.com/ which is an open source database (i.e. the community version is free to use). It is used in various online shopping carts applications and comes with a wealth of system administration and integration tools such as MySQL WorkBench.
The other popular enterprise database is Microsoft SQL Server http://www.microsoft.com/sqlserver (the Express version is free to use) which comes with it’s own integration tools included in SQL Server Integration Services. To get the most from these tools it is best to have both knowledge of the application as well as the underlying database so you are best to seek advice from a technical consultant.
For those who are not technical, there are various integration tools which use CSV or Excel files as the import mechanism. One simple solution is to use Excel2Sage www.excel2sage.co.uk which allows for data to be imported into Sage 50 (and Sage 200 and Sage 1000) from either an Excel file or a database. This will enable you to use a single tool to integrate all your systems (e.g. web site, EPOS, in-house telesales system, Customer Relationship Management (CRM) software ….)
So eliminate duplicate data entry and errors by integrating your systems.
It will save you time, money and LOADS of reconciliations!
About Excel2Sage
Excel2Sage www.excel2sage.co.uk is a very easy to use program which allows everyday data to be imported from Excel to Sage with JUST ONE CLICK!
It improves efficiency whilst eradicating errors.
YouTube videos showing how various transactions can be imported using Excel2Sage can be found http://www.excel2sage.co.uk/index.php/tutorials/
About the author
Ara Martirossian is a Director of Red IT Solutions Limited http://www.red-it.co.uk as well as a National Council and IT Faculty Member at the ICAEW. He can be contacted via email on ara@red-it.co.uk.
