Using tables instead of table extensions

Recently James Crowter wrote an excellent article about table extensions and how they affect performance. In short, table extensions are great for flexibility and ease of development, but performance decreases when the number of table extensions is adding up. Especially when table extensions are used for hot tables. With hot tables I mean tables that are used often, like Item, Customer, Sales Line, Item Ledger Entry, etc.

So I started thinking, when would you decide to not use table extensions? And how to work supplemental tables but still have the same experience for the end-user? In this blog post, I want to do some suggestions. I’m aware this can be done in many ways, so this is just a suggestion.

Considerations

Let’s first go through the process of choosing between table extension or supplemental table. Basically it should be possible to put all extra fields you have for a particular table into a supplemental table rather than a table extension. Behind the scenes, the table extension is a supplemental table anyway. The only difference, and definitely an important difference, is that the table extension appears to be one table for the developer. But with some extra effort we can do that ourselves, but what do we gain for that extra effort? Well, I guess that’s already clear, we get better overall performance because there is no automatic join. We decide in code when to read the supplemental table.

And that’s exactly what should be the main consideration when choosing between a supplemental table and a table extension. Do you need those extra fields in many places in the code? Or just at a few places? In other words, would you benefit from the automatic join of the companion table or would it be a waste of resources because you only use those fields in a few places? Besides that, you also need to look at how much the table is being used. Is it a hot table, used in many different places and a favorite table to extend? Then you should also be careful with table extensions.

The Data Table

Ok, let’s imagine I have some fields to be added to the Item. Note that I’m not saying ‘to the Item table‘. I want to the user to see those fields on the Item list and card as if they were part of the Item table but I’ve chosen to put them into a supplemental table. The first step is to create that table.

table 50100 "Item Extra Fields AJK"
{
    fields
    {
        field(1; Id; Guid)
        {
        }
        field(2; "Item No."; Code[20])
        {
            FieldClass = FlowField;
            CalcFormula = lookup (Item."No." where(SystemId = field(id)));
        }
        field(3; "Perishable"; Boolean)
        {
            trigger OnValidate()
            begin
                if Perishable then
                    "Storage Temperature" := 4
                else
                    "Storage Temperature" := 0;
            end;
        }
        field(4; "Food Category"; Enum FoodCategory)
        {
        }
        field(5; "Storage Temperature"; Decimal)
        {
        }
    }
    keys
    {
        key(PK; Id) { }
    }
}

As you can see, the table is not using the Item No. field as primary key. Instead, it uses an Id field that I want to be the same value as the SystemId of the Item record.

The Item No. field is a FlowField, so if I run the table I can see to what Item that record belongs.

The other fields are the fields that I want to add to the Item Card and Item List page. The field Perishable has code in the OnValidate trigger, and I want that to execute as normal when the user modifies that field.

The Tableextension

How are we going to work with this table? I figured it would make sense to create a table extension for the Item table with functions to read and save the supplemental table. In that way, we have one central place to get to the data and to save it. The table extension can also hold FlowFields to the fields of the supplemental table, so you can get the values directly without the need to read the supplemental table. That’s especially handy when reading values. With SetAutoCalcFields you can force to join the values from the supplemental table when you want to loop through the base table instead of reading the supplemental table for every single record.

tableextension 50100 "Item AJK" extends Item
{
    fields
    {
        field(50100; "Perishable AJK"; Boolean)
        {
            Caption = 'Perishable';
            FieldClass = FlowField;
            CalcFormula = lookup ("Item Extra Fields AJK".Perishable where(Id = field(SystemId)));
        }
        field(50101; "Food Category AJK"; Enum FoodCategory)
        {
            Caption = 'Food Category';
            FieldClass = FlowField;
            CalcFormula = lookup ("Item Extra Fields AJK"."Food Category" where(Id = field(SystemId)));
        }
        field(50102; "Storage Temperature AJK"; Decimal)
        {
            Caption = 'Storage Temperature';
            FieldClass = FlowField;
            CalcFormula = lookup ("Item Extra Fields AJK"."Storage Temperature" where(Id = field(SystemId)));
        }
    }
    var
        _ItemExtraFields: Record "Item Extra Fields AJK";
    procedure GetItemExtraFields(var ItemExtraFields: Record "Item Extra Fields AJK")
    begin
        ReadItemExtraFields();
        ItemExtraFields := _ItemExtraFields;
    end;
    procedure SetItemExtraFields(var ItemExtraFields: Record "Item Extra Fields AJK")
    begin
        _ItemExtraFields := ItemExtraFields;
    end;
    procedure SaveItemExtraFields()
    begin
        if not IsNullGuid(_ItemExtraFields.Id) then
            if not _ItemExtraFields.Modify() then
                _ItemExtraFields.Insert(false, true);
    end;
    procedure DeleteItemExtraFields()
    begin
        ReadItemExtraFields();
        if _ItemExtraFields.Delete() then;
    end;
    local procedure ReadItemExtraFields()
    begin
        if _ItemExtraFields.Id <> SystemId then
            if not _ItemExtraFields.Get(SystemId) then begin
                _ItemExtraFields.Init();
                _ItemExtraFields.Id := SystemId;
                _ItemExtraFields.SystemId := SystemId;
            end;
    end;
}

As you can see, the FlowFields are based on the value of the SystemId field, linked to the Id field of the supplement table. In the function ReadItemExtraFields these values are set in case the record does not exist.

The current record of the corresponding supplemental table is stored in a global variable in the table extension. With a get and set function, it is possible to read or set the actual values.

The function SaveItemExtraFields is used to really save the actual values back to the supplement table in the database. In this approach, not all Item records do automatically have a corresponding record in the supplemental table, so I use the construct if not Modify then Insert. The Insert uses the second parameter to tell the platform to not create a new SystemId but to use the value that we already set in the function ReadItemExtraFields. Of course this only works correctly if you follow the flow of these functions:

• GetItemExtraFields
• SetItemExtraFields
• SaveItemExtraFields

If you would only use SetItemExtraFields and did not properly initialize the record with the Id values, then this is going to fail.

Usage on an editable Page

Let’s have a look at how this works on the Item Card page.

pageextension 50101 "Item Card AJK" extends "Item Card"
{
    layout
    {
        addafter(Item)
        {
            group(FoodDetails)
            {
                Caption = 'Food Details';
                field(PerishableAJK; ItemExtraFields.Perishable)
                {
                    Caption = 'Perishable';
                    ApplicationArea = All;
                    trigger OnValidate()
                    begin
                        ItemExtraFields.Validate(Perishable);
                        SetItemExtraFields(ItemExtraFields);
                    end;
                }
                field(FoodCategeryAJK; ItemExtraFields."Food Category")
                {
                    Caption = 'Food Category';
                    ApplicationArea = All;
                    trigger OnValidate()
                    begin
                        SetItemExtraFields(ItemExtraFields);
                    end;
                }
                field(StorageTemperatureAJK; ItemExtraFields."Storage Temperature")
                {
                    Caption = 'Storage Temperature';
                    ApplicationArea = All;
                    trigger OnValidate()
                    begin
                        SetItemExtraFields(ItemExtraFields);
                    end;
                }
            }
        }
    }
    var
        ItemExtraFields: Record "Item Extra Fields AJK";
    trigger OnInsertRecord(BelowxRec: Boolean): Boolean
    begin
        SaveItemExtraFields();
    end;
    trigger OnModifyRecord(): Boolean
    begin
        SaveItemExtraFields();
    end;
    trigger OnClosePage()
    begin
        SaveItemExtraFields();
    end;
    trigger OnAfterGetCurrRecord()
    begin
        GetItemExtraFields(ItemExtraFields);
    end;
}

The supplemental table is stored in a global variable on the page and read in the OnAfterGetRecord trigger. The fields are then displayed on the screen with source expression pointing to the respective fields. That makes sure the values are directly stored in the record. But now we are getting to some details that I think developers are not going to like much. Because it is different from how we are used to working with table fields, we need to do some extra stuff.

First of all, you need to specify the caption again. That means we now have the same caption three times: in the supplemental table, on the FlowField and now also on the Page. They will end up as three different captions inside the .xlf file and they all need to be translated. So we must make sure to keep the captions and the translations are aligned across all places we use these fields.

The other point is that the OnValidate trigger is not executing automatically. The value will be stored in the field of the global record variable, but it is treated as a global variable and not as a field with an OnValidate trigger. Hence, we have to explicitly validate the field from code on the page.

I have chosen to save the current record value back to the base table immediately. It would also be possible to do so from the OnModify trigger, but better safe than sorry.

Usage on a read-only Page

Displaying the fields on the Item List page is just a matter of adding the FlowFields. They support sorting and filtering, so the user would not even notice that these fields come from a supplemental table anyway.

pageextension 50100 "Item List AJK" extends "Item List"
{
    layout
    {
        addafter(Description)
        {
            field("Perishable AJK"; "Perishable AJK") { ApplicationArea = All; }
            field("Food Category AJK"; "Food Category AJK") { ApplicationArea = All; }
        }
    }
}

Deleting the record

The final part would be a delete trigger. For that, I suppose to use an event subscriber rather than using the page triggers. In the most simple way, that would look like this:

codeunit 50100 "Item Subscribers AJK"
{
    SingleInstance = true;
    [EventSubscriber(ObjectType::Table, Database::Item, 'OnAfterDeleteEvent', '', false, false)]
    local procedure OnDelete(var Rec: Record Item)
    begin
        Rec.DeleteItemExtraFields();
    end;
}

Final thoughts

There are other approaches possible as well. Like using the OnInsert event to always create a corresponding record in the supplemental table and assume that record is always available. That would require an install and upgrade procedure as well to sync the tables during installation or upgrading of the app.

This is by far not as convenient as using table extensions, I have to admit that. And I’m not very proud of this solution. But it works and with this little effort, we can avoid the heavy load of multiple table extensions. As James said, we should use great power responsibly. So, if writing some extra code is the price we have to pay for getting a system that performs better, so be it.

I hope Microsoft will work on other solutions in the meantime. Which would not be easy. Think about enabling joins from table extensions on the fly, as we do with SetAutoCalcFields for FlowFields. It would still be hard to cover scenarios where tables are passed through an event. They will then probably not contain the joined data, so we still need to get them. Maybe similar to CalcFields? What do I know…

All code that is demonstrated here can also be found on GitHub.