For Q4 of 2014, United made the decision to align their prefixes with Lagasse’s, as United feels “Lagasse is the standard in jan-san.” Wisdom of that decision aside (hint: I didn’t find it particularly wise), it left a whole lot of dealers with some work to do.
Fortunately, OPUS handled a lot of the work. It changed item keys, updated contracts, and R-subbed the old items. Once United got their U/M issues worked out, a few days after the weekend most dealers did their Q4 update, most issues resolved themselves.
Unfortunately, OPUS does nothing for favorites lists on ECInteractive. Today, we’ll have a look at how I got around that problem.
Setting aside running reports from OPUS as to what items were changed, or picking a prefix and changing them item by item, I thought there must be a faster way. What hit me shortly was that the items were R-subbed in DDMS. This means I can pull the old items along with the new in SQL Server Management Studio:
select ltrim(rtrim(Item.MACCode)), ltrim(rtrim(Item.SKU)), Item.AlternateCode, ltrim(rtrim(Item.AlternateMACCode)), ltrim(rtrim(Item.AlternateSKU))
from Item
where Item.AlternateCode = 'R'
and (Item.MACCode = 'PAG' or Item.MACCode = 'SLO'
or Item.MACCode = 'DRA' or Item.MACCode = 'GEP'
or Item.MACCode = 'KIM' or Item.MACCode = 'COX'
or Item.MACCode = 'WTB' or Item.MACCode = 'DPR'
or Item.MACCode = 'DRC' or Item.MACCode = 'CPM'
or Item.MACCode = 'AEP' or Item.MACCode = 'GER'
or Item.MACCode = 'AHP' or Item.MACCode = 'GPK'
or Item.MACCode = 'PMP' or Item.MACCode = 'EUK'
or Item.MACCode = 'CHU' or Item.MACCode = 'AML'
or Item.MACCode = 'HTM' or Item.MACCode = 'FPI'
or Item.MACCode = 'JOJ' or Item.MACCode = 'WNS'
or Item.MACCode = 'SPG' or Item.MACCode = 'PIT'
or Item.MACCode = 'PNL' or Item.MACCode = 'HOF'
or Item.MACCode = 'BCP' or Item.MACCode = 'NCC'
or Item.MACCode = 'MRD' or Item.MACCode = 'TPK'
or Item.MACCode = 'ATP' or Item.MACCode = 'RCM'
or Item.MACCode = 'INC' or Item.MACCode = 'SBC')
order by Item.MACCode asc, Item.SKU asc
Now, this wasn’t all 53 prefixes that changed, but I didn’t even recognize a number of them. At first, I ran it on the first 8 prefixes above, which gave me 1304 items. I added the others and got to 1899 items. The difference being as comparatively small as it is, I left it like that.
From here, pull the results into Excel. As you can see above, I trimmed the item number columns. As SSMS doesn’t support trim the way Excel and various programming languages do, we have to use the rather unattractive ltrim(rtrim(column)) construct. Then, as the input file ECInteractive wants needs an action type where our Item.AlternateFlag is, select that column and replace ‘R’ with ‘C’. Save this file as CSV – Ecinteractive doesn’t specify it, but it’s CSV the mass change tools wants.
You’ll find ECinteractive’s mass change tool under Consumer Config->Favorite Item Management. What you want is almost hidden, Import from File, hiding under the Selected Items subheader. The Import from File screen has fairly thorough instructions (aside from specifying the file type). Although it recommends limiting files to 1000 items, I was making the change at 11pm on a Saturday night, so I figured the site wouldn’t be too bogged down to handle it.
Click for full size
The importer will warn on invalid items, those being items it can’t find on favorites lists. Copy them out, page by page, if you feel the need. I didn’t myself, as if it’s not on any lists, there’s no need to worry. Step through the Next prompts until the process is completed.
Click for full size
As you can probably tell, this screenshot was made during the Esselte prefix change United made in Q3, hence the different number of items.
In the end, doing this changed 460 items for me. I’d already had some changes made manually, but this knocked the rest out in 15 minutes or less, total.
So here we have found what might have been the fastest way to fix the favorites lists for the United products that changed prefixes for Q4 2014. Now that it’s on my mind, what might be next is creating a flexible way to get this information going forward, as some amount of item changes every quarter are inevitable.
As ever, I remain 
Recent Comments