Reference number allocation

Posted on 22 June, 2012 by Rowan

We sometimes get queries about gaps in reference number sequences. Why would MoneyWorks be so dumb as to skip reference numbers?

In fact, reference number allocation is not that trivial.

Consider the situation where you open 5 new invoice windows. Each one gets an invoice number. Now close them all in random order by clicking Cancel. MoneyWorks has to do something with those invoice numbers, but what? If you cancelled them in reverse order of allocation, instead of random order, then they would go back into the sequence. Unless, that is, you are not the only user in a multi-user situation. Then it depends on whether anyone else has opened a new invoice window in the intervening time and taken a number from the sequence. If you did not close the windows in the exact reverse order that you opened them, then the relinquished "unused" reference numbers become available for reuse in a random order. The "Next Reference Number" that you would see in the preferences no longer has much meaning.

Since MoneyWorks does not have a special file in the database just for storing relinquished reference numbers, what it does is remember in your session what numbers you have relinquished. The stack of relinquished numbers is only kept until you quit or close the file. Within that time, if you make a new transaction then a number from your relinquished number stack will be used.

If you quit, you lose those numbers because they are only remembered for the session and the preference allocation number has moved on.

This happens even in single user mode due to the ability to have multiple open transaction windows. In multi-user each user might end up with a cache of reference numbers in this way.

Posted in Database, Esoterica, Networking | Comments Off on Reference number allocation

XML data exchange

Posted on 25 May, 2012 by Rowan

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"

    • -- where D is a dollar amount. 0.02 is the default

  • 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
     

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>
Posted in COM/VBS, Database, Esoterica, MWScript, REST, XML | Comments Off on XML data exchange

Report Writer: For loop redux

Posted on 24 May, 2012 by Rowan

The For loop in the MoneyWorks report writer can be used in several different ways:

  • Iterate over records in a table in the database. You select the table and specify a search expression to select records, an optional sort (up to two fields, each ascending or descending). The loop identifier is the current record whose fields can be accessed as Ident.fieldName. Ident on its own is a 1-based index.
    The search expression can be relational (using [table:search]... notation).
    In a nested loop, a relational search expression using the push operator can be explicitly optimised with a double push (^^). Use this to indicate that the initial term(s) of the search are loop-invariant. MoneyWorks will save the result the first time round and will not re-execute that part of the search. e.g.:
    [detail:detail.period = per]^^[Detail:Detail.Account = acc.concat]*
    Note that this rather difficult to understand notation has been superseded in 6.1 by CreateSelection/IntersectSelection.
     
  • Iterate over a range of integers, specified as a List defined as start...finish (e.g. 1...20). The loop identifer takes on the value of each integer. The order is always increasing. The list definition can be constructed as a string result of an expression (e.g. ="1..."+VAR)
     
  • Iterate over a comma-delimited list. The loop identifier takes on each value in the list, in order. The list can be the result of an expression.
     
  • Iterate over a newline-delimited list. In this case, the list is always specified as an expression, beginning with an = sign. The loop identifier takes on the value of each line in the resulting text. Note that if lines contain tabs, these are respected if you output the line in a heading part (each tab-delimited value appears in a column).
     
  • Iterate over a table where the selection is specified by a Selection type variable. New in v6.1 are the CreateSelection() and IntersectSelection() functions. These take a search expression and optional sort expression.
    =CreateSelection("tablename","searchexpression" [,"sortepxr"][, descend=0])
    The for-loop then behaves as for the normal records in table case. The advantage is that nested loops can be optimised by saving the results of time consuming searches in variables. You also have greater control over sorting. Naturally the table selected for the for-loop must match the tablename with which the selection is constructed.
    Example: Ledger Report 4 (with createselection).crep
     
  • Iterate over a text file. A list specified as the text file://pathname will cause the file to be opened and its contents treated as a newline-delimited list. If the named file is not found, a File Open dialog box will be displayed. This is, of course, platform-dependent if you do specify a path.
     
  • Iterate over a text file downloaded from a URL (new in 6.1.1). As above, but the text file is specified as a URL http://resource. This is not platform-dependent.
     
Posted in Reporting | Comments Off on Report Writer: For loop redux

useDefaultPrinter

Posted on 23 May, 2012 by Rowan

By default, on Windows, MoneyWorks will attempt to print to the same printer that any given report, form, or list was printed to last time.

If this is not desirable, there is a preference for it, but there is no UI to set it. You can set it in RegEdit.

The preference is called useDefaultPrinter. Set it to 1 for all printing to go to the default printer.

Make this change while MoneyWorks is not running.

Recent versions of Mac OS X prevent applications from choosing a printer. Apple thinks that that is something that users should always have to do themselves.

Posted in Esoterica | Comments Off on useDefaultPrinter

OS X Server and folder permissions

Posted on 8 May, 2012 by Rowan

It is strongly recommended that Datacentre data is stored in the standard location (/Library/MoneyWorks/Documents).

If you store data in another location it is important that you ensure that the entire path to that location is searchable by the server user (moneyworks_server).

  1. The Console will only automatically set ownership of the data folder if it contains fewer than 10 non-MoneyWorks files. For more populous data folders, it will need to be done manually on the command line with sudo chown -R moneyworks_server:admin /Path/To/Data/Folder
    OK, this one is down to us being too cowardly to set recursive permissions for a folder that doesn't look like it is really just a folder of MoneyWorks documents.

    2.  

  2. Every directory in the path to the folder must be world-searchable (because those directories will legitimately have different ownership). This also needs to be checked and set on the command line. Each directory in the hierarchy needs to have sudo chmod a+x /Path/To/Data
    sudo chmod a+x /Path/To
    sudo chmod a+x /Path
    If they are not world-searchable, that basically translates as "MoneyWorks is not allowed to see this folder that the MoneyWorks files are in".

    3.  

  3. If the data is not somewhere on the boot volume, then the data volumes's Temporary Items (or possibly .Temporary Items) directory must be either owned by the staff group or it must be world-readable-writable-executable (searchable).sudo chmod a+rwx /Volumes/MyDrive/Temporary\ Items
Posted in Esoterica, Servers | Comments Off on OS X Server and folder permissions

DNS, if available, must be correct

Posted on 7 May, 2012 by Rowan

Had an instance of mysterious "permissions error" when logging in to a 6.1 Mac server. Every time. Permissions were all correct.

After some investigation, the problem turned out to be that the server had an incorrect DNS entry. Since MoneyWorks Datacentre needs to open network connections to itself as part of the login process, it is necessary for any DNS entry for the server to be correct. Evidently, earlier versions did not mind incorrect DNS but 6.1 does (possibly due to the changeover to BSD Sockets from the deprecated Open Transport API).

If you are seeing this error, and you know that permissions are correct (have run Console to set documents folder ownership to moneyworks_server), then please check the DNS.

If the server has a DNS name (e.g. accounts.company.com), open terminal on the server and try:

host accounts.company.com

and

ifconfig

The IP address from host should be the same as the inet address for interface (typically) en0.

If the server is behind a NAT device and has DNS for its external IP address, then ensure that there is a hosts file entry so that local hostname lookups resolve to the internal IP address.

Posted in Esoterica, Networking, Servers | Comments Off on DNS, if available, must be correct

Operand Type Gotcha

Posted on 13 February, 2012 by Rowan

The Balance History from Date form previously had a bug in the case where a credit balance in the running balance column then gets a further credit added to it. The result is that the credit balance behaves as if it were positive and the running balance is thrown off.

The reason for this turns out to be that the running balance (Sub_Total column), uses values from other columns whose result type is inconsistent.

Sub_Total = OPENING + SubTotal("List.Debit") - SubTotal("List.Credit") + Debit - Credit

The Debit column is defined thus:

if(Transaction.AccRecMove >= 0, Transaction.AccRecMove, "")

i.e. when the transaction is a credit, the value of the Debit column is not a number, but a string: "". This is because we want the column to be blank in that case.

While the subtotals work just fine (they treat non-numeric values as zero), the second "+" in the Sub_Total formula is problematic when the Debit column is a textual value. This is because addition where one operand is text and the other is a number will convert both operands to text and perform a string concatenation†.

So

-100.35 + "" - 446.96

The left hand side of the addition is "promoted" to text so that it can be concatenated with the right hand side text "". The column format is used*.

"100.35 CR" - 446.96

Now we have a subtraction of a number from a text string. In this case, the text string on the left hand side is converted to a number, but this conversion is done by the operating system's number parser and does not respect the "CR" suffix — it just looks at the digits. The result is that the left hand side loses its negative sign.

100.35 - 446.96

And we get result 346.61 CR, when we should have got 547.31 CR.

In general, the safe thing to do is avoid doing any numeric arithmetic where one of the operands might be a text string. The solution in v6.1 is that the Balance History from Date form uses separate columns for calculating Debit and Credit and for displaying them.

The calculation (in a zero width column) is:

Debit = if(Transaction.AccRecMove >= 0, Transaction.AccRecMove, 0)

And the visible column is

Disp_Debit = if(Debit, Debit, "")

The Sub_Total column can then operate on values that are consistently numeric and need no unreliable text -> number conversions.

†possibly overloading the + operator for string concatenation as well as numeric addition was not the wsiest of ideas, but we are stuck with it.

*note that the simple fix to the problem is not to use the "CR" numeric format. a negative prefixed by a "-" is parsed fairly reliably (although that may be not be the case in all international number formats).

Posted in Esoterica | Comments Off on Operand Type Gotcha

Mapped drives

Posted on 31 January, 2012 by Rowan

All too often we find that Windows Datacentre admins configure Datacentre to use "mapped drive" paths for Backups (or even for the Documents root). In the latter case, they soon find out that it doesn't work. In the former, they probably won't find out until they need to retrieve a backup and find that there are none. Or, as happened recently, they try to save a backup down to a client (which requires that a backup be successfully made to the Backups folder on the server first). The result in that case is an error message suggesting that the server is misconfigured. Which it is.

The issue is that MoneyWorks Datacentre runs as a service, executing as the LocalSystem user, as services do by default. The LocalSystem user has no access to "mapped drive" network volumes, which are a hack that applies only to the individual logged in user on the machine. This is not a limitation of MoneyWorks; it is a design/security feature of Windows.

Since few people are aware of this fundamental limitation, the Datacentre Console on Windows, as of 6.1, detects that a path specified is in fact a mapped network drive and will disallow it and post a helpful coach tip.

It will only do this for the usual case where the service runs as the LocalSystem account. If the service has been configured to run as some other user, then the "is network drive" check will be skipped on the assumption that the administrator knows what they are doing.

FYI, we detect the configured user of the server using

sc qc "MoneyWorks Datacentre"

and look to see if SERVICE_START_NAME is LocalSystem. You can also determine this using the Service's LogOn tab in the Services control Panel.

Posted in Esoterica, Servers | Comments Off on Mapped drives

Relational Find for arbitrary link fields

Posted on 24 November, 2011 by Rowan

When you do a relational find for anything that does not have a link that shows up in the Find Related dialog box, then you must explicitly provide the link fields. A good example of this is when finding Ledger records in a report, starting from, say, Account. There are no Find Related links for the Ledger file because there isn't even a list where you can view the Ledger file.

Therefore

[account:group="DIV"][Ledger:Category="Blah"]        (wrong)

will not give you any results.

This is the correct way to specify a relational search from [Account] to [Ledger], explicitly specifying that the linking fields are Code and AccountCode:

[account.code:group="DIV"][Ledger.accountcode:Category="Blah"]
Posted in Database, Esoterica | Comments Off on Relational Find for arbitrary link fields

Accessing REST from PHP

Posted on 27 September, 2011 by Rowan

Example of using curl to issue an authenticated REST request.

<?php

    // Demonstrates two methods of including authorisation
    //      for accessing a resource
    // The resource in this case is an xml export of the account
    //      table of document Acme.moneyworks

    if($_GET["auth"] == "headers")  {
        $ch = curl_init();
        // set URL and other appropriate options
        curl_setopt($ch, CURLOPT_URL, "http://127.0.0.1:6710/REST/Acme.moneyworks/
                                        export/table=account&format=xml-terse");
        curl_setopt($ch, CURLOPT_HEADER, 0);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        // set Auth headers for Datacentre login, and document login
        $headers = array(
            "Authorization: Basic " . base64_encode("root:Datacentre:XXXX"),
            "Authorization: Basic " . base64_encode("Admin:Document:fred"));
        curl_setopt($ch, CURLOPT_HTTPHEADER, $headers); 
        $result = curl_exec($ch);
        curl_close($ch);
        }
    else if($_GET["auth"] == "inline")  {
        $ch = curl_init();
        // usernames and passwords are inline in the URL
        // ask for verbose output so we can see the difference
        curl_setopt($ch, CURLOPT_URL, "http://root:XXXX@127.0.0.1:6710/
                                      REST/Admin:fred@Acme.mwd6/export/
                                      table=account&format=xml-verbose");
        curl_setopt($ch, CURLOPT_HEADER, 0);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        $result = curl_exec($ch);
        curl_close($ch);
        }

    header("Content-Type: text/xml");
    echo $result;   
    ?>
Posted in REST | Comments Off on Accessing REST from PHP