430
Hi All
I am just wondering if someone has gone through the exercise of deleting un-used items, item currencies, Item creditors and other un-used information relating to items.
Would anyone be able to just confirm in me thinking that every item in IV00101 table that does not exist in IV30300 table are the items that have never been used ?
select * from IV00101 where ITEMNMBR NOT IN (select ITEMNMBR from IV30300)
Many Thanks
*This post is locked for comments
Use a Macro to delete the items you want to delete.
Build the list you want to delete using your SQL statement above. Then use this list of Item numbers as the basis of a 'delete' Macro. (Lots of posts online on how to create GP Macros - but if you're having difficulty - send me an email and I can create the macro for you).
For sure the Macro may fail at some stage, but this is good - means you won't delete an Item that should not be deleted. When the macro fails, not the item number, open the macro in a text editor, find the item number and delete the Macro lines up to that point, then rerun the Macro.
For sure if 90% of the items should not be deleted, you will be editing Macro's all day - but, maybe, only a few will cause it to fail, and so it will be workable.
Ian.
Richard the reason why i didnt look at the quantity sold life to date or any of the sale quantities in any of the tables is because even though an item might never have been sold, it might have been purchased and that would affect the inventory along with item transactions on the item.
I think that the IV30300 table is a super set of all transactions that have been performed on an item.
I do agreee with both of you, that a lot of tables need to be cleared up which i will, i just need some sort of confirmation on if i am missing anything else in particular. As for concerns about leaving orpahn records, i would have thought that running checklinks should clear up most stuff.
I have already tried deleting the records on the test environment and then ran check links and that just recreated the items because i hadnt cleaned up the remaining tables but im assuming once i have deleted the sites and creditor items and some other tables, it would clear out the rest when i run check links.
The task would have been simpler if i didnt have an integrated CRM which i need to bring inline with the financial application.
You need to delete the item through the Item master and not use SQL statements. You can delete the sites using a script found on our website (www.AccoladePublications.com in the Tips and Tricks section). If the item will not delete, it has been used! Also, there are dozens of tables that need to be touched. Missing one leaves orphans, deleting a used item leaves orphaned transactions.
There is a field called quantity sold life to date that will tell you if the item has been used. Also if there is a record in the IV10200 table, it has been used.
Just to be 100% certain, I would run that script on all IV Transactions tables - IV10000 through IV30301.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (