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