The following functions are used to search for records:

These functions are some of the most commonly used C/AL functions. When you search for records, it is important to know the difference between GET and FIND and to know how to use FIND and NEXT in conjunction.

GET Function

GET retrieves one record based on values of the primary key fields.

GET has the following syntax.

  CopyCode imageCopy Code
[Ok :=] Record.GET([Value],...)

For example, if the No. field is the primary key of the Customer table and if you have created a Record variable called CustomerRec that has a Subtype of Customer, then you can use GET in the following way:

  CopyCode imageCopy Code
CustomerRec.GET('4711');

The result is that the record of customer 4711 is retrieved.

GET produces a run-time error if it fails and the return value is not checked by the code. In the preceding example, the actual code that you write should be similar to the following.

  CopyCode imageCopy Code
IF CustomerRec.GET('4711') THEN
.... // Do some processing.
ELSE
.... // Do some error processing.

GET searches for the records, regardless of the current filters, and it does not change any filters. GET always searches through all the records in a table.

FIND Function

FIND locates a record in a C/SIDE table based on the values stored in the keys.

FIND has the following syntax.

  CopyCode imageCopy Code
Ok := Record.FIND([Which])

The important differences between GET and FIND are the following:

  • FIND uses the current filters.

  • FIND can be instructed to look for records where the key value is equal to, greater than, or smaller than the search string.

  • FIND can find the first or the last record, depending on the sort order defined by the current key.

When you are developing applications in a relational database, there are often one-to-many relationships defined between tables. An example could be the relationship between an Item table, which registers items, and a Sales Line table, which registers the detailed lines from sales orders. One record in the Sales Line table can only be related to one item, but each item can be related to any number of sales line records. You would not want an item record to be deleted as long as there are still open sales orders that include the item. You can use FIND to check for open sales orders.

The OnDelete trigger of the Item table includes the following code that illustrates using FIND.

  CopyCode imageCopy Code
SalesOrderLine.SETCURRENTKEY(Type,"No.");
SalesOrderLine.SETRANGE(Type,SalesOrderLine.Type::Item);
SalesOrderLine.SETRANGE("No.","No.");
IF SalesOrderLine.FIND('-') THEN
ERROR(Text001,TABLECAPTION,"No.",SalesOrderLine."Document Type");

NEXT Function

NEXT is often used with FIND to step through the records of a table.

NEXT has the following syntax.

  CopyCode imageCopy Code
Steps := Record.NEXT([Steps])

In the following example, FIND is used to go to the first record of the table. NEXT is used to step through every record, until there are no more. When there are no more records, NEXT returns 0 (zero).

  CopyCode imageCopy Code
FIND('-');
REPEAT
// process record
UNTIL NEXT = 0;