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.
Tip
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.