MoneyWorks Datacentre REST API

Synopsis

The MoneyWorks Datacentre REST API provides a simple, platform-neutral, stateless network interface to MoneyWorks Datacentre.

Description

The MoneyWorks REST API is hosted on a MoneyWorks Datacentre server. The server may be self-hosted on-premises, self-hosted on a cloud VM, or hosted by Cognito as a MoneyWorks Now instance. Therefore there is no single URL for the REST API. On a self-hosted server, if the API is to be accessed from the Internet, the network administrator will need to ensure a static IP and port-forwarding for the REST port on the router.

The MoneyWorks Datacentre REST interface is configurable by the server administrator. If enabled, the REST API runs on port 6710 by default, but may be configured to run on a different port.

The REST API should use TLS (https) provided the server administrator configures it to do so and supplies an appropriate certificate. Unencrypted access (http) should only be used on a trusted private network.

Internally, the server uses MoneyWorks command-line instances to service REST requests (hence the similarity of the REST API to the CLI API). It keeps a pool of logged-in instances which stay alive for 30 seconds or so, so the latency cost (on the server) of making multiple requests over a short space of time is low. Note that sometimes latency for a request may be high if the database needs to be opened to service the request (MoneyWorks databases do not stay open when no users are logged in, so periodic polling via REST with a frequency less than about 10 minutes is strongly discouraged as it can cause unreasonably high server load).

Authentication

MoneyWorks Datacentre REST API uses Basic Password authentication.

MoneyWorks Datacentre servers can be configured with single-level (document password only) or 2-level (folder password + document password) login requirements. In the latter case, the Server: header will include Login_Required.

The MoneyWorks administrator should create a user in MoneyWorks specific to the REST client. To deny access to the REST client in future, simply delete that user or change the password on it.

If folder login is not required (Login_required is not present in the Server: header), then the only authentication required is a document username and the password for that user. This should be provided in an Authorization: header with the method Basic for realm Document i.e: a Base64-encoded concatenation of username:Document:password. E.g. if the username is "Robot" and the password is "s3cret", then the authorization data will be base64-encoded Robot:Document:s3cret

Authorization: Basic Um9ib3Q6RG9jdW1lbnQ6czNjcmV0

If the server is set up to require folder-level login (The "Require Folder Name and Password to Connect (ASP mode)" option in the console; the Server: header string will include Login_Required. This is always the case on a MoneyWorks Now server), then you must additionally supply a folder name and folder password Authorization header for the realm "Datacentre". If the document is at the top level of the server root, then the username should be root. If the document is in a folder named Some Folder with a password soMe-p4as5word, then the additional header will be base64-encoded "Some Folder:Datacentre:soMe-p4as5word":

Authorization: Basic U29tZSBGb2xkZXI6RGF0YWNlbnRyZTpzb01lLXA0YXM1d29yZA==

With Login_Required, you must supply both Authorization headers. They can be separate headers or you may concatenate them into one header separated by a comma and a space:

Authorization: Basic Um9ib3Q6RG9jdW1lbnQ6czNjcmV0
Authorization: Basic U29tZSBGb2xkZXI6RGF0YWNlbnRyZTpzb01lLXA0YXM1d29yZA==

or

Authorization: Basic Um9ib3Q6RG9jdW1lbnQ6czNjcmV0, Basic U29tZSBGb2xkZXI6RGF0YWNlbnRyZTpzb01lLXA0YXM1d29yZA==

The order does not matter.

CURL command line URL and authentication examples

Doc in subfolder with spaces in name, all credentials in explicit headers - RECOMMENDED

curl -H "Authorization: Basic `echo -n 'Folder/sub folder:Datacentre:FPASS' | openssl base64`" -H "Authorization: Basic `echo -n 'Admin:Document:DPASS' | openssl base64`" "https://example.moneyworks.net.nz:6710/REST/Folder%2fsub%20folder%2fDocument.moneyworks/export/table=login&format=xml" 

Doc in ubfolder with spaces in name, folder credentials in explicit header, document credentials in URL- NOT RECOMMENDED

curl -H "Authorization: Basic `echo -n 'Folder/sub folder:Datacentre:FPASS' | openssl base64`" "https://example.moneyworks.net.nz:6710/REST/Admin:DPASS@Folder%2fsub%20folder%2fDocument.moneyworks/export/table=login&format=xml" 

Doc in subfolder with spaces in name, folder credentials parsed by UA, document credentials in URL - NOT RECOMMENDED

curl "https://Folder%2fsub%20folder:FPASS@example.moneyworks.net.nz:6710/REST/Admin:DPASS@Folder%2fsome%20new%20folder%2fDocument.moneyworks/export/table=login&format=xml" 

API

The URIs for the REST API generally take the form

https://server:6710/REST/Document_Path/command?param=value

for requests that operate on a database document, or:

https://server:6710/REST/server/command?param=value

For simple server enquires (version, list).

The document path/name must be URL-encoded. If the document is located in a subfolder on the server, the document path will include path separators url-encoded (%2f) e.g.

https://server:6710/REST/Folder%2fSubfolder%2fThe%20Document.moneyworks/command?param=value

If the document is on a private server and is at the root level, then you just need the name:

https://server:6710/REST/The%20Document.moneyworks/command?param=value

You shoud consider URI components to be case sensitive. Be careful to match the case of the document name and path. Some servers may tolerate case mismatch in some parts of the URI and may tolerate alternative path separators such as backslash. Do not rely on this tolerance.

The REST endpoints are effectively command verbs. Available commands are:

version, list, export, import, evaluate, post, doreport, doform, and image

Simple server information GET requests (version, list) do not require a document name or parameters. The document name can either be empty or server if your UA does not permit double slashes.

In the example server responses below, irrelevant headers are omitted for clarity.

Server information commands

version

Get the server version number.

GET /REST/server/version

Does not require any login credentials. Returns the Datacentre version number as text/plain. e.g.:

HTTP/1.1 200 OK
Server: MoneyWorks_Datacentre/9.0 REST/9.0 Login_Required
Content-Type: text/plain

9.0

The version is also supplied in the Server header of every request.

Iff the server requires per-folder login, then the string Login_Required will appear in the Server header of the response.

list

List the documents (databases) on the server.

GET /REST/server/list HTTP/1.1
Authorization: Basic cm9vdDpEYXRhY2VudGU6UjAwdC1wYXNzd29yZA==

Returns a list of the available documents in the server or a subfolder thereof as text/xml. e.g.:

HTTP/1.1 200 OK
Content-Type: text/xml

<?xml version="1.0"?>
<documents>
        <document>Acme.moneyworks</document>
        <document>CognitoAccounts.moneyworks</document>
</documents>

If you authenticate with a subfolder password instead of as root, you will only get the documents within that subfolder

GET /REST/server/list HTTP/1.1
Authorization: Basic U3ViZm9sZGVyOkRhdGFjZW50cmU6cDRzNXcwcmQ=

This command can also be used with a document. See below.

Note: DO NOT use list as an "is the server there" test. It is relatively expensive to service.

Requests that operate on a document

These must include the document name (case-sensitive).

https://server:6710/REST/DocumentName.moneyworks/command?param=value

DocumentName is the name of the MoneyWorks document in the Datacentre that you are accessing.

If the document is in a subfolder (rather than at the root level of the server), the document name must include the path to the document. Non-alphanumeric characters in the URL should be url-encoded by converting to %-prefixed hexadecimal. This includes any / path separators if the document is in a subfolder (/ = %2f).

Credentials in the URL: if you are using a very stupid user agent that cannot supply headers with your requests (Filemaker Go... looking at you) then you may prefix the document name with login credentials in the form user:password@ and the server will parse these out of the URL. This is obviously not good practice. The login credentials will be logged on the server. Such user agents will also usually parse out a user:password@ preceding the domain name of the server in the URL and add an Authorization header for you (with no realm specified). The server will interpret this as a Datacentre realm credential or Document realm credential depending on the whether folder login is required or not.

Platform-specific path separators: You should use %2f as a path separator in the document path. This will work for all servers. You can use \ (unescaped backslash) as a path separator if the server is hosted on Windows, but you should not, because if the operator migrates the server to another platform, your requests will stop working.

Backwards compatibility: if your document name ends with .mwd7 (the MoneyWorks 7 file extension), the server will automatically convert that to .moneyworks.

Requests with parameters

Params sections are a list of named parameters in the form paramName=paramValue, separated by ampersands. ParamValues must be url-encoded. Note that characters such as = in the paramValue that are not normally url-encoded must be, since they are syntactically significant in the parameter list. i.e encode = as %3d

Parameters follow the command in the URL separated by either a ? or a /.

e.g.

GET /REST/DocumentName.moneyworks/export?table=login&format=xml&search=Name%3d%22John%22

export

This is the principle means of extracting information from the database.

GET /REST/DocumentName.moneyworks/export

Parameters for export:

table=tablename —required, (see Appendix A of manual for table names)

search=expr —optional, url-encoded search expression. All records if omitted. You must use the MoneyWorks-specific search syntax to find records. For information on MoneyWorks search expressions see Find by Formula and, for advanced cross-table searching MoneyWorks Relational expressions in the MoneyWorks manual. Don't forget to URL-encode the expression.

format=xml|xml-terse|xml-verbose|format-expr —optional, may be omitted for canonical text export

sort=expr —optional, can be simply a field name or an arbitrary expression

direction=ascending|descending —optional, direction of the sort

start=N —optional, start offset when format is xml

limit=N —optional, limit of number of records returned when format is xml

If the search parameter is omitted, you'll get the entire table. BEWARE: it may be very large. Downloading entire tables without good reason should be avoided. Use the start and limit parameters to page through "windows" on the data.

Result will be text/plain or text/xml as per the requested format.

The XML export format is fully compatible with the XML import format. xml-terse omits blank/zero values and non-importable system fields and detail line items for transactions. The xml-verbose format includes that data, but with a system="true" attribute.

A format expression should be fully url-encoded. It defines the export format with anything inside square brackets considered an expression to be evaluated. Anything outside square brackets will be output verbatim for each record.

e.g. XML output

GET /REST/Acme.moneyworks/export/table=name&search=Category1%3d%22NORTH%22&limit=2&format=xml

produces

HTTP/1.1 200 OK
Content-Type: text/xml

<?xml version="1.0"?>
<table name="Name" count="2" start="0" found="6">
        <name>
                <code>AUTUMN</code>
                <name>Autumn Fabrix Ltd</name>
                ...
                <payaccount>2500</payaccount>
                <email>accounts@autumn.co</email>
                <productpricing>A</productpricing>
        </name>
        <name>
                <code>BROWN</code>
                <name>Brown Suppliers</name>
                <contact>Bronwyn</contact>
                <address1>PO Box 12</address1>
                ...
                <email2>fred@brown.co</email2>
                <productpricing>A</productpricing>
                <receiptmethod>2</receiptmethod>
        </name>
</table>

Canonical plain text output (default)

GET /REST/Admin:fred@Acme.moneyworks/export/table=name&search=Category1%3d%22NORTH%22 

produces

HTTP/1.1 200 OK
Content-Type: text/plain

0       2       24/09/11 7:44:58 PM     AUTUMN  Autumn Fabrix Ltd       Allan     ...
1       3       31/01/11 4:58:07 PM     BROWN   Brown Suppliers         Bronwyn   ...
4       6       31/01/11 4:35:56 PM     SMITH   J Smith & Sons Ltd      Simon     ...
7       9       31/01/11 4:58:28 PM     WHITE   White Contractors       Grey      ...
8       10      31/01/11 3:58:09 PM     WINTER  The Winter Bakery       William   ...
21      32      31/01/11 4:21:45 PM     BROWNX  Brown Suppliers         Bronwyn   ...

Formatted output, using format string [Code], [Name]\n

GET /REST/Admin:fred@Acme.moneyworks/export/table=name&format=%5BCode%5D%2C%20%5BName%5D\n

produces

AUTUMN, Autumn Fabrix Ltd
BROWN, Brown Suppliers
BSUPP, Beetle Suppliers Limited
CANON, Canon Photocopiers
CSUPP, Casbah Suppliers Int
GREEN, Green's Garden Depot
LIST, Listener
NEWHER, Newton Herald
NEWPOW, Newton Power and Electric
NEWPROP, Newton Properties

import

Create (or update) records in the database. Method must be POST.

POST /REST/DocumentName.moneyworks/import

Optional Parameters:

return_seq=true —get seqnum of last record updated, else get created: N; updated: M

Data to be imported must be XML. The structure of the XML will generally that provided by the xml-terse export format. Further information on the required structure of the XML can be found at XML Importing in the manual, and information on importing data generally: Exporting & Importing

Supply the XML to be imported as the POST payload.

It is important to note that if using the work-it-out="true" attribute for fields to be imported, that the prerequisite data for calculating the field value must precede the calculated field in the XML.

MoneyWorks will not import invalid records. You should prevalidate your data before attempting to import it, rather than relying on the error messages from the import command to be meaningful to end users who have supplied invalid data (such as an invoice for a non-existent customer, or that does not add up).

The usual response is text/plain in the form

created N; updated M

Where N and M are the number of records created or updated, respectively. If you supply the return_seq=true parameter, then the response is text/plain containing the decimal representation of the last sequence number created or updated (not all tables respond to this option).

If there are errors in the data, the response will begin with the text [ERROR] followed by some information about the error(s). No records will be imported or updated in this case.

Extra import behaviours (such as posting the imported transactions) may be specified by passing the required parameters as attributes to the table element in the xml data. See XML Importing.

evaluate

Evaluate an expression

GET /REST/DocumentName.moneyworks/evaluate/expr=expression

Evaluates the given expression in the context of the document and returns the result. The expression should be fully url-encoded. The result will be text/plain. If you only need one data point from the database, this is more efficient than an export.

e.g.

GET /REST/Acme.moneyworks/evaluate/expr=Today%28%29 
HTTP/1.1 200 OK
Content-Type: text/plain

31/01/11

e.g.

GET /REST/Acme.moneyworks/evaluate/expr=Lookup%28%22SPRING%22%2C%22Name.DBalance%22%29 
HTTP/1.1 200 OK
Content-Type: text/plain

5678.12

post

Post an existing transaction, identified by its sequence number. Note the method must also be POST, since the database is changed by this operation.

POST /REST/DocumentName.moneyworks/post/seqnum=sequencenumber 

Posts the transaction. Sequence number should be decimal, without thousands separators. Result will be text/plain status, either "OK" or "not posted".

doreport

Run a report

GET /REST/DocumentName.moneyworks/doreport

Parameters:

report=name — required, the name of the report, which must exist on the server (either a standard report, or in the Custom Plugins folder for the document)

format=html|text|pdf

from=yyyymmdd|nnnn

to=yyyymmdd|nnnn

font=fontname

size=pts — optional, font size in points

leading=pts — optional, extra spacing between lines (only relevant if output is PDF)

title=heading — the title to appear at the top of each page.

control-id=value — custom control values

The report can reside in custom or standard plugins on the server. All params except report are optional.

Default format is text, delivered as text/plain. You will probably usually want to specify html or pdf. From and To dates can be ISO dates (which effectively denote a period range) or period numbers.

User-defined identifiers will be entered into the name table and can be used to specify custom control values by name (which some custom reports may require). The proper control-ids to use may be obtained using the list command (see below).

Example

GET /REST/doreport/report=Balance%20Sheet&format=html&leading=8&font=Verdana&size=10 
HTTP/1.1 200 OK
Content-Type: text/html

<HTML>
<HEAD>
<META NAME=GENERATOR CONTENT="MoneyWorks">
<style type="text/css">
...
</HEAD>
<BODY style="background-color: #f7f6f2;">
<div>
<div>
... etc

list

When used for a document, the list command provides information about installed and available reports or forms.
Parameters

folder=reports|forms —one of these is required

filter=user|standard|all —default is all

For folder=forms, the type may be specified

type=invc|stmt|prod|job_|remt|rept —default is invc

For folder=reports, a report name may be specified to get details of the report name=reportname

GET /REST/Acme.moneyworks/list/folder=forms 
HTTP/1.1 200 OK
Content-Type: text/xml

<?xml version="1.0"?>
<plugins type="invc">
        <form>
                <name>Business Check 104 (US)</name>
        </form>
        <form>
                <name>Business Check 105 (US)</name>
        </form>
        ...

or

GET /REST/Acme.moneyworks/list/folder=reports 
HTTP/1.1 200 OK
Content-Type: text/xml

<?xml version="1.0"?>
<plugins type="crep">
        <report>
                <name>Balance Sheet</name>
        </report>
        <report>
                <name>Bank Balances</name>
        </report>
        ...

Querying for parameter details of a specific report. These are returned in html-compatible format. Theid of the input elements should be used as parameter names to the doreport command. Boolean parameters should be passed as 0 or 1. Select parameters should be passed the selected value.

Users for whom the report is signed are indicated in the signatures element. If you are connecting as a user who does not have privileges to run unsigned reports, then your username must appear in the signatures block, or else doreport will fail with a 403 for the report.

GET /REST/Acme.moneyworks/list/folder=reports&name=Bank%20Balances 
HTTP/1.1 200 OK
Content-Type: text/xml

<?xml version="1.0"?>
<plugins type="crep">
        <report>
                <name>Bank Balances</name>
                <signatures>
                        <user>Admin2</user>
                </signatures>
                <controls>
                        <div>
                                <input id="Omit_Zero_Balances" type="checkbox" />
                                <label for="Omit_Zero_Balances">Omit Zero Balances</label>
                        </div>
                        <div>
                                <input id="Include_Unposted" type="checkbox" />
                                <label for="Include_Unposted">Include Unposted</label>
                        </div>
                        <div>
                                <input id="Print_Movements" type="checkbox" />
                                <label for="Print_Movements">Print Movements</label>
                        </div>
                        <div>
                                <input id="Cash_Basis" type="checkbox" />
                                <label for="Cash_Basis">Cash Basis</label>
                        </div>
                        <div>
                                <input id="Show_Departments" type="checkbox" />
                                <label for="Show_Departments">Show Departments</label>
                        </div>
                        <div>
                                <label for="sort">Sort</label>
                                <select id="sort">
                                        <option value="2">Code</option>
                                        <option value="3">Description</option>
                                        <option value="4">Accountant Code</option>
                                </select>
                        </div>
                        <div>
                                <label for="from">Period</label>
                                <select id="from">
                                        <option value="112">Mar:2008/09</option>
                                        ...
                                        <option value="310">Jan:2010/11</option>
                                </select>
                        </div>
                </controls>
        </report>
</plugins>

Note that you can use the <controls> element more or less directly in an HTML page to collect settings:










doform

Generate a PDF of a form

GET /REST/DocumentName.moneyworks/doform

Parameters

form=name

search=expr

Message=messagetext

Print_Copy=1|0

Include_Remit=1|0

Stmt_Date=yyyymmdd

Omit_Zero=1|0

Omit_Credit=1|0

The form can reside in custom or standard plugins on the server. All params except form and searchare optional.

Output format is pdf, delivered as application/pdf.

Example

GET REST/Acme.moneyworks/doform/form=Plain%20Ruled%20Invoice&search=sequencenumber%3d1686&message=Thanks

image

Use GET to retrieve an image.

GET /REST/DocumentName.moneyworks/image/product=code 
GET /REST/DocumentName.moneyworks/image/transaction=seqnum 
GET /REST/DocumentName.moneyworks/image/key=ident 

Retrieves a product or transaction image. Response will be image/png, image/jpg or application/pdf.

Use PUT to upload an image and attach to an existing transaction or product. Any existing image will be replaced.

PUT /REST/DocumentName.moneyworks/image/product=code 
PUT /REST/DocumentName.moneyworks/image/transaction=seqnum 

Note that the PUT data must be binary (if using curl use the --data-binary option) and content type should be image/png or image/jpg for a product image. For a transaction image it may also be application/pdf. In actual fact, a Content-Type of application/octet-stream is fine, just make sure you are PUTting binary data.

The transaction flags field will be updated to indicate that the transaction has an image, provided that no user has the transaction locked. If the transaction is locked, an error is returned, even though the image has been uploaded (and may have replaced an existing image). If the transaction is locked, it may be that a user is editing the transaction and will subsequently overwrite your image when they save the transaction.

Concurrent users and availability

Most REST requests are performed by a worker process on the server. This process logs into the database with the credentials you provide. It may consume one of the concurrent logins allowed for the server or folder account. If there are no concurrent logins available, then the request will fail.

If the server is configured with a dedicated REST availability serial number, then this will never be a problem. If it is not, then you should take the possibility of concurrent login saturation into account.

Note also that, by default, these worker processes linger for a short time (on the order of 10-30 seconds), so that a subsequent request with the same credentials can be serviced much faster. These lingering processes will continue to consume a concurrent login. If you do not wish this to happen, as of v7.1.5 you can add a parameter no_linger=true to your requests to cause the worker process to die immediately after servicing your request (this will allow a subsequent request with different credentials to succeed where concurrent logins are in short supply).

History

MoneyWorks REST interface first appeared in MoneyWorks Datacentre 6.1

HTTPS support was added in v7.1

HTTP/1.1 support was added in v8.0

Posted in REST | Comments Off on MoneyWorks Datacentre REST API