Sage 100

So, I have a client that went out and rented hand held devices from their old Cougar Mountain Rep. That rep nor the client thought to talk to me. That may say something about our relationship that I need to fix. Many of the barcodes are alias items. I now how and can import the counted counts against the frozen counts. The issue is I need to know if there is something I can do to address the failed records because they are alias numbers. Of course this client is also on 4.3 (pre framework).

Are they at least on a current support plan??? Sounds like there are several topics of conversation in your future..... Dump everything into Excel. Add the CI item table to a sheet. Add the alias item table to another sheet. Write an if statement formula using a vlookup to look in the ci item table first for a value. If N/A, then use a vlookup to look in the alias table for the item code. Something like below. =IFERROR(VLOOKUP(C1651,ci_item,1,FALSE),VLOOKUP(C1651,alias,2,FALSE)) This is what I use at a client for their physical inventory count. They scan the bar code tag on the tire which is the manufacturers number. Their ci item number is different.

So I am assuming the Alias numbers don't work in physical count? I know you can upload the alias item numbers a general aliases with VI

The CI Item table will probably come into your spreadsheet sorted properly, not so sure about the alias table - VLOOKUP() will only work correctly if data is sorted properly.

Thank you for the suggestion. I will see what I can do to build this out for them. They are current on maintenance. They are a large organization and the people that I have their ear are not those that will/can make the decision to upgrade. Believe it or not, I probably only have 2 clients on the product and not on maintenance. I was hoping for a VI job trick to do the translation since I am confident they are not going to be please with their counts. People that choose not to upgrade tend to make other poor choices in their physical count procedures.

Odd, I recently had a conversation regarding those clients that stay on top of their inventory and those that let it slide.... Our conclusion had more to do with whether or not the client had to deal with tight margins...

Not to mention those held accountable...

Always amazing about what drives management decisions. I have one client STILL on v4.3, refuses to discuss upgrading, spends three hours on a process that if Sage was used properly would take five minutes. Claims his people wouldn't ""get"" the new process so he continues to create elaborate processes that ARE difficult to follow. I shake my head and sigh! OBTW, they pay 10K a year to stay current on Sage support. Go figure!

@RandyMarion - I don't think the data has to be sorted for the Vlookups to work properly in a ""modern"" version of Excel. The ""false"" argument in the formula specifies an exact match is required. It will scan the entire lookup table column looking for the exact match. What is important is that the column being searched has to be formatted exactly as the target column in the search table. IOW, if the search column is a text, then the target column has to be a text format also. 123456 will not result in a ""hit"" if one is text and the other is numeric.

Yup. I nested into three if statements. It looked for the item against the item file, then if iserror then looked for the alias, then if error strip leading zeros because some of the items don't match either Alias or item file. They were left with 50 items out of 1700 that could not find a match.

Perhaps this is a good enough reason that they come to you. 3% failure rate isn't toooo bad.

Last year they worked on it for two days before calling. They did all the matching and merging manually.

Two days, numerous people. Sounds like a $750 value added invoice. Perhaps next year they will call you before listening to their old rep.....