So you’ve got a whole bunch of items to delete. Perhaps your procurement department got really enthusiastic about a new range of products and listed them all and, then when sales looked at them they hated them.
You don’t want to sit there for hours deleting them one at a time so what can you do?
In the old days, you’d nip into the object designer and delete what you didn’t want. But in BC you can’t.
This cropped up today as a support case for a customer of mine. One of my helpdesk colleagues was wondering about a SQL script but I’d seen a session that José Miguel Azevedo had given at BC Bootcamp last year and I knew there had to be a better way.
First: A WARNING!!!
I was pretty pleased with this solution when I came across it, so I quickly wrote up this blog post and posted it. I’d spoken about it on Twitter so I posted a link there too.
Pretty quickly some folk who’ve been doing this a lot lot longer than me – folk I really respect – chipped in with some warnings about my solution and some truly horrifying stories.
Before you start on this please test in a Sandbox or test company.
The function I describe here, using configuration packages, deletes all records in the table, then commits, and only then reloads the records in the package.
I repeat…it DELETES all records in the table and only re-imports what you tell it to. Don’t do this when anyone else is on the system. You really don’t want to run into a conflict whilst doing this.
And final piece of advice: don’t muck about with anything else whilst doing the deletions. It might be tempting to edit some records too. Don’t do it. Keep it simple.
Configuration Packages
We’re all familiar with Config Packs, right? They’re just for loading data into BC during implementations, aren’t they?
Well, no. They’re not just for that. They can help you with bulk deletions too. Let’s take a look.
1 What to delete
In my Sandbox I created a dozen copy items of the good old ATHENS desk and I want to get rid of them.
First of all I need to check that there are no outstanding orders or item ledger entries because you must make sure you don’t delete an entity that has transactions against it. I’d usually do this with Power BI (list of items with an aggregated count of item ledger entry lines)
This is important because, whilst you usually cannot delete a record with open documents or ledger entries against it, using this function you can. It’s easy to screw up here and leave orphaned ledger entries which is messy and will beg questions when the auditor comes calling.
2 Create the configuration package
I’ve created a really simple one here. It’s one table (27: Items) and I only need two fields: No. and Description.
3 Export the pack to Excel
Export the table to excel.
And then open it up.
Very, very carefully, delete the records you don’t want in BC anymore and save the spreadsheet.
4 Reimport the data
Reimport the data and, once it’s uploaded, check the “Delete Table Records Before Processing” checkbox. This will delete all the records in the table and only reimport what you’ve asked it to in the spreadsheet you’ve imported. Obviously this is a very useful but VERY dangerous feature. This is why you need to make your BULK_DELETE package clearly different and separate from all the others.
5 Apply the package
Once the package is imported you can see that there are fewer package records than Database records.
Click the “Apply Package” button in the ribbon and you then see that the deletions have occurred.
Go to Items and there they are: gone!
This can be done for any entity in BC: Customers, Vendors, Contacts; but the same rules apply. They have to have no ledger entries and mustn’t feature on any current document.
Remember…this is a very powerful feature that will permanently delete records.
ALWAYS TEST IN A SANDBOX FIRST AND DON’T PERFORM THE DELETION WHEN ANYONE ELSE IS ON THE SYSTEM.
Deleting records using Edit in Excel
I wrote the above post whilst trying to solve a problem for a client who is on BC v14 (using the Nav desktop client – sighs sentimentally). In their case I’ll have to use the solution described. However in newer versions there is a safer method that doesn’t allow the user to delete records with ledger entries.
Again, I created a dozen duplicate records. This time using the PARIS Guest Chair
Click on the Edit in Excel button
This downloads an excel file to your desktop that is connected to BC. Highlight the lines you want to delete and delete them just like you would in any excel file.
Now simply click “Publish”. This reloads the file back to BC. Your deletion of the lines passes an instruction back to BC to perform the deletions on those records.
The following message pops up to let you know what you’re about to do (as opposed to configuration packages which give you a similar message after the event!)
Having had a play around with this I can see definite benefits as compared to my earlier solution. My favourite, though, is that it will not allow you to delete anything you shouldn’t.
So if you’re lucky enough to be on a newer version (I think this is v16 onwards) this is a much safer option for bulk deletes.