Business Central – CLEAR vs INIT function

What is the difference between CLEAR and INIT functions? Clear(Record) vs Record.Init()

CLEAR function

When used on a variable, i.e. Clear(SomeVariable), it simply clears the value that is stored to its default state. For example if it is a number variable, it will be set to 0 (zero), a date variable to undefined date (0D) and so on. Clear can be used on a Guid data type, and in this case sets the GUID to all zeroes.

When used on a record, i.e. Clear(Rec), or an array, all elements are cleared. It resets all the filters, resets the primary key and all fields will be initialized based on the InitValue property. If ChangeCompany is used, it will reset back to the original company.

INIT function

Used for initializing a new record in a table for insertion. It will assign default values to all the fields in the record, including the SystemId field.

Both of these functions might seem to be doing the same thing, setting fields to their default state, but they are used under different circumstances for different purposes.

Business Central – How to change primary key of a record

Typically when we want to update the contents of a record in BC, we would just use the modify function. For most cases, that would suffice, but what happens if the field you want to update is part of a composite primary key? Or when updating a record in which the content to be changed includes part of a primary key? In these scenarios, using the modify function will return an error. The solution? Read on!

Sample Record Structure

Here is a sample record structure for reference. In this case, we have a composite key consisting of “Entry No.” and “Line No.” as well as other data fields.

table 55047 "Sample Line Data"
{
    fields
    {
        field(1; "Entry No."; Integer)
        {
            Caption = 'Entry No.';
        }

        field(2; "Line No."; Integer)
        {
            Caption = 'Line No.';
        }
       
        field(3; "Item Code"; Code[10])
        {
            Caption = 'Item Code';
        }

        field(4; "Item Description"; Text[50])
        {
            Caption = 'Item Description';
        }
    }

    keys
    {
        key(PK; "Entry No.", "Line No.")
        {
            Clustered = true;
        }
    }
}

So what should we do if we need to change the following data: “Line No.” and “Item Description”?

We use the Rename function to update the “Line No.” which is part of the primary key, then re-retrieve the record and update the “Item Description” using the modify function.

The syntax for Rename function as show below.

[Ok := ]  Record.Rename(Value1: Any [, Value2: Any,…])

Code example as show below.

// #1 change part of primary key, line no from 1000 to 2000
if MyRecord.Get(1,1000) then
    MyRecord.Rename(MyRecord."Entry No.", 2000); 

// #2 get record again with new key
if MyRecord.Get(1, 2000) then
    begin 
        // #3 update item description (non key field)
        MyRecord."Item Description" := 'Changed Description';
        MyRecord.Modify();
    end;

Remember, do not change the sequence of the primary key!

NAV2016 – How to Import Excel Data into NAV

Note: This is only for Microsoft Dynamics NAV, NOT Business Central. While the principle behind it is the same, Business Central uses different objects to achieve the intended result. What we are focused on in this post is how to import data from an excel spreadsheet to NAV (example, transfer data from excel to cash receipt journal records). This will be a brief guide on the import aspect.

Variables Required

• ExcelBuffer – Record “ExcelBuffer” – need to declare as a global variable
• FileManagement – Codeunit “File Management”
• ServerFileName – Text
• SheetName – Text
• TotalRows – Integer
• RowNo – Integer
• And any other variables to store the excel cell data

Supporting Function

// Get Value At Cell Function 
LOCAL GetValueAtCell(RowNo : Integer;ColNo : Integer) : Text
IF ExcelBuffer.GET(RowNo,ColNo) THEN
  EXIT(ExcelBuffer."Cell Value as Text")
ELSE
  EXIT('');

The above function is required to get the value from a specific cell in the excel spreadsheet based on the Row and Column of the cell and return the result as a string. You will have to do the necessary conversion to other data type if you want a numeric value and so on.

Import Function

First we will need to create a function (i.e. in a codeunit) that can be called from a page to trigger this import function. In this function we will trigger the dialog for the user to select the excel spreadsheet to import and also to select which sheet to import (if there are multiple excel sheets). The code snippet for this is as shown below

// Sample Import Call Function that takes in some parameters
ImportItemJournalExcel(JournalBatchName : Code[10];JournalTemplateName : Code[10])

// Get Import File
ServerFileName := FileManagement.UploadFile('Please select import File','File');
IF ServerFileName ='' THEN
  EXIT;

// Get Sheet
SheetName := ExcelBuffer.SelectSheetsName(ServerFileName);
IF SheetName = '' THEN
  EXIT;

Additional Information: It might be possible to import a zip file of excel spreadsheets for processing and/or also to process multiple sheets in an excel file. But we will not be covering this here.

Next step is to open the excel document and get the total number of rows of data.

ExcelBuffer.LOCKTABLE;
ExcelBuffer.OpenBook(ServerFileName,SheetName);
ExcelBuffer.ReadSheet;
ExcelBuffer.RESET;
ExcelBuffer.SETCURRENTKEY("Row No.");
IF ExcelBuffer.FINDLAST THEN
  TotalRows := ExcelBuffer."Row No.";

Once we know the total number of rows, we need to identify which row to start taking data from and start looping the spreadsheet to extract the data. And in this loop, you can add the codes to insert the data to a record (example Gen. Journal Lines)

FOR RowNo:=3 TO TotalRows DO BEGIN    // Start from Row 3

  // Get Cell Data
  SampleLocationData := GetValueAtCell(RowNo,2); // Column Number 2 : Location Code
  SamplePONo := GetValueAtCell(RowNo,8); // Column Number 8 : PO Number
  SampleProdCode := GetValueAtCell(RowNo,13); // Column Number 13 : Product Code
  SampleProdDescr := GetValueAtCell(RowNo,16); // Column Number 16 : Product Description  

  //  Do any logic here (i.e. insertion)

  CustomRecord.INIT;
  CustomRecord.LocationCode := SampleLocationData;
  CustomRecord.PONo := samplePONo;
  // continue adding data as needed
  CustomRecord.INSERT();


END;

Finally to end it off.

ExcelBuffer.CloseBook;

MESSAGE('Excel Import Completed');

That’s all that there is to it.