SBX - Search With Button

SBX - Forum Post Title

Add Dynamically Calculated Field to Sales Document Lines

Dynamics 365 Business Central Forum

Drew Kipfer asked a question on 8 Nov 2018 4:03 PM
My Badges

Question Status

Verified

What is the best way to add calculated fields to Sales Document Lines? For example, I would like to be able to dynamically display Total Cost (Qty * Unit Cost) and Margin % ( (Profit/Revenue) *100) on each Sales Document Line. I see where I can add a CalcFormula, but that seems to be limited in its operators.

Reply
Suggested Answer

You can create the fields you want with a tableextension object and populate these field value by subscribing OnAfterValidate of the other fields on which the calculation in based (for example Qty and Unit Cost).

Reply
Drew Kipfer responded on 9 Nov 2018 7:41 AM
My Badges

Does the OnValidate trigger extend the other fields (Qty/Unit Cost), or the field I'm adding?

For instance:

field(50100, "Total Cost"; Decimal)

{
trigger OnValidate ();

Reply
Suggested Answer

No. You need to create a custom codeunit with an event subscriber to the OnAfterValidate event of the Quantity field in the Sales Line table and here perform the calculation: Total Cost := Quantity/Unit Cost.

The same event subscriber must be added also for the Unit Cost field in the Sales Line table (if you edit this).

Reply
Drew Kipfer responded on 9 Nov 2018 11:08 AM
My Badges

I was able to get this to work using the method below, but do you mind explaining why this is not the correct/best approach?

First I extended my Sales Line table with a Total Cost field. Then I extended the Sales Quote Subform to include the following:

modify(Quantity)

{

    trigger OnBeforeValidate()

    begin

         "Total Cost" := "Unit Cost" * Quantity;

    end;

}

There is a similar trigger in place for the Unit Cost field that updates Total Cost using OnValidate.

Reply
Suggested Answer

OnBeforeValidate for me is not correct before it’s triggered before inserting a value in the Quantity field. OnAfterValidate is better. Here you have created a tableextension object, in my suggestion only a custom function in a codeunit (event subscriber).

Reply
Drew Kipfer responded on 19 Dec 2018 8:53 AM
My Badges
I thought I would share how I ended up getting this to work. I am not sure if this follows best practice or makes it "as local as possible", so any feedback in that regard would be much appreciated!


codeunit 50102 TotalCostOrderPub { trigger OnRun() begin end; [IntegrationEvent(false, false)] local procedure OnAfterValidate(var r: Record "Sales Line") begin end; } codeunit 50103 TotalCostOrderSub { trigger OnRun() begin end; [EventSubscriber(ObjectType::Table, Database::"Sales Line", 'OnAfterValidateEvent', 'Unit Cost', false, false)] local procedure UnitCostChange(var Rec: Record "Sales Line") begin Rec.TotalCost := Rec."Unit Cost" * Rec.Quantity; If (Rec."Unit Price" > 0) then Rec.Margin := ((Rec."Unit Price" - Rec."Unit Cost") / Rec."Unit Price") * 100; end; [EventSubscriber(ObjectType::Table, Database::"Sales Line", 'OnAfterValidateEvent', 'Quantity', false, false)] local procedure QtyChange(var Rec: Record "Sales Line") begin Rec.TotalCost := Rec."Unit Cost" * Rec.Quantity; end; [EventSubscriber(ObjectType::Table, Database::"Sales Line", 'OnAfterValidateEvent', 'Unit Price', false, false)] local procedure UnitPriceChange(var Rec: Record "Sales Line") begin If (Rec."Unit Price" > 0) then Rec.Margin := ((Rec."Unit Price" - Rec."Unit Cost") / Rec."Unit Price") * 100; end; }
Reply
Verified Answer

For me these two event subscribers are ok:

[EventSubscriber(ObjectType::Table, Database::"Sales Line", 'OnAfterValidateEvent', 'Quantity', false, false)]

   local procedure QtyChange(var Rec: Record "Sales Line")

   begin

       Rec.TotalCost := Rec."Unit Cost" * Rec.Quantity;

   end;

   [EventSubscriber(ObjectType::Table, Database::"Sales Line", 'OnAfterValidateEvent', 'Unit Price', false, false)]

   local procedure UnitPriceChange(var Rec: Record "Sales Line")

   begin

       If (Rec."Unit Price" > 0) then Rec.Margin := ((Rec."Unit Price" - Rec."Unit Cost") / Rec."Unit Price") * 100;

   end;

Here you're handling the customization at table level (subscribing to a standard event) and this is the best approach. Why for Unit Cost you've subscribed a Page event and not the table's event as the other field?

Reply
Drew Kipfer responded on 19 Dec 2018 9:40 AM
My Badges

Thanks for pointing that out! Error on my part. I corrected my original post.

Reply
Verified Answer

For me these two event subscribers are ok:

[EventSubscriber(ObjectType::Table, Database::"Sales Line", 'OnAfterValidateEvent', 'Quantity', false, false)]

   local procedure QtyChange(var Rec: Record "Sales Line")

   begin

       Rec.TotalCost := Rec."Unit Cost" * Rec.Quantity;

   end;

   [EventSubscriber(ObjectType::Table, Database::"Sales Line", 'OnAfterValidateEvent', 'Unit Price', false, false)]

   local procedure UnitPriceChange(var Rec: Record "Sales Line")

   begin

       If (Rec."Unit Price" > 0) then Rec.Margin := ((Rec."Unit Price" - Rec."Unit Cost") / Rec."Unit Price") * 100;

   end;

Here you're handling the customization at table level (subscribing to a standard event) and this is the best approach. Why for Unit Cost you've subscribed a Page event and not the table's event as the other field?

Reply
Suggested Answer

You can create the fields you want with a tableextension object and populate these field value by subscribing OnAfterValidate of the other fields on which the calculation in based (for example Qty and Unit Cost).

Reply
Suggested Answer

No. You need to create a custom codeunit with an event subscriber to the OnAfterValidate event of the Quantity field in the Sales Line table and here perform the calculation: Total Cost := Quantity/Unit Cost.

The same event subscriber must be added also for the Unit Cost field in the Sales Line table (if you edit this).

Reply
Suggested Answer

OnBeforeValidate for me is not correct before it’s triggered before inserting a value in the Quantity field. OnAfterValidate is better. Here you have created a tableextension object, in my suggestion only a custom function in a codeunit (event subscriber).

Reply

SBX - Two Col Forum

SBX - Migrated JS