CODEWITHSUNDEEP
dynamics-business-centralbusinesscentral
Jeff Brelsford
Jeff Brelsford

Reputation: 1

How to update Field in my Business Central Table Extension?

I am trying to create a new field for the "Prod. Order Routing Line" table that shows the total planned time for each operation. Total Planned Time is defined as the Setup Time + (Prod Ord Qty * Run Time). The Total Planned Time values are not updating correctly, however.

I have created a Table Extension with a new calculated field, however the values are not updating. I also created a codeunit with an EventSubscriber, and this was also unsuccessful. Below is the following code used to create the Table Extension and Code Unit.

tableextension 50109 ProdRtgLineExt extends "Prod. Order Routing Line"
{
    fields
    {
        field(50100; TotalPlannedTime; Decimal)
        {
            DataClassification = ToBeClassified;
            Caption = 'Total Planned Time';

            trigger OnValidate()
            begin
                //check if have setup or run time or both
                IF Rec."Setup Time" + "Run Time" > 0 then begin
                    Rec.TotalPlannedTime := "Setup Time" + ("Run Time" * "Input Quantity");
                    MESSAGE('OnValidate: TotalPlannedTime updated with value');
                end else begin
                    MESSAGE('OnValidate: TotalPlannedTime is 0');
                end;
            end;
        }
    }

    trigger OnBeforeInsert()
    begin
        Message('Entering OnBeforeInsert trigger');
        //check if have setup or run time or both
        IF Rec."Setup Time" + Rec."Run Time" > 0 THEN begin
            Rec.TotalPlannedTime := Rec."Setup Time" + (Rec."Run Time" * Rec."Input Quantity");
            MESSAGE('OnBeforeInsert: TotalPlannedTime updated with value');
        end else begin
            MESSAGE('OnBeforeInsert: TotalPlannedTime is 0');
        end;
    end;
}

codeunit 50110 GetTotalTime
{
    [EventSubscriber(ObjectType::Table, database::"Prod. Order Routing Line", 'OnAfterInsertEvent', '', true, true)]
    local procedure OnAfterInsertEvent_YourTable(var Rec: Record "Prod. Order Routing Line")
    begin
        IF Rec."Setup Time" + Rec."Run Time" > 0 then
            Rec.TotalPlannedTime := Rec."Setup Time" + (Rec."Run Time" * Rec."Input Quantity");
    end;

    [EventSubscriber(ObjectType::Table, database::"Prod. Order Routing Line", 'OnAfterModifyEvent', '', true, true)]
    local procedure OnAfterModifyEvent_YourTable(var Rec: Record "Prod. Order Routing Line")
    begin
        IF Rec."Setup Time" + Rec."Run Time" > 0 THEN
            Rec.TotalPlannedTime := Rec."Setup Time" + (Rec."Run Time" * Rec."Input Quantity");
    end;
}

As seen below, the new field is in the Prod. Order Routing Line table, but the only value is 0.0 Screenshot of Prod. Order Routing Line table 5409

The table has already been extended via a third party app, but it is my understanding that you can extend an already extended table. Any guidance on why this is not working and how to fix would be most appreciated.

Upvotes: 0

Views: 97

Answers (1)

kaspermoerch
kaspermoerch

Reputation: 16560

First of all you should create a procedure on your tableextension to update TotalPlannedTime. At the moment you have a lot of code duplication which is not recommended.

procedure UpdateTotalPlannedTime()
begin
    if Rec."Setup Time" + Rec."Run Time" > 0 then
        Rec.Validate(TotalPlannedTime, Rec."Setup Time" + (Rec."Run Time" * Rec."Input Quantity"));
end;

Secondly you should remove the OnValidate trigger from the field. To prevent anyone from updating the field manually you use the Editable property to disable editing. This will still allow changing the value from code.

Lastly you need to fix your EventSubscribers in order to call the new procedure at the correct points in the process.

As a general rule there are three stages to this and I will list them in the preferred order:

  1. OnAfterValidate triggers in the tableextension for the related fields i.e., Setup Time, Run Time and Input Quantity
  2. OnBeforeInsert and OnBeforeModify triggers in the tableextension
  3. EventSubscribers for OnBeforeInsertEvent and OnBeforeModifyEvent

Unfortunately the base application doesn't call the Validate, Insert or Modify triggers correctly to allow you to use step 1 or 2.

This means you are forced to use step 3 which is almost the same as you have but using the OnBefore-triggers instead.

Be aware that subscribing to the trigger events from EventSubscribers breaks the bulk insert feature which means it can have an impact on performance.

Upvotes: 0

Related Questions