Microsoft Dynamics NAV (Archived)

Identifying Expired Materials Through Item Ledger Entries Table

(1) Share

Report

Posted on by Community Member Microsoft Employee

Hi All,

To identify expired materials I've been running a filter on the Item Ledger Entries Table that looks like this:

Field Filter

Item No. 430*

Entry Type Output

Posting Date 01/01/01..05/02/14 (2 year shelf life and no possibility of being made before 01/01/01)

Remaining Quantity 0.001..100,000 (basically any non zero remaining quantity)

The problem I'm having is if the the item has had a positive journal adjustment this increase in quantity is not reflected in the remaining quantity field of the output line. So I can run this filter and not identify expired material if its quantity is entirely due to positive journal adjustments.

Any thoughts on how I can fix this?

Thanks!

Patrick

*This post is locked for comments

All responses (17)

Answers (3)

Suggested answer

Alexander Ermakov 28,094 on at

Like (0)

Report

RE: Identifying Expired Materials Through Item Ledger Entries Table

In Russian localization there are very handy functionalities called "Turnover Sheets". These are the pages for each balance sheet item (something that has a card), e.g. for customers, vendors, suppliers, G/L accounts, FA, etc., and also for items. That page is basically a list of all items with columns Opening Balance, Debit, Credit and Closing Balance. "Item G/L Turnover Sheet" shows those columns for both value and quantity (thus, there are 8 columns). You can filter it by any period, and other analytics such as e.g. location.

So it is very easy to see what was the opening balance for the period, how many items had left and at what cost, and what has been left at the end of the period. Since it is a page, you can drill down to figures to see the details and navigate to documents. The page is build on standard functionalities.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Identifying Expired Materials Through Item Ledger Entries Table

[quote]

Utilizing the remaining quantity, you'll know what quantities are in your warehouse and at what cost.

[/quote]

the question wasn't what dollar value of an item was remaining, it was what QUANTITY remained.

seems like a very simple question, asked many times a day.
Suggested answer

Alex Chow 4,481 on at

Like (1)

Report

RE: Identifying Expired Materials Through Item Ledger Entries Table

The problem is the costing layers per lot. For example, if you move part of a lot into a separate warehouse, you can allocate a landed cost into the item that you moved. So even if they're in the same lot, the cost is different. When you sell the item, the proper cost should be allocated for the sale.

Utilizing the remaining quantity, you'll know what quantities are in your warehouse and at what cost.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Identifying Expired Materials Through Item Ledger Entries Table

Well, why aren't the remaining quantities the same?

It seem like the remaining quantity field should just sum all the positive and negative changes to the lot, telling you the overall remaining quantity of that lot. This way is a distinction without a difference and makes the remaining quantity field a lot less useful.
Alex Chow 4,481 on at

Like (0)

Report

RE: Identifying Expired Materials Through Item Ledger Entries Table

I don't understand your question. Which part do you see not balancing?
Community Member Microsoft Employee on at

Like (0)

Report

RE: Identifying Expired Materials Through Item Ledger Entries Table

so, outside the world of NAV developers and query writers, what is an average user supposed to do?

just throw up their arms and accept that inventory balances in one spot don't match inventory balances somewhere else?
Verified answer

Alexander Ermakov 28,094 on at

Like (0)

Report

RE: Identifying Expired Materials Through Item Ledger Entries Table

Yes, as I mentioned, applying Expiration Date would suit your needs better :-).

Another option is to make a query that would calculate the remaining quantity of a certain item and lot from the time it has been posted, by summing up the Quantity field.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Identifying Expired Materials Through Item Ledger Entries Table

Here's a screen shot of whats going on. The remaining quantity field in the output line for lot 001 of item 730001 is 0 but the actually remaining quantity is the sum of the two positive adjustments. I was missing the quantity accounted for by the positive adjustments because I was filtering just the non zero output lines..

Not sure why it does it this way (you'd think the remaining quantity should be the same for all lines referring to the same lot) but I was able to get what I need by assigning expiration time frames in the item card (thanks for the help on that btw) and then filtering by that.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Identifying Expired Materials Through Item Ledger Entries Table

[quote]The problem I'm having is if the the item has had a positive journal adjustment this increase in quantity is not reflected in the remaining quantity field of the output line. So I can run this filter and not identify expired material if its quantity is entirely due to positive journal adjustments. [/quote]

What's up with this?

If you make a positive adjustment, why isn't that reflected in the remaining quantity field?
Verified answer

Alex Chow 4,481 on at

Like (0)

Report

RE: Identifying Expired Materials Through Item Ledger Entries Table

Hi Patrick,

Filtering on the Posting Date would not give you what you need because that's the transaction date of the item entry, not when it's first produced.

What it sounds like you need is a 2nd field so you can keep track of the date that the item will expire. As Alexander suggested, utilizing the expiration date in NAV will help you keep track of the expiration date.

TDLR; filter on the expiration date, not posting date.