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).
Business Applications communities