When we setup RMS we used a suppliers disk to import all their items. Recently, I used a query to delete all items that were never sold or recieved, so that I would have a more manageable item list, as there were about 16000 items that we were never going to use. Now I'm running into some instances where an ILC can't be used on a new item because the system says it's already being used. When I try to pull up that item in the Item list nothings there. However, if I create a PO and enter the ILC in quickscan, it will put in an item that has no info at all, no description, or reorder number. The cost fields are there, they just are $0. Does anyone have an idea what may have happened, and if there is a way to fix it? Thanks. Craig
ghost items
May 10, 2006
12 Replies
Try running a query against the Alias table and see if there are any orphaned records in there.
Rob
SELECT a.* FROM Alias a LEFT JOIN Item i on a.ItemID = i.ID WHERE i.ID is NULL
Before you start looking for Aliases, you might want to just go into SO Administrator and use Database/Reindex
Glenn Adams Tiber Creek C> Could you please talk me through that, as I really don't know much about > SQL. Thanks
Thanks Glenn, I had already Reindexed. I ran the query and recieved a list of hundreds of Alias's. What's the next step? Thanks for your help. Craig "Glenn Adams [MVP - Retail Mgmt]" wrote in message news: snipped-for-privacy@TK2MSFTNGP03.phx.gbl...
Craig,
Make a backup, then change the query to;
DELETE a.* FROM Alias a LEFT JOIN Item i on a.ItemID = i.ID WHERE i.ID is NULL
Haven't tried it, but it looks right. That's why I said to backup first!!
Hi Craig - restore your test DB back to the way it was before you deleted these IDs, then try this:
DELETE FROM Alias WHERE ID IN ( SELECT Alias.ID FROM Alias LEFT JOIN Item ON Alias.ItemID = Item.ID WHERE Item.ID is NULL)
As stated by the others, always make sure you make a backup of your data before running this sql statement
hth kev> I have a test sysytem that I ran this query on, DELETE Alias FROM alias
Join the Discussion
Have something to add? Share your thoughts — no account required.
Didn't find your answer?
Ask the community — no account required