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

Suggested Answer

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
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