MYOB ODBC

Thursday 19 June 2008, 14:00 WST —

I have a problem at work. Basically, I have to do the accounts, this means I have to type out the sales orders from our web-based/PHP/MySQL application (that I wrote) into MYOB. This chews up my time, what would be cool was if this could happen automatically, or at least as easy as possible for me.

Enter the MYOB ODBC driver. This basically lets you automate importing and exporting into MYOB, you can get MYOB read access for your company file for about $249AUD. However, write access requires that you sign up for the MYOB Developer Program, this costs about $700AUD per year.

Once you’ve signed up, you get a developer pack sent out, which is basically most of the MYOB software, and access to the MYOB developer forums. Now I’ve been using Ruby/Rails for my webapp development recently, so I went googling for some information for getting MYOB working with Ruby/Rails and came across Pat Allan’s site, Freelancing Gods.

Many thanks to Pat for his post, as it’s pretty much the only decent information you can find on the net about getting Ruby/Rails going with MYOB. Since this sort of information is a bit thin on the ground, and I’ve got such a shocking memory, I thought I should blog some of this stuff.

First off, my situation. I work at a two-way radio company Direct Communications, we sell and repair all types of commercial two-way radios. For our sales, repairs and purchase orders, we used to use an Access database, we have since migrated to a webpage based system. At least, we have for the sales and purchase orders, the jobcard still runs under Access, and are accounts are handled in MYOB. The upshot is, like I said, that I have to manually enter in each and every purchase order, sale, and jobcard into MYOB.

Okay, so to get around this, I have to get the sales, etc, into MYOB without me typing the damn things in. For write access, the MYOB ODBC driver depends on the MYOB program, so you need your ruby/rails app running on Windows. So to start with I got the one-click Ruby installer for Windows. Once you’ve installed Ruby, you’ll need a few RubyGems.

More to come…

This website uses IntenseDebate comments, but they are not currently loaded because either your browser doesn't support JavaScript, or they didn't load fast enough.

11017 Responseshttp://luminousmonkey.org/?p=110MYOB+ODBC2008-06-19+06%3A00%3A19Mike to “MYOB ODBC”

Allan Mann Says:
July 2nd, 2008 at 4:03 pm
Hi there,

A very informative blog.

Have you ever thought about creating the invoice in Excel? It’s relatively simple to do (time consuming though).

This method is ideally suited to memberships and subscriptions ( I did it for the Cancer Council a few years ago and they used it for creating 20,000 invoices).

If you’re invoicing widgets it would be difficult but if it’s a service being provided Excel can be used.
Mike Says:
July 2nd, 2008 at 8:34 pm
G’day Allan,

I have to admit, my Excel knowledge is really limited, and using the ODBC driver allows me to make a large part of the process automated. Unfortunately although the ODBC driver allows you to get the read and write tables, MYOB itself isn’t a database so you can really tell the whole SQL thing is shoehorned into it.

The upside of doing it this way is we can really customise the software, for example MYOB has no concept of tracking serial numbers, but with the new app I’m designing it keeps track of all that.

My current plans with MYOB integration include setting it up to automatically send out statements for overdue accounts, there seems to be so much of my job that could largely be automated as we seem to do lots of double entry.

I plan on posting more on this topic soon, at the moment the rails application is on my desktop, but I’m going to setup a virtual machine on our Solaris server and have it running off that.
dan Says:
July 7th, 2008 at 12:05 pm
Hi Mike,
I am not a programmer but have been looking for a solution to get an MS Access database to update MYOB (and hopefully not the manual import data way). I came across Business Driven Systems, a company that provides middleware for MYOB, and their product is supposed to talk to any database type. I don’t know enough about this stuff. My question is; Have you looked into this and would you consider it user friendly for a novice?
Mike Says:
July 7th, 2008 at 12:59 pm
I have to admit, I haven’t done too much research on other offerings. The best advice I can think of is to ask for a trial version, after playing with it for a short while you can get a pretty quick feel for how usable you’ll find it.

I can’t get a feel for how the software is from their website, they seem to like mentioning things like SQL, XML, MS.NET, etc, which actually don’t mean much to you as the end user and there’s no screenshots on their webpage. So I can’t really give much of an educated opinion on their software, sorry!

I guess the only real bit of advice I can give, is call around, check out demos and see what you can find that suits your situation. Find out what experience they have writing applications, what programming qualifications they have, maybe even find out who some of their previous customers are and talk to them. Personally I would be wary of anyone using buzzword terms, they should be considerate enough to know your probably don’t know what they mean.
Sandy Says:
July 8th, 2008 at 11:18 pm
Mike, will this approach with RoR only work if hosted on the local machine that has myob on it. I was thinking of trying to read the myob database in remotely via an externally hosted app. It seems like a lot of trouble compared to integrating quick books. Is your code base hosted anywhere that I can look at?
Mike Says:
July 9th, 2008 at 7:24 am
G’day Sandy,

The way the MYOB ODBC driver works, requires that you have a copy of MYOB on the machine. It appears to run MYOB with the GUI disabled, if you wanted to run the Rails app on linux, etc, then there is an ODBC to ODBC bridge that would allow you to have a Windows machine setup to accept requests from the network. However I was put off the price.

The code isn’t anywhere yet, it’s nowhere near production quality, it’s just something I hacked together. However, since I hope you won’t hold the quality of the code against me, I’ll see about getting it up here, just on the condition if you make any improvements I’d like to see them.

I’ve just got WindowsXP setup on my Solaris box with VirtualBox, and I’m going to do a post on setting up MYOB and Rails from a fresh install.
Sandy Says:
July 10th, 2008 at 11:13 am
Hi Mike,

Have you looked at the import/export csv feature in MYOB? I know importing and exporting to csv is pretty well documented… but i guess the changes would require an import/export everytime, rather than the unobtrusiveness of connecting straight to the ODBC.
Mike Says:
July 10th, 2008 at 12:00 pm
Pretty much, using CSV import/export can’t be automated as easily. Anything you do via the ODBC driver you should be able to do with the Import/Export function, but it’s a matter of making it automatic. However you can’t do things like update lines, so SQL functions involving UPDATE simply don’t work.

Also, when writing to the tables there’s no concept of an id for each line, and actually getting lines added to the same invoice involves wrapping it in a transaction. For example:
```
  def create_myob_invoice( iso )
    # We can assume we have the job
    # Now, for each iso line, we add an invoice
    latest_invoice_number = Sale.find( :first, :order => 'InvoiceNumber DESC' ).InvoiceNumber.to_i
    if latest_invoice_number
      # We need to wrap it all in a transaction so MYOB knows they're all the one invoice
      MyobDatabase.transaction do
        iso.lines.each do |line|
          new_service_line = ImportServiceSale.new
          # Map iso fields to MYOB service sale fields
          # e.g. suff like:
          new_service_line.Description = "#{line.sDescription}"
          new_service_line.save
        end
      end
    end
  end
```
So, it’s like you’re writing a new invoice everytime, the only way MYOB knows they’re supposed to be lines on the same invoice is that they’re wrapped in a transaction. I’m sorry, it’s probably not very clear.
Sandy Says:
July 10th, 2008 at 4:32 pm
Ok, probably the only thing that the csv feature is useful for is if you are migrating completely across from MYOB to an online system and never going back? On the Freelancing Gods site (http://freelancing-gods.com/search?text=csv) you have only reference columns (SELECT column_a, column_b, column_c, column_d) and not ids? Look forward to playing around with it if you end up making it open source. Keep up the good work.
Sandy Says:
July 10th, 2008 at 5:47 pm
Worth looking at?

http://richinternetapplications.com.au/blog/2007/11/23/15-minutes-to-myob-web-reporting-with-clearbi-and-adobe-flash-player/
Mike Says:
July 14th, 2008 at 7:51 am
IDs are rows in the columns, so, for example, you’d have something like:
```
id    part_number    description
1     TOPB200        Tait T5000 Battery
2     TOPA_CH-201    Tait T5000 Fast Desktop Charger
```
The MYOB import/export functions in reality are probably just as useful as the ODBC driver, it’s just far easier to do things programmatically with ODBC.

That link looks interesting, I haven’t seen that software before. Looks like an app that’ll help make some custom reports if MYOB doesn’t already provide what you want.
Anne Says:
August 11th, 2008 at 7:21 pm
Hi,

I don’t know anything about code etc, I hope it is OK to drop in here and ask another but related question:

I am using MYOB at work for raising purchase orders and sale confirmations. My main problem is, that I am missing an advanced search feature in MYOB.

The products are fairly specified and customised, and there is no effective information database (and won’t be, for various reasons). Each new order is based on the specifications from the similar previous orders, which is, same customer same product. So to say, old orders serve as manuals for new orders.

In MYOB, I can ask for a list of a specific customer’s orders in a specific period, or a suppliers ditto, or a certain job number, but i can’t ask for a list of certain product specifications from a specific customers. Therefore, I will have to open up a number of old orders up one by one from a list looking for the combination i am after – that chews up MY time.

Anyone knows a way around this? An ad-on search-application for MYOB, would that be impossible?

Anyone else has the same problem?
I would appreciate good ideas.

Anne.
Mike Says:
August 12th, 2008 at 2:32 pm
G’day Anne,

I’m afraid I don’t know any easy search options with MYOB. One thing we used to do so we could search things we invoiced out, was to print the days invoices into a PDF file via something like PDF reDirect, then using a desktop search engine like Copernic that’s been setup to search the directory where we put the PDFs.

Basically we could then type what we wanted to search for and find the invoices that had the search term. There’s a few problems with the approach though, you have to make sure you print the invoices to your normal printer first, otherwise if you print them to PDF first, then they get marked as printed and you won’t be sure if you’ve printed them normally.

It’s a kludge, the other option is to read the data out of MYOB and into a database that does support searching, though that’s going to require someone who knows how to use MYOB ODBC, again, it’s kludgy.

Unfortunately it seems if you want to do anything to extend MYOB it ends up being a horrible hack.

Hope you can find something that works!
Volkan Says:
June 14th, 2009 at 8:44 pm
G’day Mike,

I was looking for some info in the net re: tapping into MYOB with ruby/rails and came across your post.
Was wondering if you did get around connecting to MYOB and automating the whole invoicing process?

Regards,

Volkan
Mike Says:
June 18th, 2009 at 12:09 pm
G’day,

I have got it basically working. I just have a page with checkboxes that I tick off jobs to be invoiced.

It works ok, but I want to try and get it working with JRuby and JDBC rather than the Ruby ODBC thing. To be honest I haven’t devoted too much time to it yet, with the end of financial year coming up.

So basically, yes, it works, but I’m not happy with how it works using ActiveRecord, so I’m looking into something like JRuby and Sequel to make something neater.
Daniel Heath Says:
December 21st, 2009 at 8:39 am
If you get a chance, would you publish the code?
I know you’ve said it’s not of great quality, but you and Pat Allen are the only sources I’ve been able to find for info on this.
One working code example (however hard to read) is 1000 times more useful than the (virtually nonexistant) documentation MYOB provide.
Mike Says:
December 23rd, 2009 at 10:35 am
At the moment I’m working with using Sequel and JRuby, if you check my most recent post (which is a bit old now, whoops).

I give a very brief example, I am going to work this out, as I mean to convert the invoicing system to use this, as Sequel and JODBC seem to work much better than trying to get ActiveRecord to play nice with ODBC.