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

All responses (6)

Answers (1)

Sort by

Suggested answer

Gerardo Rentería Ga... 27,623 Super User 2026 Season 2 on at

Like (1)

Report
Copy link

Link copied!

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
Philippe P 57 on at

Like (1)

Report
Copy link

Link copied!

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,
Valentin Castravet 32,808 Super User 2026 Season 2 on at

Like (1)

Report
Copy link

Link copied!

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
Philippe P 57 on at

Like (1)

Report
Copy link

Link copied!

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.
Verified answer

Valentin Castravet 32,808 Super User 2026 Season 2 on at

Like (2)

Report
Copy link

Link copied!

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
Suggested answer

YUN ZHU 103,829 Super User 2026 Season 2 on at

Like (0)

Report
Copy link

Link copied!

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