ERPNext Foundation ERPNext Cloud Chat Blog Discuss Frappé* Donate

Easiest way to create a BOM for 276,000 items

bom

#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.


#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


#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