The FIND family of functions is the general purpose data retrieval function in C/AL. It is much more flexible than GET, therefore more widely used. GET has the advantage of being faster as it operates only on an unfiltered direct access through the Primary Key, looking for a single uniquely keyed entry. There are two forms of FIND functions in C/AL, one a remnant from a previous database structure and the other designed specifically to work efficiently with SQL Server. Both are supported, and we will find both in standard code.

The older version of the FIND function has the following syntax:

[BooleanValue :=] RecordName.FIND ( [Which] ).

The newer SQL Server-specific members of the FIND function family have slightly different syntax, as we will see shortly.

Just as with the GET function, assigning the FIND function result to a Boolean value is optional. However, in almost all of the cases, FIND is embedded in a condition that controls subsequent processing appropriately. Either way, it is important to structure our code to handle the instance where FIND is not successful.

FIND differs from GET in several important ways, some of which are as follows:

• FIND operates under the limits of whatever filters are applied on the subject field.
• FIND presents the data in the sequence of the key that is currently selected by default or by C/AL code.
• When FIND is used, the index used for the data reading is controlled by the SQL Server Query Analyzer.
• Different variations of the FIND function are designed specifically for use in different situations. This allows coding to be optimized for better SQL Server performance. All the FIND functions are described further in the Help section C/AL Database Functions and Performance on SQL Server at https://msdn.microsoft.com/en-us/library/dd355237.aspx.

The forms of FIND are as follows:

• FIND('-'): This finds the first record in a table that satisfies the defined filter and current key.
• FINDFIRST: This finds the first record in a table that satisfies the defined filter and defined key choice. Conceptually, it is equivalent to the FIND('-') for a sinqle record read but better for SQL Server when a filter or range is applied.
• FIND('+'): This finds the last record in a table that satisfies the defined filter and defined key choice. Often this is not an efficient option for SQL Server because it causes it to read a set of records when many times only a single record is needed. The exception is when a table is to be processed in reverse order. Then, it is appropriate to use FIND('+') with SQL Server.
• FINDLAST: This finds the last record in a table that satisfies the defined filter and current key. It is conceptually equivalent to FIND('+') but, often, much better for SQL Server as it reads a single record, not a set of records.
• FINDSET: This is the most efficient way to read a set of records from SQL Server for sequential processing within a specified filter and range. FINDSET allows defining the standard size of the read record cache as a setup parameter but, normally, it defaults to reading 50 records (table rows) for the first server call. The syntax includes two optional True/False parameters, as follows:

        FINDSET([ForUpdate][, UpdateKey]);

The first parameter controls whether or not the Read is in preparation for an update and the second parameter is TRUE when the first parameter is TRUE and the update is of key fields. FINDSET clears any FlowFields in the records that are read.