Field to filter Items with no ledger entry

(1) Share

Report

Posted on by Philippe P

Hi,

I'm looking to delete all the items in our system that are unused. To do so, those items must not have any item ledger entries during the fiscal period.

I'm wondering if there is a field on the item table that would allows me to filter every items who doesnt have any item ledger entries?

Much obligated,

Philippe P

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (6)

Answers (1)

Suggested answer

Gerardo Rentería Ga... 25,225 Most Valuable Professional on at

Like (1)

Report

Hi

I would say that you should create a flowfield type column of type Exist, to verify that you do not have any records in that table.
Here is a link that may give you an idea.

Dynamics 365 Business Central: FlowFields (Sum, Average, Exist, Count, Min, Max, Lookup) | Dynamics 365 Lab (yzhums.com)

Best regards

Gerardo

Was this reply helpful? Yes No
Philippe P 55 on at

Like (1)

Report

It is not a bad idea gdrenteria,

If possible I'd rather not add a flow field to my item table which is already pretty heavy. I was hoping that maybe another set of filter coud give me directly that information.

In the case there isn't (I didn't find one). I'll probably do it that way by adding a flow field.

Thank you,

Was this reply helpful? Yes No
Valentin Castravet 31,452 Super User 2025 Season 2 on at

Like (1)

Report

If this is a one off I would just do it in excel as it would only take a few minutes. Export both the item list to excel and the item ledger entries. In the item list excel file do a vlookup or a similar formula to see if that item no. exists in the item ledger entries excel file.

Valentin Castravet

Zander ERP Services

Was this reply helpful? Yes No
Philippe P 55 on at

Like (1)

Report

Hi Valentin,

This is a one off for now but I presume that I will be deleting items after every fiscal year to maintain the integrity of data. I agree that your method might be more viable, maybe just pull 2 O'data flow and make a recurring report would be the ideal solution.

From both your answer so far I'm presuming there is no such field that would simply allow me to filter on directly to see which one can be deleted without issue.

Thank you both for your help.

Was this reply helpful? Yes No
Verified answer

Valentin Castravet 31,452 Super User 2025 Season 2 on at

Like (2)

Report

No, no such field.

Also, I would only recommend deleting items if they've had zero item ledger entries ever, rather than no entries during the fiscal period. Deleting an item only deletes the item card but maintains all the entries associated with that item, so in the inventory subledger your entries would remain but the item no. field would be blank. You never know if in the future you will need to look at these entries, and in terms of an audit trail, this will make it very difficult. For example:

Best practice would be to block these unused items, you can also add a variation of Z or ZZ in front of the item number so they are all sitting at the bottom of the item list. If an item is blocked it also doesn't appear in any of the item dropdowns (i.e. in a purchase order when you go to select an item, it only shows items that are not blocked for purchasing).

Valentin Castravet

Zander ERP Services

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 95,681 Super User 2025 Season 2 on at

Like (0)

Report

Just adding some information.
If you are a developer, instead of adding a flowfield, it is better to create a batch process directly.

For example,

In addition, I also recommend using the Block function.

More details:

Dynamics 365 Business Central: How to block items (Block, Sales Blocked, and Purchasing Blocked)

https://yzhums.com/18968/

Hope this helps.

Thanks.

ZHU

Was this reply helpful? Yes No