We are buying, selling and using (for projects) the following generic “raw” materials.
Plywood 6mm
Plywood 9mm
Plywood 12mm
Plywood 19mm
Plywood 25mm
Plywood 6mm - Moisture resistant
Plywood 9mm - Moisture resistant
Plywood 12mm - Moisture resistant
Plywood 19mm - Moisture resistant
Plywood 25mm - Moisture resistant
Plywood 6mm - Fire resistant
Plywood 9mm - Fire resistant
Plywood 12mm - Fire resistant
Plywood 19mm - Fire resistant
Plywood 25mm - Fire resistant
Plywood 6mm - Fire resistant - Single sided primer
Plywood 9mm - Fire resistant - Single sided primer
Plywood 12mm - Fire resistant - Single sided primer
Plywood 6mm - Fire resistant - Double sided primer
Plywood 9mm - Fire resistant - Double sided primer
Plywood 12mm - Fire resistant - Double sided primer
Plywood 6mm - Perforated
Plywood 9mm - Perforated
Plywood 12mm - Perforated
Plywood 19mm - Perforated
Plywood 25mm - Perforated
OSB [sort of the same layout as plywood]
Etc… 750 main and sub-types more.
All of them are coming form 3 main resellers offering multiple brands and types.
We just keep small stock for a couple of them. Let’s say 75 types. The rest we are buying for a specific project.
I’m getting clean CSV/EXEL sheets with price updates of every Item from the 3 suppliers. It’s a list of 5000 records containing: Reseller, MPN (or other unique reseller nummer), the price per square meter and the price per full sheet.
We only buy full sheets and sheet size for every brand and type is different. Let’s say there are 100 different sizes of sheets.
General profit: 150% for full sheets. 200% for cut sheets.
Selling price is basically the above calculation plus our own input per item: we don’t want it to drop to much
Questions / goals:
How do we get the info into ERPNext consedering:
keeping track of suppliers price history. And company price history.
quickly finding (filtering) raw material prices from all manufacturers based on multiple relevant propperties (stated above like thickness, fire resistance etc). This would be used for quick calculations for customer re quests.
Having the same pricing info at hand for the items we sell. Allso these same propperties should the the filters to get to the right item (price) fast. And add it to a quote or manufacturing order.
Having our “items for sale” (at least the ones we are keeping on stock) linked to the right Supplier, MPN and latest price.
We’re OK with using the import data feature to mass-update the prices every few months.
How should we fit these items, prices and links into ERPNext? Your insights are verrywell appreciated.
Item price has a valid_from and valid_to date to keep track of historical pricing (for any price list).
The import tool is useful here but it would be a good idea to use several smaller imports vs 5,000 rows in a single file.
If prices change often you might consider “moving” no longer valid prices to some other price list like hist_selling or archived_buying, to keep pricing queries performing optimally.
We have “bought” ourselves a year to find, tryout and implement a shadow ERP system before we go live in januari 2026. We have been collecting price lists in Google sheets for de last years. Unfortunately I can’t share them with you. It’s basically containing all generic information about 5000 “items”. We use the sheet to quickly finding the best prices for project quotes. To get down to the right material we simply use the column filters. Like… material, thickness, type, surface finish, certificates, sheet size and then there are a few options left from 3 suppliers. We then can make a quick decision where to buy the sheet based on price brand, quality, etc.
One “item” in ERPNext should be “one specific type of sheet” we buy from one manufacturer? This might imply that if we sell this sheet or a cut sheet under our own name as retailer of raw materials. I need to create a new item, with a new bom, that contains one item. “the item we buy from the manufacturer”. Richt? Seems like quite some bookkeeping to sell a sheet.
Or we have both branded “items” we buy and abstract “items” we sell listed. I can imagine just keeping the 100 types we frequently sell up to date. But how do I simply link these “abstract” items to their (multiple) Manufacturers, MPNs and its latest/current Prices?
Thx for the advice of having no more than 1000 rows imported at ones.
The system is flexible and robust. Without knowing your inventory, process or desires, it’s a considerable amount of work to list all the possibilities.
Start with a small scale of examples, ask specific questions and provide detailed desires.
A single Item can have multiple associated suppliers part numbers.
A single Item can have multiple prices (Standard Selling, Standard Buying, Custom List A, Custom List 2, etc). If you can simply use the two defaults, you won’t have
to alter purchasing and selling doctypes.
If Each manufacturer has unique pricing and you want to track costs and selling prices, you’ll need items and prices for how finely you’d like to track or assign prices.
If you purchase or sell (3) items from supplier A, (4) items from supplier B, and 2 Items from supplier c, do you want your inventory to reflect the quantity from each supplier? Perhaps Batch numbers may help if you don’t want to list unique items when the only difference is the supplier, brand, or price???
When you have three brands to choose from, how do you decide which you’ll sell to your customer? Do you provide the brand name, or do various brands also
relate to unique quality or features?
How are you handling these items before ERPNext was considered?
The company is using printed out (yes paper) 3 supplier price lists. A worked has a list of stock items and manually keeps an EXEL sheet of “current” prices every few months. Our ERP system just handles orders with abstract descriptions of materials used. We don’t keep track of stock, only counting ones a year. We roughly “know” what’s in the warehouse and mainly buy from supplier on project base or raw materials on specific customer requests.
So yea… current status is not realy reflecting what we’re aiming for. We’re looking for an easy to maintain system to find and keep all supplier prices, to quickly add them to a quote of BOM. Or have prices live at hand when a customer “asks” for it behind the countertop or on the phone.
It seems to me that “at least” I need to find a way to get the 5000 “Items” from 3 different suppliers into ERPNext as unique Items. I’ll have the properties (attributes) manually embedded/code in the ID and/or Item name. And I have to find a way to update these massive list of items every few months with the Data import function from the “Items” interface and the “Price list” interface. Then I have to do the “manual work” in excel of Google sheets.
Would this be a straigtforward and logical first step? I guess from here we can at least start creating BOMs, POs.
Second step would be to add our own company branded Items with are actually rebranded cut pieces of material from several suppliers. I can list those abstract items on a website, or name them in a quote without exposing the name of my manufacturers. In listing them in a quote or website I DO need the items to have full description of properties (item attributes?!?) listed. So, it should be better create those items form item templates with the right set of attributes?
Perhaps consider Buying and Selling separately to determine your best path.
You can assign a default price list to a supplier. When you purchase “sheet-a” from Supplier-X, you’ll use the price-list “supplier-x”. For purchasing purposes, you only
need one item (which will be associated with up to three supplier specific price lists).
If you sell or use “sheet-a” in a BOM, you’ll know the price based on inventory on hand or method (LIFO, FIFO, etc). If you’re comfortable with a single sell price no matter who you purchased from (always base pricing on the highest of the three) then you can likely use a single item for all three suppliers.
The Item ID (aka name) will be your unique identifier for item and item_price.
The link between Item-[sheet-a] and “a specific line” in price-list “supplier-x” is
the item ID. Please realize price-list “supplier-x” is a Price List named “supplier-x”.
If your suppplier-x supplies you with 10 unique products, you’ll have a purchase price
list name “purch_Supplier-x” and a “sell_Supplier-x”. These two price lists are where you’ll
maintain pricing for the ten items you source from supplier-x. You can use the valid dates to
track pricing over time. You can then create an archive price list for both buy and sell prices.
When the regular lists have “too many” out-of-date prices, you can perform a bulk edit on those
item prices and move them to an archive price list.
The above assumes you’ll create a single item and multiple price lists (two for each supplier, one for sell, and one for buy). Alternatively, you can create unique item IDs by supplier and maintain only one sell price list and one buy price list.
Ok, looking at our stock (in reality) we have (all together) 1pc of “sheet-a” and all together 4m2 of lose phases of “sheet-a”. We sell it as a generic PLYWOOD-12MM-FIRERET-WHITE.
In case stock runs low, or is not sufficient for a new project. We want to search for this specific type of plywood, find the items from the different suppliers that offer this type of material. Discover the best price for the project and use this info for quote/calculation and perhaps a PO.
Reflecting on your last comment. Wouldn’t this imply that the Item ID/Name above is stated multiple times in the pricelists of several suppliers that deliver material that match these properties? So wouldn’t I then have just one price list per supplier? The only “set” value of the raw price lists I get from my supplier contain a MPN of similar UID. Ofcourse not our “Item ID/Name”. Do I need to match this in excel before importing?
First you need to decide if you are going to create three entries in the Item table for item “sheet-a”. If you create three items (one for each supplier) then yes, you’ll have
one price list because you have unique item IDs.
If you don’t want to create three Items, then you’ll create additional price lists (one for each supplier).
I don’t want to lead your direction, so It’s difficult to be impartial. There are many ways to accomplish the same goal. I’m still not sure what your goal is.
For example, if you look at the Item Price docType, there are several fields like ‘Note’ or ‘Reference’. You can use these fields to indicate your supplier, but this
will be subject to user error. Alternatively you can customize the Item Price docType and add a link field to Supplier. You’ll need to name it something other than “Supplier” because there already is a field from supplier but that comes from
the price list link.
While investigating the pros and cons of both option… We’d lose the “sheet size” propperty/info if we would go for “generic items + three price lists” route. This propperty belongs to the “item” not the “price list”. If we need to accuire material for a specific project. Cut pieces need to come out of a full sheet as economic as possible. This also desides from which supplier we are sourcing the sheets. Sheet size also determins internal handling/operation routines.
So that would be one price list for all items and one Item for each type of full sheet the suppiers offer under their own UID.
For example I’ll have an Item: PLYWOOD-19-1220-2440-FIRERET-BLACK-SX(Supplier X) I don’t want the MPN/UID in my Item ID/Name to prevent tracability in case we put this item directly on a quote.
So woudn’t it be a great idea to also have a postfix for ourself as a supplier? So we’d get -SX -SY -SZ and -S1 (our company). We’d use this item to maintain stock of cut pieces. So we’d get: PLYWOOD-19-FIRERET-BLACK-S1. (Where sheet size and supplier is not relevant) We later can figure out how to quantify stock: total square meter or perhaps even sizes of each cut piece in stock.
Experiment with your ID naming scheme. Consider autocomplete while the user types the ID.
What’s the most important characteristic “plywood”, “thickness”, or overall sheet dimension?
Consider abbreviations as well (“PW” or “PLY” for plywood). If the user can type 4-8 characters
and get a short list of choices, that would make them happy
