In the previous chapter, we learned about the basics of the FIND
function. We learned about FIND('-')
to read from the beginning of a selected set of records, FINDSET
to read a selected set of records, and FIND('+')
to begin reading at the far end of the selected set of records. Now we will review additional functions that are generally used with the FIND
functions in typical production code. While we are designing code which uses the MODIFY
and DELETE
record functions, we need to consider possible interactions with other users on the system. There might be someone else modifying and deleting records in the same table which our application is updating.
We may want to use the LOCKTABLE
function to briefly gain total control of the data, while updating the data. We can find more information on LOCKTABLE
in the online C/AL Reference Guide Help. The SQL Server database supports Record Level Locking. There are a number of factors that we should consider when coding data locking in our processes. It is worthwhile reading all of the C/AL Reference Guide material found by a Search on LOCKTABLE
, particularly Locking in Microsoft SQL Server.
The syntax defined for the NEXT
function is:
IntegerValue := Record.NEXT ( ReadStepSize )
The full assignment statement format is rarely used to set an IntegerValue
. In addition, there is no documentation for the usage of a non-zero IntegerValue
. When IntegerValue
goes to zero, it means that a NEXT
record was not found. In early versions of NAV 2015, the Help text for NEXT
does not properly explain the use of or value setting for IntegerValue
.
If the ReadStepSize
value is negative, the table will be read in reverse; if ReadStepSize
is positive (the default), then the table will be read forward. The size of the value in ReadStepSize
controls which records should be read. For example, if ReadStepSize
is 2
or -2
, then every second record will be read. If ReadStepSize
is 10
or -10
, then every tenth record will be read. The default value is 1, in which case every record will be read and the read direction will be forward.
In a typical data read loop, the first read is a FIND
or FINDSET
function followed by a REPEAT-UNTIL
loop. The exit condition is the expression UNTIL
Record.NEXT
=
0;
. The C/AL for FINDSET
and FIND('-')
are structured alike.
The full C/AL syntax for this typical loop looks like the following:
IF CustRec.FIND('-') THEN REPEAT Block of C/AL logic UNTIL CustRec.NEXT = 0;
The purpose of the INSERT
function is to add new records to a table. The syntax for the INSERT
function is as follows:
[BooleanValue :=] Record.INSERT ( [ TriggerControlBoolean ] )
If BooleanValue
is not used and the INSERT
function fails (for example, if the insertion would result in a duplicate Primary Key), the process will terminate with an error. Generally, we should handle a detected error in code by using the BooleanValue
and supplying our own error handling logic rather than allowing a default termination.
The TriggerControlBoolean
value controls whether or not the table's OnInsert
trigger fires when the INSERT
occurs. The default value is FALSE
. If we let the default FALSE
control, we run the risk of not performing error checking that the table's designer assumed would be run when a new record was added.
When we are reading a table and we also need to INSERT
records into that same table, the INSERT
should be done to a separate instance of the table. We can use either a global or local variable for that second instance. If we INSERT
into the same table we are reading, we run the risk of reading the new records as part of our processing (likely a very confusing action). We also run the risk of changing the sequence of our processing unexpectedly due to the introduction of new records into our data set. While the database access methods are continually improved by Microsoft and this warning may be overcautious, it is better to be safe than sorry.
The purpose of the MODIFY
function is to modify (update) the existing data records. The syntax for MODIFY
is:
[BooleanValue :=] Record.MODIFY ( [ TriggerControlBoolean ] )
If BooleanValue
is not used and MODIFY
fails (for example, if another process changes the record after it was read by this process), then the process will terminate with an error statement. The code should either handle a detected error or gracefully terminate the process. The TriggerControlBoolean
value controls whether or not the table's OnModify
trigger fires when this MODIFY
occurs. The default value is FALSE
, which would not perform any OnModify
processing. MODIFY
cannot be used to cause a change in a Primary Key field. In that case, the RENAME
function must be used.
There is system based checking to make sure that a MODIFY
is done using the current version of the data record by making sure that another process hasn't modified and committed the record after it was read by this process. Our logic should refresh the record using the GET
function, then change any values, and then call the MODIFY
function.
In Table and Page objects, the system automatically provides us with the system variables Rec
and xRec
. Until a record has been updated by MODIFY
, Rec
represents the current record data in process and xRec
represents the record data before it was modified. By comparing field values in Rec
and xRec
, we can determine if changes have been made to the record in the current process cycle. Rec
and xRec
records have all the same fields in the same structure as the table to which they relate.
The purpose of the DELETE
function is to delete existing data records. The syntax for DELETE
is as follows:
[BooleanValue :=] Record.DELETE ( [ TriggerControlBoolean ] )
When DELETE
fails and the BooleanValue
is not used, the process will terminate with an error statement. Our code should handle any detected error or terminate the process, as appropriate.
The TriggerControlBoolean
value is TRUE
or FALSE
, and it controls whether or not the table's OnDelete
trigger fires when this DELETE
occurs. The default value is FALSE
. If we let the default FALSE
value in place, we run the risk of not performing error checking that the table's designer assumed would be run when a record was deleted.
In NAV 2015, there is improved checking to make sure a DELETE
is using the current version of the record, which making sure another process hasn't modified and committed the record after it was read by this process. Therefore, before the DELETE
function is called, the program should refresh the record using the GET
function.
MODIFYALL
is the high-volume version of the MODIFY
function. If we have a group of records for which we wish to modify one field in all of them to the same new value, we should use MODIFYALL
. MODIFYALL
is controlled by the filters that apply at the time of invoking. The other choice for doing a mass modification would be to have a FIND
–NEXT
loop in which we modify each record, one at a time. The advantage of MODIFYALL
is that it allows the developer and the system to optimize the code for the volume update. Any system optimization will be a function of the SQL statements are generated by the C/AL compiler.
The syntax for MODIFYALL
is as follows:
Record.MODIFYALL (FieldToBeModified,NewValue [,TriggerControlBoolean ] )
The TriggerControlBoolean
value, a TRUE
or FALSE
entry, controls whether or not the table's OnModify
trigger fires when this MODIFY
occurs. The default value is FALSE
which would result in the field OnValidate
trigger not being executed. In a typical situation, a filter or a series of filters would be applied to a table followed by the MODIFYALL
function. A simple example where we are going to reassign all the Territory
Codes
for a particular Salesperson
to NORTH,
is as follows:
Customer.RESET; Customer.SETRANGE("Salesperson Code",'DAS'); Customer.MODIFYALL("Territory Code",'NORTH',TRUE);
DELETEALL
is the high volume version of the DELETE
function. If we have a group of records that we wish to delete, use DELETEALL
. The other choice would be a FIND-NEXT
loop in which we delete each record one at a time. The advantage of DELETEALL
is that it allows the developer and the system to optimize the code for the volume deletion. Any system optimization will be a function of what SQL statements are generated by the C/AL compiler.
The syntax for DELETEALL
is as follows:
Record.DELETEALL ( [,TriggerControlBoolean] )
The TriggerControlBoolean
value, a TRUE
or FALSE
entry, controls whether or not the table's OnDelete
trigger fires when this DELETE
occurs. The default value is FALSE
. If the TriggerControlBoolean
value is TRUE
, then the OnDelete
trigger will fire for each record deleted. In that case, there is little or no speed advantage for DELETEALL
versus the use of a FIND-DELETE-NEXT
loop.
In a typical situation, a filter or a series of filters would be applied to a table followed by the DELETEALL
function, similar to the preceding example. Like MODIFYALL
, DELETEALL
respects the filters that have been set and does not do any referential integrity error checking.