MoneyWorks

MoneyWorks native scripting language

Rationale

Since v4.1, MoneyWorks has supported customisation via platform-specific attached helper scripts. On Mac, this was via a Helper.scpt Applescript in the Scripts folder, and on Windows via a slightly more convoluted mechanism to invoke a WSH (typically VBS) helper script.

However, other than the inherent difficulty of writing, debugging and deploying these platform-specific scripts, we observed time and again the significant drawback that the scripts were not portable across platforms. All too often the script author’s regular platform was not the same as the deployment platform, or the deployment site was multiplatform. Furthermore, these external scripting platforms were not a good fit for the particular needs of MoneyWorks customisation.

Finally, the mechanism only allowed for a single helper script to be deployed.

Our solution in MoneyWorks 7 is to provide an embedded scripting system.

External helper scripts (Applescript, COM) still work, but are deprecated in v7. It should be quite easy to port existing external scripts to MWScript.

Advantages:

• Platform neutral: Write once; deploy on either platform
• Fully embedded: Policy-enforcing scripts cannot be circumvented by removing script file.
• Faster. No IPC overhead and also considerable scope for optimisation
• Language closely aligned with purpose
• Very dynamic loading of scripts: Make a change, activate script and it's immediately enabled.
• Easy deployment: Scripts can be emailed to customers and installed by a double-click.

Audience

This document assumes familarity with scripting/programming languages.

Scope of scripts

There are two ways of getting native scripts loaded:

• Scripts are normally stored in the document and automatically loaded when the document is opened or connected to. All users share the same scripts.Document scripts can be activated or deactivated in the Script Editor window. Changes to scripts are automatically synced to the server when the script window is closed (other users will get the changes the next time they log in).To get user-interface events like ValidateField, you must install the script in the document.
• Script files (plain UTF-8 text files with a .mwscript file extension) can also be placed in the Scripts folder of Standard or Custom plugins. These files will be installed in the Commands menu at application startup (Standard plugins) or Document open (Custom Plugins). When the script is selected from the command menu it will be loaded, compiled, and  then its Load() handler and Unload() handlers will be called.Use this method of script loading if you need to open and close documents from your script (e.g. for consolidation across companies)

Syntax

The MWScript expression syntax is compatible with the existing MoneyWorks expressions. MWScript simply adds handlers and statements. Flow control is closely analogous to that provided by the report writer.

Newlines are syntactically significant. One statement per line.

As an introduction, here is a complete example script:

constant meta ="Script by YOUR NAME. http://YOUR_URL"

on Hello
	Alert("Hello World!")
end

on Load
	InstallMenuCommand("Hello World", "Hello")
end

To put the script into a document, you must be logged into the document with the Scripting Privilege.

1. Choose Show ➝ Scripts
2. Click the + in the sidebar to create a new script
3. Enter the name of the script ("Hello_World"). Script names do not have spaces in them.
4. Type or paste the script text into the editor pane.
5. Click Activate in the toolbar. Your script is compiled and loaded.

This example would be invokable by choosing its command from the Commands menu.

Now let's move on to a detailed examination of the language...

Types

MWScript supports the same types as are available in the report writer. As in the report writer, the language is very weakly typed (values are freely type-promoted as necessary).

Type Example

• Number 5.35
• Date '1/1/13' // note that single quotes are for dates, not text strings!
• Text "mwscript" or `also a string`
• Selection create via CreateSelection(tableName, search) function
• Table create via CreateTable() function
• Record created by looping over a selection with foreach

and also

• Associative Array create with the CreateArray() function

Associative arrays use the syntax arrayname[key], where key can be any text up to 31 characters, an integer, or a date (internally, integers and dates are represented as text formatted so that lexical sort order matches numeric/date sort order). Data values can be any other type.

let myArray = CreateArray()
let myArray[0] = "thing"
let myArray["mykey"] = myArray[0]
let myArray[’31/1/12’] = 500

In the current implementation,  insertion into an associative array is O(N). Retrieval is O(log N).

Arrays, like Tables, are always passed by reference, so if you assign an array to another variable, or pass it as a parameter to a function, you are not copying the array. If you want to copy an array, you must allocate a new array with CreateArray and explictly copy all of its members.

Properties

Properties are variables that last for the lifetime of the script. They are instantiated each time the script is loaded and discarded on unload.

Properties are defined outside of message handlers.

property mytextglobal = "qwertyuiopasdfghjklzxcvbnm"

If you need a property to be persistent across sessions, you must store the value in the database (the User2 table is appropriate for this, see the SetPersistent/GetPersistent functions). Load the value in the script’s Load handler, and store it in the Unload handler. Keep in mind that every user will be executing the script with their own copy of the properties, so you may need to take steps to stop one user from clobbering values stored by another user.

Constants

Constants are named values for use within the script.

Constants are defined outside of message handlers.

IMPORTANT: Every script must declare a constant with the name meta.

constant meta = "Script for Something by Your Name and your URL"

The meta contant will be used by MoneyWorks to identify your script to the user. It must be a string and it must not be empty.

Message Handlers

A message handler defines a callable function with optional named parameters and an optional return value. A message handler is a peer of the built-in intrinsic functions and can be used in expressions in the same way.

A message handler is defined by the keyword on, followed by the handler name and an optional list of parameter names. The handler body ends with the keyword end on a line by itself.

If a parameter required by your handler is not supplied by the caller, then a runtime error will result. Parameters are untyped, so if callers don’t provide a value of the correct type, you should convert the type yourself to the required type (usually using TextToNum or NumToText)

 

Comments

Comments use the C++ form. // for a to-end-of-line comment; /* and */ to begin and end a block comment that can span lines or within a line.

Assignment

The let statement assigns the result of an expression to a new or existing variable. If the variable has not previously been seen in the handler and is not a property, then it is implicitly declared as a local variable in the handler.

let myVar = Today() + 7

Conditionals

Conditional flow control is done with if, elseif, else, and endif. Zero or more elseif clauses are allowed for an if, followed by zero or one else clause, followed by an endif.

Short form:

if condition
    // do something
endif

And the long form:

if condition
    // do something
elseif anothercondition // can have any number of these
    // do something else
else // up to one of these
    // do other thing
endif

The long form effectively provides switch/case functionality.

While loops

General looping can do done with a while loop. Loops can make use of the break and continue statements for early exit or short circuit (just as in C-like languages, and the report writer).

while condition
    if special condition
        break // exit the loop
    endif
    if anothercondition
        continue // go back to top of loop, skipping do stuff
    endif
    // do stuff
endwhile

For loops

There are several kinds of for loops that operate on different kinds of collections or ranges. Each kind starts with the keyword foreach and ends with endfor.

The general form is

foreach loopcontrolvar in type_keyword expression

Foreach declares a local loop control variable whose scope is limited to the loop body. Type keywords can be:

• database table names (defining a loop over a selection of records). Available table names are: account, ledger, general, department, link, transaction, detail, log, taxrate, message, name, payments, product, job, build, jobsheet, bankrecs, autosplit, memo, user, user2, offledger, filter, stickies, lists, and login. The expression must yield a selection variable for the specified table. The loop control variable is a record, whose fields can be accessed by suffixing the variable name by a field name (e.g. rec.ourref). The variable used on its own will yield a 1-based index.
• the keyword text which will iterate over words or lines in comma or newline delimited text. The delimiter is determined automatically—if there is at least one newline, then iteration is by line, otherwise iteration is by comma. Every line of multiline text should terminate with a newline (including the last one!).
• the keyword textfile that will iterate over lines in a textfile loaded from the local filesystem using the "file://" scheme, or from an HTTP server using the "http://" or "https://" scheme. You can use the http scheme to access data from remote data sources (e.g. REST servers. It retrieves data using the GET operation only). If the path is supplied, it must be in the temp directory, the custom plugins directory, MoneyWorks app support directory, or have a file extension of .txt or .csv. If the path is specified by the user, it can be anywhere or have any extension (but obviously should be a text file). Anywhere outside these locations will need to be specified in a file open dialog box by the user (which will be automatically invoked for any invalid or empty path).
• the keyword array which will iterate over key values in an associative array variable;
• and finally no keyword but rather a parenthesis-enclosed pair of values defines a simple numeric range (with an optional step value). The values can be full expressions but must be numeric. If the finish value is less than the start value, no iterations will occur (unless the step is negative).

Foreach variants:

foreach rec in transaction CreateSelection("transaction", "status=`P`")
foreach rec in account someSelectionICreatedEarlier
foreach word in text "foo, bar, baz"
foreach line in textfile "file://"
foreach line in textfile "http://"
foreach key in array myArrayVar
foreach i in (0, 10) // integers
foreach i in (100, 0, -10) // integers with optional step

Range Example:

// log the numbers 1...100
foreach i in (1, 100)
    syslog(i)
endfor
// note that (100, 1) will iterate 0 times, unless you supply a negative step
// log the numbers 100, 90, 80, ... 0
foreach i in (100, 0, -10)
    syslog(i)
end for

Array Example:

// foreach in array iterates over keys; get the value by subscripting with the key
foreach key in array anArray
    syslog(key + " = " + anArray[key]) // key is always text
end for

List Example:

// comma-delimited text
foreach w in text "foo, bar, baz"
    syslog(w)
endfor
// newline-delimited text
foreach line in text "first\tline\nsecond\tline\nlast\tline\n"
    syslog(line)
end for

Selection Example:

// loop control is of record type; use dot-notation to access fields
// the naked loop contol variable is a 1-based index
foreach a in account CreateSelection("account", "code=`1@`")
    syslog("record #" + a + ": " + a.code + " " + a.description)
end for

Running Scripts

You can install multiple scripts in a document and enable or disable them individually.

Use the Show Scripts command to show and edit the scripts installed in a document. Note that only one user at a time can make changes to scripts.

You can add, delete and rename scripts (use the icons at the bottom of the sidebar).

Note: The script editor will use the Consolas font on Windows if it is installed, otherwise Courier New. On Mac, Menlo will be used if installed, otherwise Monaco.

Active scripts are loaded at login and unloaded at logout, and a script is also unloaded/reloaded when you click the Activate button in the script editor toolbar (the old version of the script is first unloaded if it was loaded, then the modified script is compiled and loaded).

You can keep inactive scripts in a document. They won't do anything until you activate them.

User-interface helper handlers with special names are called automatically for active scripts. Handlers that implement these standard messages (and variants thereof) will be called in all active document scripts.

The automatically called handlers are:

Load

The script has been loaded. Do any initialisation you need (such as loading persistent values from the database)

Unload

The script is about to be unloaded. Use this opportunity to save persistent values.

UserLoggedIn

The user has just logged in (network or local). You can use this opportunity to load user-specific state. In v7, you get this message even if password protection is not turned on. IN v7.1 and later, you can also use this handler to abort the login by returning 0. Older scripts will still work due to the default return value for any handler being 1.

UserLoggingOut

The user is logging out. You can use this opportunity to save user-specific state.

AllowPostTransactions (selection)

The user is about to (electively) post transactions. Return 0 to abort posting. If you return 0, none of the transactions in the selection will be posted.

E.g.

on AllowPostTransactions(toPost)
    foreach t in transaction toPost
        if t.EnteredBy <> Initials
            Alert("Can't post", "You can only post transactions that you entered")
            return 0
        endif
    endfor
    return 1
end

PostedTransactions (selection)

The user has just posted the transactions. This will be called for both electively and non-electively posted transactions.

Messages from windows: WindowRefs and WindowIDs

Window handler functions are passed an opaque windowRef identifying the specific instance of the window from which the message was sent. You can use this to access content of the specific window. Each kind of window is identified by a class name (or windowID) but this class name is not passed to the handler. Window handlers will usually be implemented with windowID-specific handler names incorporating the windowID (see Specific UI Element Handler Names), so its value is usually implicit. If you use a general handler, you can get the windowID from the windowRef using the GetWindowID(winRef) function.

Validate (windowRef)

Called when user clicks OK or Next on a record that has had changes made to it (if no fields have been changed, the handler may not be called). Use the windowRef to access information from the window.  The handler should return 1 (true) or 0 (false) to indicate whether the window content should be accepted. Returning 0 will prevent the window from closing. IMPORTANT: If you do this you should provide an explanation to the user via an alert or coachtip. Users will be very unhappy if they don't know why the OK button is not working!

Before (windowRef)

Called when a window opens or when a new record is loaded in the window (when the user clicks the Next or Prev button). For list windows, this will be called when the user changes the view in the sidebar.

After (windowRef)

Called when a window is closing or when a new record is about to be loaded into it. May not be called if no content was modified.

Cancel (windowRef)

Called when a window is closed via the Cancel button.

Close (windowRef)

Called when a window is closed. Use this to dispose any persistent data you may have created for the window (to "dispose" an array, assign 0 to it).

ValidateField (windowRef, fieldNameString, fieldValueString)

Called when a field is about to be exitted. Return 0 if the field value is not acceptable: this will keep the focus in the field. IMPORTANT: If you do this you should provide an explanation to the user via an alert or coachtip. Users will be very unhappy if they don't know why they can't exit a field!

ExitedField (windowRef, fieldNameString, fieldValueString)

Called when a field has been exitted.

ValidateCell (windowRef, listRef, rowNum, columnNum, cellValueString)

Called when a cell in an editable list (such as the detail line entry list) is about to be exitted. Row and column numbers are zero-based. Return 0 if the cell value is not acceptable: this will keep the focus in the cell. IMPORTANT: If you do this you should provide an explanation to the user via an alert or coachtip. Users will be very unhappy if they don't know why they can't exit a cell!

ExitedCell (windowRef, listRef, row, column, cellValueString)

Analogous to ExitedField for editable list cells.

For handlers that return a Boolean value where 0 indicates validation failure (Validate, ValidateField, ValidateCell) the first one that returns 0 will cause invocation of that handler name in all scripts to stop. To exit a field successfully, the validation handlers in all active scripts must return 1.

Specific UI Element Handler Names

Since common UI handlers tend to need to be implemented for specific window classes or fields, there is an easy way to implement these handlers for their specific target.

For the following handlers, you can write a handler that specifies its windowID class and optional additional scope in the handler name:

Validate:windowID:transtype

Before:windowID:transtype

After:windowID:transtype

Cancel:windowID:transtype

Close:windowID:transtype

ValidateField:windowID:fieldindent:transtype

ExitedField:windowID:fieldindent:transtype

ValidateCell:windowID:listname:columnname:transtype

ExitedCell:windowID:listname:columnname:transtype

The transtype specifier only applies to the Transaction entry window and will not be present for any other kind of window. Your handler will only be called if all of the specifiers match. To have your handler called more generally, simply use fewer specifiers (note that the specifiers are necessarily positional, so you can only reduce specificity by dropping specifiers from the end). If there are multiple handlers for the same message in your script that match, they will all be called, beginning with the most specific. E.g. if you have ValidateField:f_trans:e_user1:DI and also ValidateField:f_trans, they will both be called, in that order (assuming the first one does not return false, in which case the call sequence would stop at that point).

Example:

// will be called for all transaction entry window fields
on ValidateField:f_trans
    say("validate trans field")
return 1
end

// will only be called for transaction user1 field for a sales invoice
on ValidateField:f_trans:e_user1:DI
say("validate sales invoice user1")
return 1
end

Note: You SHOULD always return a value (0 or 1) for these validation handlers, however, if you fail to do so, 1 is the default return value.

Important: You should generally never implement a non-specific handler (such as Before or Validate, with no window id) unless you take great care to limit your functionality appropriately. Remember that even the script editor receives these messages.

Other ways of invoking handlers

You can install a handler in the Command menu using the InstallMenuCommand function. You would normally do this from your script's Load handler. The command will be uninstalled automatically when the script is unloaded (or you can do so explicitly by calling the InstallMenuCommand function again with an empty handler name).

You can install a handler in the Transaction Entry and list windows using the InstallToolbarIcon function. Do this from the Before handler for the window. The icon will be automatically uninstalled prior to another Before (so you should reinstall it for every Before message). This is so that you can easily install a transaction-type-specific toolbar icon. Lists will get a Before every time the toolbar changes (such as for a change of selected view).

To make a handler accessible to reports, forms, the evaluate external command, different scripts, or the entry field expression parser, use the public attribute in the handler declaration.

on MyHandlerName(myParam) public
    say("This is the public handler " + myParam)
end

External invocations of the message will need to use its public name, consisting of the script name, a colon, and the handler name (e.g. My_Script:MyHandlerName(x)). If the script is not currently loaded, you won't be able to compile a script that calls it. Inter-script calling is not recommended.

Scripts normally run with the privileges of the logged-in user. You can have a handler run with admin privileges by adding the elevated attribute to the handler declaration. Note that the extra privilege is dropped when the handler exits, so the operation requiring the privilege will have to be completed in its entirety.

on MyHandlerName(myParam) elevated
    say("I'm running with admin privileges")
    // hmm what would be useful to do here?
    //   • post without elective posting privilege?
    //   • override the extension field with a discounted value ✓
    //   • replace a value in a selection?
    //   • Clear the hold checkbox with SetFieldValue()?
end

New Intrinsic Functions

To facilitate user-interface scripting, some new instrinsic functions have been added. These largely correspond to the functions available to the COM scripting interface, although some provide previously-unavailable functionality.

GetWindowName (windowRef)

Gets the window title.

GetWindowID (windowRef)

The windowID is a string denoting the class of the window. E.g. transaction entry windows have a windowID of "F_TRANS". Window messages will usually be implemented with window-specific handler names incorporating the windowID, so its value is usually implicit. If you use a general handler, you can get the windowID from the windowRef using the GetWindowID() function.

SetFieldValue (windowRef, fieldNumOrName, stringValue)

Attempts to set the user interface field to the given value. Further validation may be invoked. The actual value set in the field is returned by the function. Works with check boxes and popups as well.

GetFieldValue (windowRef, fieldNumOrName)

Gets the value of a field as a string. Works with check boxes and popups as well.

GetListField (listRef, rowNum, columnNumOrName)

rowNum is zero-based. To reduce fragility of scripts over software versions, avoid using column numbers. Use column names.

SetListField (listRef, rowNum, columnNumOrName, value)

rowNum is zero-based. To reduce fragility of scripts over software versions, avoid using column numbers. Use column names. Normal validation will be applied to whatever value you set, just as if the user had typed it.

AddListLine (listRef)

Adds a line, provided the list is mutable. You'll get an error if you try to add a line to e.g. a posted transaction. Returns the new row number.

DeleteListLine (listRef, rowNum)

Deletes a line, provided the list is mutable. You'll get an error if you try to delete a line from e.g. a posted transaction.

GetListHandle (windowRef, listName)

Get the named list (the name is the name in the tab that the list is embedded in (e.g. "By Account", "Payment on Invoice", ...). If the requested list is not visible, return value is a zero handle. Otherwise the returned list handle can be used in the list accessor functions.

GetDialogHandle (listRef)

Get a windowRef from a listRef.

GetListLineCount (listRef)

Gets the number of rows in a list.

GetListName (listRef)

Gets the name of the tab that the list lives under.

GetFieldCount (windowRef)

Returns number of UI objects in a window (note that you can't use Get and Set on all of them, and some may not have symbolic names).

GetFieldNumber (windowRef, fieldNameString)

GetFieldName (windowRef, fieldNum)

Allows you to get symbolic names of fields by iterating over the field numbers.

ExchangeListRows (listRef, rowNum, rowNum)

Exchange two rows of an edit list. You can use this to implement your own custom sorting.

SortListByColumn (listRef, colNumOrName)

Equivalent to the user clicking on the column heading of an editable list.

GotoNextField ()

Move the focus to the next UI field

Further new intrinsics

Say (text)

Speaks the text (requires SAPI5 on Windows).

SysLog (text)

Writes the text to MoneyWorks_Gold.log and the system log on Mac (system.log viewable in Console.app on Mac. You can view the log using the Show Log icon in the script editor.

Alert (text, text, okButton, cancelButton, otherButton, timeoutSeconds)

Displays an alert with up to 3 named buttons. Parameters are optional. Retun value is the button number (1...3)

Ask (controlType, name, value, definition, ...)

Displays a dialog box with custom controls. See the dedicated section (below) on the Ask function.

SetFieldEnabling (windowRef, fieldNameOrIndex, enabled)

Enable or disable a custom control (in report setup dialog or in a custom Ask dialog).

Authenticate (username, password [, privilegeName])

Returns true if the user exists and the password is correct for that user. If a privilege name string is supplied, then the user must also have that privilege to get a true result. See also Allowed()

InstallMenuCommand (menuItemText, handlerNameString)

Install a command in the scripts section of the Command menu. The handler will be called when the command is selected. You can remove the item by calling again with an empty handler name. Your handler does not need to be declared public since the script context is known at the time of installation.

InstallToolbarIcon (windowRef, handlerNameString)

For supported windows (currently the transaction entry window, and list windows), adds an icon to the toolbar. The icon name will be the same as the handler name. When clicked, the handler will be called with the windowRef passed as a parameter. Returns true (1) on success, 0 if no icon was added (due to unsupported window, or toolbar full). Your handler does not need to be declared public since the script context is known at the time of installation.

OpenDocument (pathOrURL)

Available to scripts loaded from the Scripts folder. Opens a document or connects to a url (beginning with "moneyworks://". Returns 1 on success, 0 on failure.

CloseDocument ()

Closes or disconnects from  document. Returns 1 on success, 0 on failure.

CreateArray ()

Creates an empty array variable.

CreateTable ()

Creates an empty table variable. Equivalent to, but more efficient than,  TableAccumulate("")

Navigator (hotlink, coachtip)

Execute the hotlink (privileges and window modality permitting). The optional coachtip text will be displayed after successful execution. You can also use this to just display a coachtip by passing an empty hotlink string.

DoReport (reportName, format, title, parameters...)

Format can be "pdf", "html", "text". The return value is the path to a temporary file that will be deleted when you quit. For any other format (e.g. ""), the return value is the output of the report.

Parameters should be strings in the form "paramName=paramValue". An associative array is also acceptable. See the documentation on the command line doreport command for more information.

DoForm (formName, format, search, parameters)

Format should be "pdf". The return value is the path to a temporary file that will be deleted when you quit.

Parameters should be strings in the form "paramName=paramValue". An associative array is also acceptable. See the documentation on the command line doform command for more information.

Mail (to, subject, content, attachmentName, attachmentPath)

Create or send an email according to the email preferences (note that if the email preference is to create an email in the default email client, the message content parameter will be ignored). Depending on settings and platform, the attachmentName may also be ignored.

Tip: If you need greater control over sending emails, smtp user agents are included in the standard installs and can be accessed using the external() function.

DisplayStickyNote (winRef, tableName, seqNum)

Displays the sticky note(s) belonging to the record in tableName having the given sequence number. E.g. DisplayStickyNote(ref, "Name", 27) displays any notes for Name record with sequence number 27.

DisplaySelection (sel, viewName)

Opens the list window for the given selection, selects the named view and finds the records in the selection (which will necessarily be intersected with the view). Will have the side effect of resetting the view's filter to No Filter (assuming such a change is allowed by the current user's privileges (or the script's elevated privileges)).

ChooseFromList (prompt, tabularText [, mode])

Presents the tabular data in a list, from which the user may select a row. Return value is normally the text of the selected row.
The optional mode parameter can contain keywords that affect the behaviour of the list:
"Drag" allows the user to reorder the list rows.
"All" will return all rows regardless of highlight (can combine "drag,all").
"Multiple" allows multiple rows to be selected (cannot combine).

Unicode (chartext)

Opposite of Char(). Converts the character (first char of string) to its numeric unicode codepoint.

FieldLabel ("file.field")

Gets the name of a custom field, such as "Name.Category1" (as specified in the document preferences).

GetMutex (name)

Attempts to obtain a named mutex from the server. If another user already has the named mutex, returns 0, else 1 if successful. Always successful on a single user system.

Use this when you need to ensure that some operation will only be executed by one client.

ReleaseMutex (name)

Releases the named mutex. If client logs out before releasing a mutex, the mutex is automatically released.

GetPersistent (table, key1, key2)

Loads values from a record in one of the user-definable persistent storage tables ("user", "user2", "lists", and "offledger"). Returns an associative array that contains the field values keyed by the field names (does not include they keys—you already know those).

SetPersistent (table, key1, key2, array)

Updates a record with values from an associative array using the key values as for GetPersistent().

Here is an example of using a mutex and persistent storage to execute something at most once a week by only one user without risk of a race condition whereby two users might look at the user2 persistent storage record at the same time:

on Load
    if GetMutex("rate_update")
        let values = GetPersistent("user2", kMyDevKey, "last_update")
        if values["date1"] < Today() - 7
            GetRates() // do weekly thing
            let values["date1"] = Today()
            SetPersistent("user2", kMyDevKey, "last_update", values)
        endif
        ReleaseMutex("rate_update")
    endif
end

See the table below for tables and fields supported by GetPersistent and SetPersistent.

table	key1	key2	fields
user	7 character key of your choosing	none	data
user2	32 bit numeric developer key. You may use any value from #80000000-#8FFFFFFF for internal projects. For projects that you wish to distribute to others, please contact Cognito for a key range that you can use exclusively.	27 character key of your choosing	int1, int2, float1, float2, date1, date2, text1, text2, text
lists	15 char listID(may or may not be a listID known to the Validation Lists list)	15 character list item	comment
offledger	must be "USR" (you can Get the "CUR" (currency) records if you wish, but you may not Set them)	15 character name	description, balance91..balance00, budget29..budget00, budgetnext01..budgetnext18

SetExchangeRate (currency, date, period, newRate)

Sets the currency rate and creates the associated journal entry.

CreateSelection (tableName, searchExpr, [sortExpr], [descending])

Creates a new selection of records which can be used in a foreach loop. SortExpr can be a field name or a more complex expression.  Pass 1 for the 4th parameter for a descending sort. The default (0) is ascending.

CreateSelection("transaction", "NameCode=`SPRING`", "TransDate")

The search expression can be a relational search or a simple one. It may also be a meta-search mnemonic from the following list:

"*highlighted" or "**" —highlighted records in the main list for the table
"*found" or "*f" —found records in the main list for the table
"*" —highlighted, if any; else found, if any; else all.
"*foundOrAll" —found records, else all records if list not open

IntersectSelection (sel1, sel2OrExprText, [sortExpr], [descending])

Creates a new selection which is the intersection of the given selections. The new selection may optionally be sorted by passing a sort epxression. The first selection must be a selection variable; the second one may be an existing selection variable or can be specified on the fly using a search expression. Obviously, both selections must be for the same table.

RecordsSelected (selOrMetaSearch)

Returns the number of records in the given selection, or metasearch (see CreateSeelction for metasearch mnemonics).

 

Customised input dialogs with the Ask() function

The Ask function provides a simple custom UI for data capture. It takes a variable number of parameters that define the custom controls to appear in the dialog box. The general form is:

Ask (controlType, name, value, definition, ...)

ControlTypes are "static", "text", "password", "number", "date", "checkbox", "popup", "radio", and "invis". These are case-sensitive strings. If you use any other string for controlType, that will be taken as the name (i.e. content) for a static text control. The text, password, number, date, radio, and checkbox types have a name and a value (static just has a name). The popup type has a name, a value, and a definition. The function knows how many following parameters to expect after each controltype parameter.

The buttons are always OK and Cancel.

Examples

let result = Ask("This is a prompt", "number", "Number of Coins", 5, "checkbox", "They are Gold", 0)

The function result is an associative array whose keys are the control names (with spaces replaced by underscores, in the same manner as custom controls for reports). There is also a key-value with key "ok" and value 0 or 1 denoting whether the OK button was clicked.

e.g. result["Choose_one"] → "bar"
result["Number_of_Coins"] → 5
result["They_are_Gold"] → 0

To implement validation handlers for the Ask dialog box, you must pass an invisible control  with the name "ident", thus:

... "invis", "ident", "com_yourdomain_My_Ask"

This defines an invisible control with the special name "ident" which becomes the dialog box's symbolic identifier for the purposes of message interception.

IMPORTANT: Since every Ask dialog will be different, but all loaded scripts receive UI messages for dialogs, the handlers for validating them must be unique across all scripts that could possibly be installed in the same document). You MUST choose a globally unique identifier (do not use the one in this example). It is recommended that you use the reverse domain name convention to derive a globally unique name. For example, I would use names in the form nz_co_cognito_rowan_myscript_ask. This should provide reasonable assurance that no-one else's scripts will try to trap messages for the ask dialog in my script.

Keep in mind the 63-character limit on handler names.

Example

on Load
    let a = Ask("text","Some Text","Default","invis","ident", "nz_co_cognito_MyAsk")
    if a["OK"]
        alert(a["Some_Text"])
    else
        say("cancelled")
    endif
end

on ValidateField:nz_co_cognito_MyAsk:Some_text(id, ref, field, value)
    say(value)
end

Deployment XML file

Scripts can be deployed in an xml file. You can create these files using the Save As XML options in the sidebar popup in the script editor. If such a file is opened while a document is open in MoneyWorks, the scripts will be imported into the document.

New globals

PlatformApplicationPath
PlatformPlugInsPathString
PlatformStandardPlugInsPath

On Windows, these are exactly equivalent to the versions without the Platform prefix. On Mac, they return POSIX paths instead of the HFS paths returned by the non-prefixed versions.

PlatformDocumentPath

Returns the path to the document if it is local (POSIX form on Mac), otherwise an empty string.