Edit

Relational Searches

You can exploit the awesome power of the MoneyWorks relational database by using a relational search. In its simple form, a relational search has the form:

[File:SearchExpression]

So for example:

[Transaction:Type="DII"]

would find all incomplete (unpaid and/or unposted) debtor invoices.

The relational aspect comes in when you add further terms. Each term is in [...] and must contain a file name, but the search expression is optional. Omission of a search in the first term implies start with all records. Subsequent terms invoke a Find Related (sort of a relational Join) from the previous term's selected records to the new term's file, which can then be optionally filtered with the search expression.

Thus:

[Account:Type = "CA"]

will find every account which is a current asset, and:

[Account:Type = "CA"][Detail]

will find all the transaction lines (details) that pertain to current asset accounts.

Transaction:Period > X and Period < Y][Account:Type = "CA"][Detail]

will find the same, but restricted to transactions in the period range.

[Transaction:Type="DII"][Name:state="NSW"]

will find all Customers in NSW who have incomplet e invoices. Or, reversing it:

[Name:state="NSW"][Transaction:Type="DII"]

will find all outstanding invoices for customers in NSW.

Ambiguous Links: When the field linking one file to another is ambiguous (e.g. [Account][Product] could use any of the product's control accounts as the linking field), you explicitly specify which field to use. Where necessary, two terms of the same file may be used. E.g. to go from a selection of payments to invoices they pay, use

[Transaction:ourref="blah"][Payments.CashTrans][Payments.InvoiceID][Transaction]

If no explicit links are given, MoneyWorks will choose the first match that you would see in the options for Find Related between those files (If you try a Find Related from Account to Product you will see that the first option that gets you to Product is the sales account i.e. Products whose sales account is the account you are starting from).

Linking to accounts: When joining to the account file, any department in the input is ignored. Thus to find all the stock control accounts, you can use:

[product.stockAcct:hash>=8][account.code]

Even if some of the stockAcct codes were departmentalised, it will still find the correct account (e.g. “1310-WEST” will link to account “1310”).

Negating a search: Use the Negation operator [!] to find items that are not part of the related set. Thus the search:

[product:code = "BA100"][transaction:type="DI@"][Name]

will find every customer who has been invoiced for product BA100.

[product:code = "BA100"][transaction:type="DI@"][Name][!]

will find every customer who has not been invoiced for the product.

Unions and Intersections: Searches that start from different source files or selections can be joined using the postfix push, intersection and union operators. Having found one selection in the target file, that selection is saved using the push operator (^). The next search is then done, and the resulting selection is combined with the previously-saved selection using the union (+) or intersection (*) operators, then start finding a new selection in the target file. For example:

[Transaction:transdate = today() and type = `DI@`][Detail]^[Product:Supplier=supplier_code][Detail]*

will find the detail lines for all debtor invoices dated today and push (^) the result. It will then find all lines for products for supplier_code (a run-time report or form variable). Finally the two detail line selections will be intersected (*) to get just the invoiced detail lines for supplier’s products in today’s invoices.

Using variables in relational search expressions: 

In a report cell, or in a MWScript handler when you have an existing selection in a cell name/variable, you can reference that selection as the starting point (or an intersection or union) in a subsequent relational search in place of a table name (i.e. the relational search parser can access the context of the caller).

e.g. the following calculations can be done in report cells named dsel and psel:

dsel =CreateSelection("detail", "detail.stockcode =`B@`")

psel =CreateSelection("product", "[dsel][product]") // CreateSelection can "see" dsel

or, equivalently, in MWScript: 

    let b = "B@"
    let dsel = CreateSelection("Detail", "Detail.StockCode=b")
    let psel = CreateSelection("Product", "[dsel][product]")

(note that CreateSelection can also access simple variables from the calling context, not just selections).

Relational searches can be used in:

Note that in all cases the final term’s file must reflect the target list.

Also note: Search expressions are limited to 255 characters in length.

Precedence Selections