MoneyWorks 6.1 and later supports import and export of xml-formatted data. This removes the need to preconfigure an import map when building automated importing scripts (although you will still need to understand import maps). XML is the only import format supported by the REST interface.
For manually invoked XML import, there is an XML option to the Import menu, and you can also drag & drop .xml files or paste xml text into list windows to invoke an import.
XML files to be imported must be valid XML and must provide the necessary fields to specify valid record data for MoneyWorks. For example, if importing a transaction, the transaction line items must add up and agree with the transaction total; the transaction type and necessary fields such as Contra must be specified. This is no more or less that you would do with an Import Map. You can't just throw some partial data in and expect MoneyWorks to read your mind about what it means.
XML file format
The import format is compatible with the XML export format that MoneyWorks produces. You can export a table from the command line with, e.g:
moneyworks -e 'export table="transaction" format="xml"' document.moneyworks
This exports the entire transaction table to stdout.
The import file may contain a single <table> element, or may contain an <import> element with multiple <table> elements within it. The <table> elements can be for different tables.
The <table> element must have a name attribute specifying the target table for the import.
The <table> element will contain multiple record elements, whose element name will be the table name (e.g. <transaction>). Each record element will contain a set of field elements whose names are valid fieldnames from the MoneyWorks schema (e.g. <ourref>). See appendix A of the manual for table and field names.
As a special case, transaction elements will also contain a <subfile> element, which will contain multiple <detail> elements specifying the line items for the transaction.
i.e.
<?xml version="1.0"?> <table name="Transaction"> <transaction> <ourref>2116</ourref> <transdate>20101220</transdate> <duedate>20110120</duedate> <type>DII</type> <theirref>213544</theirref> <namecode>BROWN</namecode> <description>Widget Sales</description> <gross>1008.56</gross> <contra>1500</contra> <tofrom>Brown Suppliers</tofrom> <subfile name="Detail"> <detail> <detail.account>4000-1</detail.account> <detail.taxcode>G</detail.taxcode> <detail.gross>779.62</detail.gross> <detail.tax>86.62</detail.tax> <detail.net>693.00</detail.net> <detail.description>Chrome Taper Widget</detail.description> <detail.stockqty>7.000000</detail.stockqty> <detail.stockcode>CB200</detail.stockcode> <detail.costprice>35.000000</detail.costprice> <detail.unitprice>99.000000</detail.unitprice> <detail.saleunit>ea</detail.saleunit> </detail> etc...
Dates
Dates must be in YYYYMMDD format. Regional date formats are not recognised.
Numbers
Decimal numbers must use only a "." decimal separator.
Numbers must not use any thousands separator. Regional number formats are not recognised.
Calculated fields
The Work-it-Out facility for fields that support it can be invoked with an empty field name tag with the attibute work-it-out="true" (you can determine such support by checking the relevant field in the usual importing configuration dialog - if the field options support work-it-out there, then it is supported by the XML importer).
e.g:
<transdate work-it-out="true" />
Likewise, calculations can be done using the calculated attribute. In this case the text within the element is treated as an expression whose result is used as the actual import data for the element. If you are referencing imported fields, those fields must have appeared earlier in the xml file.
e.g:
<user1 calculated="true">Lookup(NameCode, "name.Category2")</user1>
Fields and subrecords with the attribute system="true" will be ignored by the importer.
Additional table attributes for controlling the import
Where appropriate, the same import options available in the regular import can be specified as attributes of the table element.
The following attributes may be specified for the transactions table. They correspond to the equivalent setting in the Transaction import options dialog or the parameter to the regular scripted import:
- create_names="true"
- create_jobs="true"
- rounding_tolerance="D"
- negate_in="true"
- negate_out="true"
- update="true" seqnum="N"
-- where N is a sequence number
- return_seq="true"
-- response of the import command will be a sequence number of last record imported instead of a success notification
- post="true"
-- for transactions, posts them after importing - discard="true" seqnum="N"
-- where N is a sequence number of unposted trans to be deleted. This usage is deprecated
-- where D is a dollar amount. 0.02 is the default
For Importing Names, Jobs, and Products (Items)
- update="true"
Payments on invoices
The XML format for importing payments on invoices is slightly non-obvious. The table name attribute for the import needs to be "payments", but the top-level records that you import are <transaction>, with subrecords of <payments>.
E.g:
<?xml version="1.0"?> <table name="payments"> <transaction> <type>CR</type> <namecode>GREEN</namecode> <ourref>123456</ourref> <transdate>20101030</transdate> <gross>380.25</gross> <contra>1001</contra> <subfile name="payments"> <payments> <invoiceid>2041</invoiceid> <amount>380.25</amount> </payments> </subfile> </transaction> </table>