Easiest way to create a BOM for 276,000 items

ERPNext Manufacturing
1

We have 276,481 different types of fixtures we sell at the lighting company I work for. It is IMPOSSIBLE for us to create a BOM for every single variant. What is the easiest way for us to create a BOM for every item?

Can we copy a BOM on item creation and edit what needs to be changed?

2

Do you truely need BOM for all of those items? Asked differently, are these items changing state in some way? Lighting parts → lighting fixtures? Otherwise you would not need a BOM for each item. When you do have a BOM, you need two or more items. If your quarter-million Items includes all the parts that make up your products, that’s a relief. If you’re talking about just the goods-for-sale, you’ve going to have many more items than that.

I would look into Item Variants to cut down on your overall item count, which will also reduce the number of BOMs.

@MichaelPinkowski has some experience in the lighting industry but I know he’s traveling over the next few days.

3

Sorry, we have 276,481 VARIANTS of one type of fixture.

Each variant has at least one part that differs from other BOMs.

4

For something like this, my first instinct would be performing some kind of mass-update through MySQL (MariaDB).

You’d have to have a solid understanding of how ERPNext models parts and BOMs in the database. But it could be done.

From a certain point of view, ERPNext (or any other ERP for that matter) is just a giant user interface to a database, with code that does CRUD on the data. With some SQL wizardry, I don’t see why you couldn’t populate 276,000 rows of data (in many different tables).

Not saying it’s easy. But this is how I’d approach the problem, for these kinds of bulk inserts. Either purely with MySQL, or perhaps with Python.

5

Noo! Don’t do this.

Ideally, if you even want to do this. You want to write a script that uses the Frappe ORM to create the BOMs. Even if that takes longer than running queries, this is necessary so that the system can run all the validations and checks it needs to run for each BOM.

At the end of the day ERPNext runs on Frappe, use the ORM.

4 Likes
6

Hi.

Please do not edit anything. (As in do not mess around with SQL imports).

Easiest way is to use Data import located in Setup >> Data Import and click New to create one.

Then choose DocumentType “BOM”.

Then locate your file. For the template you should see a button “Download Template” next to the help button on the top.

One tip is to create the BOM and a couple of variants and then when downloading the template select “Download with data” and you can see how the data is structured.

Hope it helps.

EDIT:: Clarify no touching the DB!
EDIT: Don’t forget to backup before running mass imports!

7

The guys are right - I have (VERY foolishly) tried to do sql-dabbling for imports. It is a disaster, and WILL damage your system. Use the import system as described above.
Fortunately I had backups to restore to/from.
At best you could programmatically create CSV files to use as your import templates, but the sql-dabbling will probably be horrendous.
I would recommend making a backup after each import run. The max is 5000 items at a time, but I would recommend using 3000-4000 for safety, so you’re in for about 100 runs.

Make sure all the product categories exist before running the imports too, otherwise you have a lot of hassles

8

There is Data Import Facility. I dont know how structured data do you have. But you can first workout with item codes and BOM in Excel and import it with Import Format from Frappe Import Tool.

https://erpnext.org/docs/user/manual/en/setting-up/data/data-import

Seems to be a task. But if you have things already running on it, then it should be possible for you to map it in columns in a spread sheet.

Hope it helps

regards.

Parth

2 Likes
9

The only issue I see is “identifying” the particular “variant” that you might want to build.

When creating BOM, each new version is merely a 3 digit number appended to the BOM name.

For example. If you make a light fixture called “Door Lantern” and it has a Item Code of DL000123, then the very first iteration of the BOM would be named:

BOM-DL000123-001

The second variant would be named:

BOM-DL000123-002

And so on, etc. So the real problem is being able to later identify which variant of the DL000123 you need to create a Production Order for in order to have it made in your manufacturing facility. There isn’t much to work with when it comes to locating a specific BOM version unless you have some other way to associate the variant to the BOM version number.

Not sure how you might get this resolved. Hopefully I laid out the issue well enough here that someone else might be able to take it to the next logical step.

BKM

10

Is there a way to bypass creation of BOM and assign material at the time of manufacturing?
In this way a user can directly assign the required material to the production unitadn not have to generate so many BOMS

1 Like
11

Hi Prithvi,

Well, you don’t need a BOM to make a manufacturing entry. You could consider creating a virtual warehouse on ERPNext, drop all input items into this virtual warehouse and when the item has been manufactured on the shopfloor, you can make a manufacturing entry by consuming the input items and making the transformed item.

It’s fairly simple and easy to do this. The complexity is if you have simultaneous building of such items on the shopfloor. Tracking consumption against each transformed item is the problem and you need to have good physical controls on the shopfloor for that. The ERPNext side is relatively easy if you manage to implement the tracking mechanism,

Hope this helps.

Thanks

Jay

12

Hi JayRam

You mean input the raw materials at the time of work Order generation??
when i tried to do this it forces me to create a BOM again

13

Don’t even do a Work Order. Start Manufacturing based on Sales Order items. The standard manufacturing flow of ERPNext is constraining in certain situations and many of my implementations don’t use the standard flow at all.

Hope this helps.

Thanks

Jay

14

Yes that actually helped will try implementing this in process and see how it works

Thanks

15

Do you just use stock entry type ‘Material Consumption for Manufacture’ to consume the raw materials and use entry type ‘Material Receipt’ to bring in the produced item. If that is the case how do we ensure the cost of produced items equal the cost of raw materials. Since the cost of the raw materials and the cost of finished good will go to ‘stock adjustment account’. If the two are not same the profit and lost will give fictitious loss or profit from the production process.I desire insight to how this is being overcome. Thanks

16

Could you try Stock entry Type “Repack”?

Though we eventually just created a simple doctype for this purpose

image
image1002×835 25.3 KB

Basically for simple busineses who do not want to go the BOM route.

I belive the image is clear enough??
You input the raw materials in the Consumed by section and the manufactured products in the Produced from Manufacturing section.

You can produce multiple items as well as apporton the input costs across the outputs.

A bit simplistic but works for smaller manufacturing companies

2 Likes
17

I am interested in your simplified solution, I got a couple of questions

  1. the Apportion is always manually input by user? even if output only one Finish goods?
  2. when this simplified work order submitted, the stock ledger and general ledger entry will be posted also?
  3. is possible to share the doctype definition(json), Js and py code? maybe after the code shared all the above questions are answered.

thanks.

18

Good.Is it possible to make the apportionment of the total materials cost as weighted average of the individual item produced cost for the final absorbed cost automatically ? If this is done,this simplified solution becomes mighty.

19
  1. By default it apportions the input value equally amongst the items produced, if you do not want equal apportion then you can input the aportion % for each item in the Apportion field.
  2. Yes, a stock entry is created that consumes the input items and adds to the manufactured items.
  3. Its an app, will share the github page when I get to the office
20

I do not understand this request. Could you break it down with examples ?