Record Links and Anti-patterns

The notes and links are disappearing. What is going on?

Recently, I had to solve the above mystery for a customer. It made me discover some of the inner workings of Record Links and I thought I better share this knowledge before someone gets hurt.

The scenario is quite simple. Imaging a Sales Document. It has a primary key with two fields: Document Type and Number. Classic.

When the document changes type from Quote to Order, in this case, it keeps its number and it is just the type that changes.

However, each time the notes and links of the document vanished.

Where did they go? Are they lost forever or can we get them back?

Notes and links are records in the same table: Record Links.

Antipattern

Record Links is an anti-pattern because of this:

  • The table is shared across all companies.
  • Instead it has a field Company specifying which company the record belongs to.

I ignore the origins of this design decision, but I suppose it could have been like any other table with DataPerCompany and consequently there would be no need for the Company field.

Fields

The primary key is an integer and it is automatically incremtented by the system.

The field Record ID is of the special type RecordID. This is a unique pointer to any record in the database independently of the table and company. I will show you how to find the original record further down.

Table Design

The following is a reduced version of the table, included so you don’t have to look for it.

You can find the table in the Microsoft System app.

table 2000000068 "Record Link"
{
    Caption = 'Record Link';
    DataPerCompany = false;
    ReplicateData = false;
    Scope = Cloud;
    InherentPermissions = rX;

    fields
    {
        field(1; "Link ID"; Integer)
        {
            AutoIncrement = true;
            Caption = 'Link ID';
        }
        field(2; "Record ID"; RecordID)
        {
            Caption = 'Record ID';
        }
        field(3; URL1; Text[2048])
        {
            Caption = 'URL1';
        }
        field(7; Description; Text[250])
        {
            Caption = 'Description';
        }
        field(8; Type; Option)
        {
            Caption = 'Type';
            OptionCaption = 'Link,Note';
            OptionMembers = Link,Note;
        }
        field(9; Note; BLOB)
        {
            Caption = 'Note';
            SubType = Memo;
        }
        field(12; Company; Text[30])
        {
            Caption = 'Company';
            TableRelation = System.Environment.Company.Name;
        }
    }

    keys
    {
        key(Key1; "Link ID")
        {
            Clustered = true;
        }
        key(Key2; "Record ID")
        {
        }
        key(Key3; Company, "Record ID")
        {
        }
    }
}

Page Design

If you want to make a page showing all the record links, you need the following code on the OnOpenPage trigger. Because you are only interested in seeing the record links for the current company.

trigger OnOpenPage()
begin
    Rec.SetRange(Company, Database.CompanyName());
end;

Function Design

To test if a record link is orphaned, you need a function like this:

local procedure RecordLinkIsOrphaned(RecordLink: Record "Record Link"): Boolean
var
    RecRef: RecordRef;
begin
    if RecordLink.Company <> Database.CompanyName() then
        RecRef.ChangeCompany(RecordLink.Company);
    exit(not RecRef.Get(RecordLink."Record ID"));
end;

If you do not change the company, the Get will fail when the record is not in the current company.

Assume that we have some kind of Sales Document as mentioned in the beginning.

In our scenario the record links did not get moved to the new record.

Luckily, there is a standard codeunit to help us with this: Record Link Management.

procedure MoveRecordLink(FromSalesDocument: Record SalesDocument; ToSalesDocument: Record SalesDocument)
var
    LinkManagement: Codeunit "Record Link Management";
begin
    LinkManagement.CopyLinks(FromSalesDocument, ToSalesDocument);
    FromSalesDocument.DeleteLinks();
end;

Warning: Do not try this in production

My first version of the above function looked like this:

procedure MoveRecordLink(FromSalesDocument: Record SalesDocument; ToSalesDocument: Record SalesDocument)
var
    LinkManagement: Codeunit "Record Link Management";
begin
    LinkManagement.CopyLinks(FromSalesDocument, ToSalesDocument);
    LinkManagement.RemoveLinks(FromSalesDocument);
end;

Though this looks right, it is wrong. The following line will remove the record links on all records in the entire table, not just on the selected record.

    LinkManagement.RemoveLinks(FromSalesDocument); // DANGER

So, please, note that you have to use the following code to remove the links on a specific record.

    FromSalesDocument.DeleteLinks();

Deleting a Record Link - Behind the Scenes

Let say, you have added a note to a document and then you delete the document. What happens to the note?

It depends on whether the OnDelete trigger is run or not.

If you delete the document from the UI, that is a Page, then the note is deleted automatically. If you delete the document from code, you have to explicitly tell the code to run the OnDelete trigger.

This will delete the note:

    SalesDocument.Delete(true);

This will not delete the note and the note will be come an orphan.

    SalesDocument.Delete(false);

Anti Pattern

Normally, at least in the past, we only ran the trigger, if there was code on it. Rec.Delete(false);

Now, if you are deleting a record that has record links, but no code in the OnDelete trigger, you have to run it like this Rec.Delete(true);.

There is a function for that and you can use it like this:

local procedure RemoveOrphanedLinks()
var
    LinkManagement: Codeunit "Record Link Management";
begin
    LinkManagement.RemoveOrphanedLinks();
end;

This will remove all orphaned links in the entire database in all companies.

Job Queue Entry

I have not quite understood the relation ship between error messages in Job Queue Entry and Record Links.

If you make a page displaying all Record Links in the company, you will see notes belonging to Job Queue Entries. This a bit strange. If you open the corresponding Job Queue Entry Card page and click Show Error, you will see the latest error. But in the Record Links there will be the entire history of errors.

If you know anything about this, let me know.

Back to Our Mystery

The origin of the disappearing links and notes were this code.

SalesDocument.Find();
SalesDocument.Delete();
SalesDocument.Copy(NewSalesDocument);

The good part of the story is that the programmer wrote SalesDocument.Delete(); thereby making all the Record Links orphans and since only the document type changed, I was able to find and pair all Record Links with its parent again.

I fixed the code like this.

SalesDocument.Find();
MoveRecordLink(SalesDocument, NewSalesDocument);
SalesDocument.Delete();
SalesDocument.Copy(NewSalesDocument);

When Anti-patterns Meet

This was the story of what can happen when two anti-patters meet.

The story ended happily. The users got their links and notes back.

But it could have gone terribly wrong.

Anti-patterns are the opposite of best practice and they are bad for our brains. When we see something we first presume, it works as usual and we use our intuition to move forward quickly. When it doesn’t we have to analyze it and it will slow us down.

Message to all BC developers: Stick to best practices whenever you can and avoid anti-patterns.