Calcualtion of a new field

Hi all,

I created a new field “DecimalField” In Item Ledger Entry. This new field should be calculation of item ledger entries fields : Cost Amount (Actual) divided by Invoiced Quantity .

I create e function in T.Item Ledger Entry and call in on the OnValidate Trigger of the new field.

My code:

DecimalField:= 0;
ILE.SETCURRENTKEY(“Item No.”);
ILE.SETRANGE(“Item No.”,“Item No.”);
ILE.CALCSUMS(“Cost Amount (Actual)”);
IF ILE.FINDSET THEN BEGIN
DecimalField:= ILE.“Cost Amount (Actual)” / ILE.“Invoiced Quantity”;
END;

But it doesnt work.

Please can you help me were i get lost?

BR

Can you please try ILE.CALCFIELDS(ILE.“Cost Amount (Actual)”) in findset condition…


IF ILE.FINDSET THEN BEGIN

REPEAT

ILE.CALCFIELDS(ILE.“Cost Amount (Actual)”);
DecimalField:= ILE.“Cost Amount (Actual)” / ILE.“Invoiced Quantity”;

Until ILE.Next= 0;

END;

I tried this, but it doesn’t work. [emoticon:ca08b2c27c2f40e993e89508acf29e0b]

In a first look it seems so easy but is not working [emoticon:ca08b2c27c2f40e993e89508acf29e0b]

First: The “Cost Amount (Actual)” field is not in the SumIndexField, look it up on the properties.

Second: It cannot be added because it is a calculated field.

So you will want to locate where the Item Ledgers are being entered and place your new code inside of that

Create a codeunit to update the history so you just have to run it once

  • check out codeunit 10201

Sorry, but almost all assumptions in this code are wrong.

And I tell you why:

  1. You added this code in the “OnValidate” trigger of the “DecimalField”.
    This code is being executed when anybody writes a value into the field. This will not happen, s your code is not getting executed.

  2. You tried to divide the total costs of all item ledger entries (= total of all purchases and all sales and all positive and negative adjustments ever done) by the Invoiced Quantity of the first record.

  3. You tried to do a calcums on a flowfield. A flowfield does not exist in the database, therefore it cannot be “Summed” up by a calcsums command.

If you want to have the “Cost per Unit” written into the DecimalField, you should:

Add some new Local Procedure to a codeunit, call the properties of those procedure and make them Event Subscribers.
Point to Table 32, OnBeforeInsert
Point to Table 32, OnBeforeModify
Point ot Table 5802, OnBeforeInsert

The first two should caluclate the value directly:

IF Rec.“Invoiced Quantity” <> 0 THEN BEGIN
Rec.CALCFIELDS(“Cost Amount (Actual)”);
Rec.DecimalField := Rec.“Cost Amount (Actual)” / Rec.“Invoiced Quantity”; // You could also want to call a ROUND to get a better value
END;

The third one must get the right Item Ledger Entry, (based on the “Item Ledger Entry No.” of the Value Entry and call the same code as above with the ILE, then don’t forget to MODIFY the ILE.

Why do I suggest this way:

Whenever a new Value Entry is being written to the database (an item ledger entry is revalued), the Cost per Unit changes. Therefore you would have to recalculate the value every time a new Value Entry is created.

Why I should use a codeunit? Is it not possible just to use T. Item Ledger Entry.

  1. You cannot create an event subscriber in the table.

  2. There is no way you can use to trigger your code in the table. NAV does not call any code in the table Item Ledger Entry during the post process.

  3. You would have to modify code in NAV (CU22) directly which is evil.

So, I should use CU.22 Item Jnl.-Post Line to do update my new field in T.32 Item Ledger Entry.

What Thomas said, was that you should not modify CU 22 directly!! Of course that would require that you use NAV 2016 or later, to have the ability to use Event subscribers.