Coding, Day to Day

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!