How can I merge multiple inventory items into one Master item while not losing purchase, vendor and average pricing

I have just joined a new company and we are using ESC software for field service teams, dispatch, inventory management and other features. I am a newbie to this software, but have used Netsuite and Salesforce extensively, including setup, customization, launch and support, so am knowledgeable of  relevant workflows and functionality processes. However, not so familiar with dESCO.

I am attempting to assist organizing inventory items in both the warehouse and the software. I have noticed various items have been purchased under various part numbers. However, the items are the same product, just purchased from various vendors under different part numbers, and some of these various items have different  quantities spread across them is ESC totaling to the total physical inventory count  of the one actual product found in the warehouse. However, researching this and assuring the various part numbers in ESC are equivalents  to the exact same part is an arduous task.

I would like to create our very own master part number and move each of these various part numbers, which equal to the same physical product,  under a master part number and continue being able to procure from the individual different part numbers while each of the purchased quantities and costs are always rolling up into the master part number.

Example:

2 " Nails:

My internal new master inventory item part # will be: CM-2 INCH NAILS

Our vendors part numbers:

  • Vendor A: NAILS-2 INCH-STEEL
  • Vendor B: 2 INCH STEEL NAILS MODEL  2A
  • Vendor C: NAILS-2-STRAIGHT

We would like to be able to directly link / merge / move each of the current existing vendor items found in ESC to the one master item and all of their quantities to roll up into the master items quantity.

In the case of the example above, if I have 10 cases from vendor A, 20 from vendor B, and 20 form vendor C, we simply would like to move the items in dESCO / ESC to a master part number and then to see a total of 50 cases under the master part number.

We would like to be able to do all of our inventory management from the Master Part number where issuing and returning items from staff and cycle counting are concerned. We would prefer no one in our organization need to worry about any other number, except the master part number. Purchasing would be the only department remaining aware of the various vendor items. Upon merging these item numbers into one master part number, we would like the average cost to be based off the average cost of all vendor purchases between all of the vendors and their individual items numbers as history purchased line items against the master item. We will be applying our Master Part Number bar codes to all physically related items. 

I feel confident I have over explained myself. ;-)    But, my hope is too much is better than too little. 

I am told this can be done, however current staffing are not completely certain of the proper process to make this happen. 

How can I make this happen, please? Any support would be greatly appreaciated. 

0

Comments

4 comments
  • Gary,

    The system does what you need it to do, however, getting from where you are, to where you want to be is probably a manual process.

    In ESC, every part is what you are referring to as a master part number.  If you look at the Vendor tab in the Inventory Entry dialog box you will see lines for different vendors, allowing you to set up the different vendors, their specific part number, and last price.

    If you have 3 different part numbers, you will have to set up the vendors on one of them, then adjust the quantities on the other p/n(s) from that number to the one you are going to keep.  Once that is done, make the excess part numbers inactive.  As far as count go, the system won't care who the vendor was, but it will keep track of who and when you ordered from through the POs.

    Also, when you order parts you can click on the compare button(s) and see who is giving you the best prices.

    Hope this helps.  And do speak to the tech support people, they are often able to help you massage the database and automate the process.

    Hope your New Year is off to a good start!

    Rick

     

    0
    Comment actions Permalink
  • Welcome to ESC, Gary!

    It sounds exactly like what I found when I started at my current company about a year and a half ago, the staff in charge of purchasing supplies had been creating new part numbers in ESC for each vendor, using the vendor's actual part number.

    I don't think ESC is designed to manage Master Part numbers that automatically roll up inventory levels of other part numbers. What we did was create one part number per unique item and assigned all of the inventory quantities & vendor part numbers to that part number since they were virtually identical. Our list of part numbers shrank significantly, which helped reduce confusion in the office (we added a 3 letter prefix to group similar items: BLT- for belt, FLR- for filter, etc.). Unfortunately this method won't give you the average cost by vendor, only average cost of all vendors combined as it's only one inventory part number in ESC.

    If this won't work, another option could be to use a 2-3 character suffix with your internal part number to denote the vendor, so a part that has 3 vendors would have 3 identical part numbers with the exception of the suffix at the end so they're always next to each other. That way you could determine what quantity was from each vendor and each vendor's average cost. The total quantity would have to be calculated manually, but since they're listed next to each other in the Item List it wouldn't be very difficult. Not ideal, but it would keep them separated.

    If you want to merge the part numbers together, first back up your existing ESC database, then export the entire inventory list. To do this, create a custom Item List view that has all of the columns you need, then click the "Export data to Excel" button to the right of the Item List search field. We made the adjustments to the CSV file - part numbering, combining quantities, marking old part numbers as inactive in case they were used in Service Agreements, etc. and split the sheet, as ESC uses the Inven and VenParts tables to manage inventory. The VenParts table is where ESC links the internal part number to each vendor's part number & price.

    Make sure you have included all of the required columns, there's an "ESC Data Conversion Bible" that I found online that helped me a lot. They also have the online chat, which is always quick to get an answer. When you're finished editing the items, save each sheet of the original CSV file as a Text (tab delimited) file. Open the text file to make sure it saved correctly. I found that Excel sometimes added a quote or apostrophe mark to the beginning/end/both of the item description field, so I added "zz" to the start and end of the text description field using Excel's concatenate formula. Opening the text file in WordPad, you can run a batch replace on all zz' or zz" so it only takes about a minute to make sure all of the items will import cleanly. Without adding the letters, I found that it removed any quote/apostrophe marks from part description sizes (4", 10', etc.).

    You'll then be ready to import & overwrite the old database tables (File > Import/Export > ASCII Import Utility - configure settings as needed). Since the old items are marked as inactive, they will still show in the default Item List view. We created a custom view that filtered out the inactive part numbers and set that as the default view, so everyone sees this custom view when opening the Item List.

    I know this probably isn't what you want to hear... this is a lot of work, but it has made everyone's lives in our office SO much easier when there's only one part number for each unique item - when you create a PO with the ESC part number, it prints with the corresponding vendor's part number (we edited the PO form to hide the ESC part number column - the vendor's part number is added to the beginning of the description field when printing).

    Sorry for such a long reply, I'm with Holts Mechanical in San Antonio TX if you need help with this process.

    How have others done this? I'd be interested to know if there's an easier way to fix inventory part number issues as well.

    0
    Comment actions Permalink
  • Hi Lance and Rick:

     

    I appreciate your extensive answers. No answer is too long where the desire to learn and understand is involved. I will combine this with other info and determine the most operationally logical way to proceed. I will provide feedback once a determination has been made and share results. 

    0
    Comment actions Permalink
  • First it would be really handy if this software sent notifications when people are writing in so we could all be made aware and possibly join in.  If there is such a function, how do I do it.

    ESC did write in an inventory merge function for me a couple of years ago, it will help you when a part number is changing (by a vendor, mfr, etc.) but that's where it stops.  So if you have filters under XYZ and the part number is changing to ABC (and ABC is not already in your database), simply open the XYZ part, type over the part number field ABC and hit Save.  Now that part is stored under the new number.

    Unfortunately, they did not allow a merging of multiple part numbers into one, which would be really beneficial and is exactly what you were asking for at the beginning of this thread!!

    0
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post