Find (fileName.fieldNameText, searchFnText [, limit] [, delim] [, sortFieldText] [, runOnServer])

Result Type: Text (but see below for variant with numeric result)

Definition: Returns as a delim separated string the first limit values of the specified file.fieldnameText as found by the searchfunction when the file is sorted by sortFieldText. If limit is not specified, only the first item is returned; if delim is not specified, the text will be comma separated; if sortFieldText is specified, the data will be selected from the first limit records of the sorted file. If a “-” is appended to SortFieldText the file will be sorted in descending order.

Examples:  Find("name.phone", "code = `A@`")

returns the phone number of the earliest name record which has a code that starts with “A”

Find("product.code", "barcode = '12345'")

returns the product code of a product whose barcode is "12345". This would be useful when updating product records from a supplier file that does not use our internal product code.

Find("transaction.sequencenumber", "type = `DI@` and ourref = `12345@`", 10, "\t")

returns a tab delimited list of the first ten debtor invoices with a number starting with “12345”

Find("transaction.ourref", "type = `DI@`", 10, ",", "sequencenumber-")

returns the invoice numbers of the ten most recently entered debtor invoices as a comma delimited list

Find("product.barcode", "code = `12345`")

returns the barcode for a product with code “12345”. This is equivalent to Lookup(“12345”, “product.barcode”)

Note The fieldnameText can be any valid MoneyWorks expression (a simple field name being itself a simple expression). Thus:

find("transaction.DateToText(TimePosted, -6)", "Status=`P`", 1, ",", "sequencenumber-")

will return a string containing the time of the most recent transaction posting.

Variant for getting a result count only 

Find (fileNameText, searchFnText)

Result Type: Number

Definition:  Returns a count of the number of records found in file fileNameText by the search expression. This variant is differentiation from the general version by not having a dot in the first parameter.

Examples:  Find("transaction", "type=`DII` and dueDate < today()")

returns the number of overdue invoices payable to you.

Note:  Find("Filename", "1") is optimised to get the number of records in a file (which otherwise can be quite time consuming for the general).


In MoneyWorks Datacentre 8.1.9 and later, you can pass true as the 6th parameter. In this case, Find can construct the resulting text entirely on the server if more than one record is found. This will generally improve network performance for scripts, forms, and other things, but it is also possible that if you are relying on an undefined behaviour of the expression in fieldNameText or the search expression, you may get different results. Be particularly wary of search functions that calculate a numeric value and compare it to a textual value. Find() can be forced to run clientside by passing false as the 6th parameter.