Sage and Onion project

One of the biggest problems we have in our office is accessing data about all of our clients. As the company has developed over the years various systems and databases have been put in place to hold data about our clients. As you can imagine this can be a problem as some information you need is in one system but some the other part of it is held in yet another database. Recently I have been working on a way to marry all this information together. I’m looking at running this through a php web interface and a mysql database.

All these systems have one common link thankfully, the customer code. Each place the customer appears the same code is assigned to them. The only software that has all the codes in it is our Sage Accounts. However in Sage we have two companies set up this means it can be hard to find a client in just sage. So the first feature that my new database has to be able to perform is a search on all accounts. Sage is also limited to 5 users, we have many more needing to access the information using php and mysql will solve this problem.

Sage is quite a useful bit of software if used correctly. However it’s not the easiest thing in the world to get the data out of. The report writter is setup for printing in set formats rather than creating csv files. It does have the ability to export certain screens to Excel, but this is quite limited i.e. if I export clients I get account code, client name, telephone number and email. To make my database useful it needs to contain more than this. Like full postal address and contact number.

I searched for a while and tried to do some hacks with the Sage report writter but nothing seemed to be working. I even tried calling the Sage developer support line (we have a developer account) but they said they couldn’t help as they didn’t support linking into other packages (erm what else is a developer going to do but link in there own software?).

Finally I started doing a bit more of an indepth googling and came across something interesting. In the later versions of Sage it automatically installs an ODBC connection to your Sage accounts file. It only does this for one company though. Thankfully it’s fairly simple to setup another ODBC connection to the other company.

• On a machine with Sage installed go to Start Menu -> Control Panel -> Data Sources (ODBC)

• Click on System DSN, you will see a data source called ‘SageLine50v11′ or similar double click this and it will show the datapath it is using. (C:\PROGRA~1\Sage\Accounts\ACCDATA is the default)

• To create a connection to a different company, click ‘Add’

• Give the new connection a name (I would suggest the name of the company in Sage). Then set the datapath, the datapath will be the folder called accdata for that company.

With the ODBC setup it is then just a matter of opening Excel and going to Data -> Import External Data -> New Database Query. On this screen you will be able to select which of your ODBC connections to use. Then you will be given a list of fields you can import from your database, its that simple. Obviously it all depends on what you actually want to export as to how complicated it gets.

Posted by Andy July 2008

Post A Comment

Name (required)

Mail (will not be published) (required)

Website