Reusing Delete items lookup codes...

I have deleted items in the past and now would like to reuse the lookup code. The program tells me the Item lookup code must be unique. If the old item was deleted, and i do not have any items with that lookup code being used; then how is the lookup code not unique?

HELP!!!

Thanks

Reply to
Kyle
Loading thread data ...

Deleting items is bad. If the items had any sort of history, you are going to cause yourself problems...

Try this query: SELECT * from Item WHERE ItemLookupCode = 'abc123'

Substitute the actual ILC for 'abc123', you will need the single quotes.

If you get no results, you've got problems. If your item shows up in the query results, it's probably just inactive. Use the inventory wizard to re-activate it.

Didn't you post about this topic once before?

Tom

Reply to
Terrible Tom

You hay also have the lookup code used as an ALIAS. since an alias is essentially an additional lookupcode that is assigned to an item, it is included in the uniqueness check.

SELECT * from Alias WHERE Alias = 'abc123'

Glenn Adams Tiber Creek C> Deleting items is bad. If the items had any sort of history, you are going

Reply to
Glenn Adams [MVP - Retail Mgmt

I know when I deleted a bunch of items before I knew better there was a bunch of aliases that became orphaned. I had to use a query to get rid of them. Craig

"Glenn Adams [MVP - Retail Mgmt]" wrote in message news: snipped-for-privacy@TK2MSFTNGP02.phx.gbl...

Reply to
Craig

So did deleting the orphaned Alias' resolve your issue?

Tom

Reply to
Terrible Tom

Sorry, I should have added that deleting the orphaned alias's solved the problem. I was able to reuse those ILC's. It's been a while so I don't remember the query. I will look to see if I have it saved somewhere. Basically it deleted all alias that weren't associated with an item, and there were a lot of them because I had used a query to delete a suppliers catalogue where all items that hadn't been recieved or sold within the last

3 years were deleted. I learned the hard way not to delete items with a query. Craig
Reply to
Craig

Here is a select query to show you any orphaned Aliases before running the delete query

SELECT Alias.Alias, Alias.ItemID, Item.ID, Item.ItemLookupCode FROM Alias LEFT JOIN Item ON Alias.ItemID = Item.ID WHERE (((Item.ID) Is Null));

Here is a query that will delete orphaned Aliases

DELETE FROM Alias FROM Alias LEFT JOIN Item ON Alias.ItemID = Item.ID WHERE (((Item.ID) Is Null));

Test all queries on a test database and check the results before processing on your live database. Please ensure you back-up your live database before running any queries.

Regards Michael (Brisbane, Australia)

Reply to
Michael

I have run the Query's but still cannot find the item lookup code. Its like they are in there but we cannot see them...

Kyle

"Craig" wrote:

Reply to
Kyle

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.