Small and medium business | Business Central, N...

How to get Last posting date of items in Item Ledger Entry

(0) Share

Report

Posted on by AJTR

Hello Dynamics Community.

I am actually customizing a report where I need to get the last posting date of item in the item ledger entry table whether the entry type is sale, purchase, positive adjmt, negative adjmt, etc....

So my dataitem is the Item Ledger Entry table and I have 3 colums: Item No., Posting Date and Entry Type.

I have defined PostingDate as variable date in C/AL globals.

On PreReport trigger i have inserted this code:

"Item Ledger Entry".SETCURRENTKEY("Item No.","Posting Date","Entry Type");
"Item Ledger Entry".SETRANGE("Item No.","Item Ledger Entry"."Item No.");
"Item Ledger Entry".SETRANGE("Entry Type","Item Ledger Entry"."Entry Type"::" ");
IF "Item Ledger Entry".FINDLAST THEN
PostingDate:= "Item Ledger Entry"."Posting Date";

But my report is showing all items with their all posting dates. It is not taking the last posting date of an item. It is taking all the posting dates. For example, for an item No. 1100, it has entry type and posting dates as Consumption (9/8/2019), Consumption (9/7/2019),Consumption(9/6/2019) and Postive Adjmt (6/1/2018). The report is taking all these. It is not taking only last entry type and posting date that is Consumption (9/8/2019).

Grateful if someone tell me how can I achieve the following above?

Thanks to help me.

All responses (4)

Answers (0)

Suggested answer

YUN ZHU 74,036 Super User 2024 Season 2 on at

Like (0)

Report

RE: How to get Last posting date of items in Item Ledger Entry

Hi, You can modify your code as suggested by Daniele. My suggestion is that you can add Last posting date of item in the item ledger entry like those two standard fields, not used them directly.
Then after adding the DataItem of Item in the report, you can get it.

Hope this helps.
Thanks.

ZHU
Suggested answer

DAnny3211 9,272 Super User 2024 Season 1 on at

Like (0)

Report

RE: How to get Last posting date of items in Item Ledger Entry

hello

declare another var record of the item ledger entry

example:

ItemLedgerEntry: 'item ledger entry' record

and then do this

ItemLedgerEntry.SETCURRENTKEY("Item No.", "Posting Date", "Entry Type");

ItemLedgerEntry.SETRANGE("Item No.", "Item Ledger Entry". "Item No.");

ItemLedgerEntry.SETRANGE("Entry Type", "Item Ledger Entry". "Entry Type"::" ");

IF ItemLedgerEntry.FINDLAST THEN

PostingDate:= ItemLedgerEntry. "Posting Date";

although I don't understand one thing.

if the code you made is in the onprereport, how do you know which item is the filter'?

DAniele
AJTR on at

Like (0)

Report

RE: How to get Last posting date of items in Item Ledger Entry

Hello Mr Zhu,

Actually my client wants to have the last date when an item has been moved in the report. That is the posting date should contain the last date / recent date of that item which have been moved.

Should I take it from the item ledger entry or the physical inventory ledger entry?

Grateful if you could advise me on this.

Thanks.
Suggested answer

YUN ZHU 74,036 Super User 2024 Season 2 on at

Like (0)

Report

RE: How to get Last posting date of items in Item Ledger Entry

Hi, This is the same variable? Or is it the same DataItem?

I think there is a problem with this Setrange, you have not filtered out the correct value, you can export it to Excel to have a look.

This is the same variable? Or is it the same DataItem? I think there is a problem with this Setrange, you have not filtered out the correct value, you can export it to Excel to have a look.
As for the last date, you can also refer to the following fields on the Item. With Flowfield, this is also very convenient.

Hope this helps.

Thanks.

ZHU