How do I delete aliases at the store level?

I think I found the answer to my question with this fix, but can someone translate this into Lehman's terms for me? I'm not sure what the 'A & B' is.

RR Alias Fix

Environment: HQ

Background: The item record is updated by Deleting an Alias Code. The HQ

250 worksheet cannot update this change. So at the store level the item is still linked to that Alias. If you then add the Alias to another item this will cause errors at store and communications.

Resolution: Edit the DeleteAlias.sql in notepad. Change the A & B by typing manually the Alias Codes that you wish to delete. If you have more that 2 you can copy paste the statement for B as many time as required and change the B to the next alias code to be deleted at store. Run DeleteAlias.sql script file using HQ Administrator.

Result: If successfully executed, RR Alias Fix creates WS 51 for all stores with SQL statements to be run at store to delete all item alias' listed at store. Please note that none of the items are deleted from HQ or Store. RR Alias Fix redefines association of individual items with Alias at store.

Note: No action is required at store side

Thank you for any help you can provide.

Chris,

If you're fairly familiar with Excel, I'll give you some more general advice.

If you get the query export data onto one worksheet, you can use the VLOOKUP function (use the function wizard if you're unfamiliar with that particular function) to find the IDs on the Item table that exist on the Alias table.

Once you have that information, you can use a text formula (the formula with the quotes and ampersands) or the CONCATENATE command (again, function wizard). to build a query statement for each Alias record that you want to delete.

If you are still confused, send me an e-mail and I will attach a spreadsheet with example formulas.

Tom

"Chris W." wrote:

We have hundreds of items in our database which we no longer use but would like to keep for sales history purposes. These items still have aliases associated with them. As we receive new items we input the SKUs as aliases in Headquarters Manager. Although RMS should not allow the same alias to be entered for more than one item, we have multiple new items that appear as an old item which has the same SKU as the new item when scanned in POS.

In an effort to eliminate this problem, we went through and manually deleted all the aliases from the old items we no longer wish to use in Headquarters Manager and ran a Style 250 worksheet. Unfortunately, this has not in turn made the corresponding changes at the store level. All of the aliases still appear under the item properties at the store level, despite having been deleted in Headquarters Manager.

We also tried to make the items inactive using Headquarters Manager. The changes did affect Store Operations Manager at the store level, yet the inactive item still scans as if it were active when scanned in POS.

Surely there is a way to avoid having to manually delete all of the aliases from our hundreds of old items at each of our 16 stores. Can you please offer us a remedy for this matter??

Chris,

Anytime you delete a record from a table, HQ cannot transmit the change to the stores because the modified record no longer exists...

This is a MAJOR SHORTCOMING. I have absolutely NO HOPE that MS will fix this issue, ever, as it is inherent to the design of the system.

Is this stupid? Yes.

In the meantime, we are left to fend for ourselves...

If I were you, I would do the following:

Before you do this, know that the following procedure should never be attempted by anybody, ever. If you try this and fail, your alias table may be ruined for all practical purposes. If you are careful and do it right, it will work. In any case, you are on your own. What I really mean is this:

CONTINUE AT YOUR OWN RISK.

From HQ Admin: Query #1: SELECT ID, ItemlookupCode from Item WHERE Inactive = 1 Query #2: SELECT * from Alias

Export both results as .csv files.

Open the .csv files in some sort of text editor (Word, Wordpad, Notepad). Select All, copy & paste into Excel. If you don't have any leading zeroes anywhere, this step may be unnecessary.

You can use Text to Columns... to get rid of the commas & spaces. You can use TRIM() to get rid of leading/trailing spaces if necessary. Clean up your data!

Columns A & B will be from the ID & ILC from the Item Table. Leave Column C empty just for your own sanity Columns D, E & F will be ID, ItemID and Alias from the Alias Table

Sort Columns A & B by ID Sort Columns D-F by ID

In Column G, enter the following formula: =VLOOKUP([Item.ID],[Alias.ItemID],1,FALSE) Where Item.ID = Cell A[x] where X is the current row and Alias.ItemID is the entire Alias Column (column F). The lookup array will have to be an absolute reference like this: $F$2:$F$4567 (assuming you had 4567 rows of data).

Fill Down Column G for all Alias records. This will identify all Alias records for inactive items.

Copy Column G. Paste Special... Values Only into Column I. Sort Column I to separate the good values from the bad.

In Column J, enter: ="DELETE from Alias where ItemID = "&I[x] Where I[x] is the active row cell from Column I.

Fill down to create a series of SQL statements.

Copy the list into a blank query inm HQ Admin. Executing this collection of query statements will delete the Alias' associated with the inactive ILCs.

Copy the whole list into a WS51 if you cannot connecgt directly to the store ops databases with SO Admin (try it, you might be able to connect!).

I hope that helps. There is probably some way to do this with one big SQL statement, but I'm sooo much more comfortable with Excel than with SQL.

You will want to backup everything first, of course.

If you have any questions or are the least bit hesitant, DON"T DO IT. Ask for more help or find another method.

Good luck, Tom

Please help, anybody?

Not a SQL expert, I started reading up on SQL a few months ago, so, as Terrible Tom has so famously remarked... PROCEED AT YOUR OWN RISK

Anyway, there appears to be two tables involved - the item table where you track whether the item is inactive or not and the alias table where you track all the different aliases that are assigned to your items in inventory, whether they are inactive or not...

If I understand you correctly, you are trying to delete all the aliases that are assigned to inactive items, which can't be done in the manager program since the item has been marked inactive; I tried to come up with a single delete statement that would only delete aliases assigned to itemid values that appear as inactive in the item table but to no avail....hopefully a more experienced SQL user can post if at all possible, otherwise you'll need to two-step this one.

First run the following query select item.id, item.itemlookupcode, item.description, item.inactive, alias.alias from item, alias where item.id = alias.itemid and item.inactive = 1;

this will show you all the items that are flagged as inactive AND have an alias assigned; jot down all the itemid numbers that appear (or export your results to notepad or excel using the file - export command in SO administrator...

Then run the following to flush out these aliases..... delete from alias where itemid in (a, b, c, d, e, x)

the a, b, c, d, e, x will be the itemid's that you jot down, since these are the itemid's contained in the aliases you want deleted...the "in" operand as I understand it is your way of telling the db that you want all aliases deleted that have an itemid included in the list in parentheses...test some of these flushed out aliases in POS to make sure your delete command worked.....

If you have multiple stores you can save your queries (when you close the query window it will ask you if you want to save the query) and then send the query file (the query will have a sql file extension) to all your stores via file transfer or email attachment....then open SO administrator at each store, open the queries and run them....in theory the store databases should be the same in terms of the items that are flagged as inactive AND have aliases, but if there's reason to doubt, you may want to run the first SELECT query and edit the second DELETE command based on the results of your SELECT query.....

BACKUP FIRST......!

"Chris W." wrote:

HI Chris, I have read all these, look to me trouble that how come you have been givien everything the tough job. anyhow I read the excel adviced looks ok but time consuming and also tricky, the second sql script is okay but it should also be written correctly.

What you need to do is to delete all the alias from alias table for those items which are inactive. I am writing for you simple query no need for to remember which item to delete or not. This simple script will does all you need here is the script.

delete from alias from item, alias where item.id = alias.itemid and item.inactive = 0;

If help you rate me.

"Chris W." wrote:

That will delete ALL aliases for the given item, not just the specific Alias that was removed - an Item can have many aliases. Also, this needs to be done at each store - the fix that RR is providing created a Style 51 worksheet at HQ and pushes it at all stores at once.

The 'A' and 'B' referred to in the instructions are place holders for the Aliases that are to be removed. You would enter each Alias on a separate line. The provided fix provides space to remove 2 aliases; if you have more than that, you can just copy and paste one of the statements and add as many as you need. Retail Realm has their own Forum on their site

- you will have to register on the site to access the forums, but you may find more direct help there.

If you are not familiar with SQL, I highly recommend that you contact your reseller for help. As Tom pointed out, you are at your own risk here...

Good Luck!

Glenn Adams Tiber Creek C> Chris,

Ignore me... I started reading in the middle of the thread and didn't notice you were trying to drop all aliases for inactive items.

Glenn Adams Tiber Creek C> That will delete ALL aliases for the given item, not just the specific

Thanks for the script Akber. It did help, after we found out that it deleted the aliases from all of the active items, not the inactives. So, what we ended up doing was making all of the items whose aliases we wanted deleted active, deleted the aliases with the query then made the actives inactive and the inactives active. It added an extra step but we were still able to get it done. Thanks for your help.

The correct statement for Inactive items is as follows

DELETE FROM Alias FROM Item, Alias WHERE Item.ID = Alias.ItemID AND Item.Inactive = 1

I would run the following select statement first to check the Aliases that the above statement will delete

SELECT ItemLookupCode, Description, ItemID, Alias FROM Item, Alias WHERE Item.Id = Alias.Itemid AND Item.Inactive = 1 ORDER BY ItemID, Alias

Regards Michael (Brisbane, Australia)