You can use Find by Formula (also in the Filter menu in the left of the search box) for more complex searches. Again this works on the currently displayed list window.
- Choose Find by Formula from the Filter menu in the search box
The Advanced Find dialog box is displayed.
The fields relevant to the file being searched are listed in the left hand list of the window. This list will vary depending upon the file being searched. See Appendix A—Field Descriptions for a complete list of field definition.
- Type in the search expression
The names in the list on the left correspond to the fields in the file. You can double-click on these (or type them), to include them in the search expression. Relational searches can be used.
Only those records for which the search expression evaluates to true will be included in the list after the search is done.1
MoneyWorks supports full expression searching. This means you can have simple searches such as:
gross > 1000
or more complex, such as:
round(gross/11, 2) <> taxamount
which will find all transactions whose GST component is not 10% (use round(gross/9, 2) <> taxamount for 12.5%). The expression can contain more than one field from the field list, as well as the special built-in functions from the Functions list —see Functions.
Relational searches can also be used. The following finds (in the transaction list) every transaction less than $50 for every customer/supplier with a code in New South Wales:
The relational operators are:
|=||is equal to|
|<>||is not equal to|
|<||is less than|
|>||is greater than|
|<=||is less than or equal to|
|>=||is greater than or equal to|
There may be more than one criterion that you want to search for—in this case use the And, Or and Not operators, e.g:
round(gross/11, 2) <> taxamount and transdate >= '1/7/2012'
- When your search criteria is complete, click the Find button to locate the matching records
Clicking the Cancel button at this point cancels the search.
The records that match the criterion you specified are displayed in the list window—these records form the Found Set
If MoneyWorks cannot understand your search criteria, an error message will be displayed and the text of the search equation will be highlighted beginning at the point that it does not understand.
Searching on transaction details
If you include a reference to a detail line field in your search expression when searching transactions, MoneyWorks will evaluate the expression once for each detail line in the transaction, and will select the transaction if the expression is true for any of the detail lines. For example:
Detail.Account = “1000@” and detail.gross > 99
will match any transaction for which any of it’s detail lines are coded to an account starting with “1000” and have a gross of over $99.00. This will not find a transaction with the following lines as the expression is false for each line.
Tip: You can search detail lines directly in the Detail Line Items list.
Special text searches
When searching on a text field, you can use the “@” wildcard character as a “begins with” search. For example:
will find all records whose description starts with “Phon”, e.g. “Phone”, “phonograph” (remembering that search is not case sensitive), but not “iPhone”.
To search for a text within a text field, you can use the PositionInText() function, or, more handily, the text pattern you are searching for encosed in “@” symbols. For example:
will find not only “Phone” and “phonograph” as above, but also “My iphone is black” and “His phonograph is bigger than hers”.
Tip: MoneyWorks Gold users can access the Find by Field or Find by Formula by choosing Select>Advanced Find or pressing Shift-Ctrl-F/Option-⌘-F
Tip: You can switch from the Find by Field to Find by Formula search windows by clicking the By Formula button on the search window (and back the other way by clicking the By Field button).
1 If you omit the relational operator, expressions that evaluate to non-zero will be shown in the list after the search is complete. ↩