ghost items

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

Reply to
Craig
Loading thread data ...

Try running a query against the Alias table and see if there are any orphaned records in there.

Rob

Reply to
Rob

Reply to
Craig

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

Reply to
Glenn Adams [MVP - Retail Mgmt]

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

Reply to
Craig

Reply to
Craig

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!!

Reply to
Jeff

Reply to
Craig

Reply to
Craig

Reply to
Craig

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

Reply to
kskinne

Reply to
Craig

Reply to
Craig

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.