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.
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).
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 ();
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).
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.
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).
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; }
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")
Rec.TotalCost := Rec."Unit Cost" * Rec.Quantity;
[EventSubscriber(ObjectType::Table, Database::"Sales Line", 'OnAfterValidateEvent', 'Unit Price', false, false)]
local procedure UnitPriceChange(var Rec: Record "Sales Line")
If (Rec."Unit Price" > 0) then Rec.Margin := ((Rec."Unit Price" - Rec."Unit Cost") / Rec."Unit Price") * 100;
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?
Thanks for pointing that out! Error on my part. I corrected my original post.
Business Applications communities