Integrating Sage 50 with Ruby on Rails

Published 08 November 2016 under sage, ruby on rails

Many small businesses use Sage to manage their accounts and it can be really useful to automatically get stock levels from Sage and place orders directly from a website (in this example written in Ruby on Rails). Unfortunately, the Sage API isn't terribly well documented (to put it kindly) and there are a few pitfalls that need avoiding if you don't want to end up making Sage fall over in a heap.

One of the first things to note is that Sage 50 Accounts is not a cloud service so you'll need to make sure that it's being run somewhere your webserver can access it. We'll be using the SData v1.1 interface to talk to Sage.

In the following examples our Sage 50 Accounts is accessible at http://example.com:5493 (5493 is the default port for the SData service).

Getting a list of products

The URL for getting the list of products (called commodities by the Sage API) is as follows.

http://example.com:5493/sdata/accounts50/GCRM/-/commodities?format=json&count=50&startIndex=0&select=reference,onOrder,inStock,allocated,salesprice,name,type&where=updated ge @2016-07-06 17:00:00@

Let's disect the request:

• We're requesting JSON. The payload is generally quite a bit smaller but you can use the default XML if you prefer by removing the `format=json`.
• We specify some pagination parameters: a startIndex and count. These are really important, especially if you have over a few hundred products as Sage is likely to lock up if you request all of the products at once.
• We select the parameters we're interested in. The easiest way to get the list of parameters is to do a request without the `select` parameter and setting `count=1` in the URL.
• We specify we're only interested in products that have been updated since a given time (this will be useful later when we're syncing).

As part of the request you'll need to send the authentication parameters. In ruby this would look something like:

uri = URI.escape("http://example.com:5493/sdata/accounts50/GCRM/-/commodities?format=json&count=50&startIndex=0&select=reference,onOrder,inStock,allocated,salesprice,name,type&where=updated ge @2016-07-06 17:00:00@")

open(uri, http_basic_authentication: ["username", "password"]) do |result|
...
end

You should get back something like the following:

<U+FEFF>{
  "$descriptor": "Sage Accounts 50 | commodity - Site Name                                                  ",
  "$url": "http://example.com:5493/sdata/accounts50/GCRM/-/commodities",
  "$totalResults": 3527,
  "$startIndex": 1,
  "$itemsPerPage": 1,
  "$resources": [
    {
      "$url": "http://example.com:5493/sdata/accounts50/GCRM/{E4689CE6-901E-4D25-A865-DD9A5ACB051E}/commodities(cdd82072-2a4f-4ed8-9630-b43039a45f31)?format=atomentry",
      "$uuid": "cdd82072-2a4f-4ed8-9630-b43039a45f31",
      "$httpStatus": "OK",
      "$descriptor": "",
      "reference": "REF001",
      "name": "Product Name",
      "type": "Category",
      "inStock": 0.0,
      "allocated": 0.0,
      "onOrder": 0.0,
      "salesPrice": 9.35
    }
  ]
}

In order to parse the response as JSON we'll need to remove that special character at the beginning of the message using something like the following (where `result` is the response object from above):

JSON.parse(result.read.force_encoding("UTF-8").sub(/^\xEF\xBB\xBF/, ''))

Getting a list of orders

Similarly to getting products we can grab the list of sales orders:

http://example.com:5493/sdata/accounts50/GCRM/-/salesorders?format=json&count=50&startIndex=0&select=uuid,reference,status,date,CustomerId&where=updated ge @2016-07-06 17:00:00@

The SData API claims to support nesting the sales order lines within the sales order response but this doesn't work well when we want the product information for each sales order line. A more reliable way of getting the lines with associated product information is to use something like:

http://example.com:5493/sdata/accounts50/GCRM/-/salesorderlines?format=json&where=reference eq 'REF'&count=20&startIndex=0&select=reference,commodity/*,quantity,actualPrice&include=commodity&where=updated ge @2016-07-06 17:00:00@

Note that `REF` is the reference we got back from the salesorder request.