Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Field to filter Items ...
Small and medium business | Business Central, N...
Answered

Field to filter Items with no ledger entry

(1) ShareShare
ReportReport
Posted on by Philippe P 55
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
Dynamics 365 Business Central
Dynamics 365 Business Central
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 75,848 Super User 2024 Season 2 on at
    Field to filter Items with no ledger entry
    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)
     
    Hope this helps.
    Thanks.
    ZHU
  • Verified answer
    Valentin Castravet Profile Picture
    Valentin Castravet 25,186 Super User 2024 Season 2 on at
    Field to filter Items with no ledger entry
    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
  • Philippe P Profile Picture
    Philippe P 55 on at
    Field to filter Items with no ledger entry
    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. 
  • Valentin Castravet Profile Picture
    Valentin Castravet 25,186 Super User 2024 Season 2 on at
    Field to filter Items with no ledger entry
    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 Profile Picture
    Philippe P 55 on at
    Field to filter Items with no ledger entry
    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, 
  • Suggested answer
    gdrenteria Profile Picture
    gdrenteria 13,057 Most Valuable Professional on at
    Field to filter Items with no ledger entry
    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.
    Best regards
    Gerardo

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,711 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,458 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans