Automated exporting bills of materials for manufacturing

[martukas]

What has to be done We need a tool and documented process for exporting BOM (Bill of Materials) lists from our CAD software - Autodesk Inventor - and generating Markdown pages. We probably need 2 things out of this:

• sub-assembly BOMs, such as the ones already on various pages in the repo
• a total BOM for a whole ventilator assembly

How do you know it has to be done We are an open source project and we publish our design on github as the single source of truth. And excel spreadsheets are so yesterday, and fragile, and google drive links look ugly. On the other hand, doing these BOMs manually has been tedious, time consuming, prone to human error. We need a more sustainable way going forward.

Code pointers

• See existing BOMs on various pages under manufacturing and note what fields and variations might be necessary
• liaise with someone from the mechanical team - @KishanKoradiya @tijanim @afarrington17 or @martukas, and figure out how flexible Inventor is at exporting things, and what format that will take. Also make sure the proper procedure for doing this is documented.
• Things to watch out for: alternate items, alternate purchasing sources, pricing of cosnumable/uncountable materials, pricing at bulk quantities, total price estimates, links to custom part documentation vs. supplier,

Don't forget

• [x] Put the issue in a milestone
• [x] Assign it to one of the projects
• [x] Add appropriate labels
• [ ] Mention dependent/blocked issues if any

[BrennanKolotinsky]

I created a tool to do this! It is quite simple. You can enter any amount of columns (if you need to add more for ex. alternate purchasing source). Not exactly sure how you want it added into the codebase (maybe in a separate repo?). You can see the code here: https://github.com/BrennanKolotinsky/markdownTableAutomator. I wrote a lot of simple instructions to follow there!

At the moment it only creates some simple markdown but should save a lot of time. It works with CSVs but I assume it should work with other file types you might try. One feature that I haven't added is automating creating the links (will have to think about how to do this more).

If you have additional requirements, let me know exactly what they are and I will try to add the functionality to meet them! :)

[martukas]

How about the links to purchasing sources?

[martukas]

I think we would want it in our own repo, perhaps under software/utils

[BrennanKolotinsky]

Looked into links a bit more. It appears CSV don't support links. If the BOM was another file type it would be possible (.xlsx doesn't seem to be a good pick, just to note!).

The format for a link in markdown is fairly simple: [I'm an inline-style link](https://www.google.com). So I would need to use a regex to spot http, and then add the brackets/parentheses if detected.

[KishanKoradiya]

@martukas Please go through this and let me know your thoughts.

A solution has been implemented to this effect in branch corresponding to this issue. A rough procedure is given below. If solution is found appropriate, a formal procedure can be detailed in the Wiki

The great thing about this method is that it needs to be done once for the parts and then it propagates though all instances of the part use in Inventor. Subsequent changes would just need to be edits.

(NOTE: Exercise caution when working with the bom in inventor. THERE IS NO UNDO. Heed now or learn it the hard way.)

The procedure:

1. Open an assembly and edit the 'Bill of Materials' under the 'Assemble' tab in the 'Manage' pane.
2. Go to 'Parts only', right click, and 'Enable BOM view'.
3. On the bottom left hand side, click import and browse to the 'bom_structure.xml' file in the internals/display_assembly directory.
4. You will still need to add a web links column because only the final version was exported. More on this explained below
5. Fill in all the cells with correct values. Some details on how to fill are given below.
6. After filling all cells, select 'Export Bill of Materials', make sure it is 'Parts Only'.
7. Export it to a .csv file.
8. Open the .csv file in Notepad, copy all its content, convert into markdown using this online converter.
9. Copy the markdown code and insert into your readme file. And you're done!

How to fill the table, only special considerations mentioned:

• Regarding the issue of multiple vendors:

• Insert multiple web links to vendors in the web link column by putting two spaces between the links.
• Copy those links and insert them into the vendor column like this

[vendor1](weblink1)  [vendor2](weblink2)

• To make alternative parts, use the create virtual component feature. All alternates should be marked as optional.
• Part number is our RespiraWorks part number unique to each item used by us.
• Vendor part number is the part number from whom we are purchasing the item if sourced externally.
• Include the discipline (electrical/mechanical/pneumatic), origin(sourced/custom), and assemblies/subassemblies where this part can be found, and the word 'alternate' if it an alternate part in the keywords.

Part number code explanation: All parts have a unique 12 digit code that can provide useful information at a glance. Here is a sample part number designation code:

ELEV03EA0001

Here's how to understand this:

• First three letters give the discipline/field/area: ELE – Electrical MEC – Mechanical PNE – Pneumatic
• Next three letters give the revision number which is the version that the team is currently working on. This will be useful for time based evolution, to see features that were added/removed from a part: V03 – corresponds to v0.3 build V14 – corresponds to v1.4 build
• Next two letters give the origin - only the primary source if it is externally sourced or specify RespiraWorks if it is a custom part: RW – Stands for custom Respiraworks Part E – Denotes from external supplier/vendor. Not made by RespiraWorks A – Amazon (the alphabetic code from the standard sources guide A part may have several vendors, but only the alphabet of the primary source is given in this two digit part of the code.
• Next four numbers: 0001: A unique serial number
  • Rules for part numbers
    • Copies do not get part numbers
    • There is no order yet (no need for one)
    • If it is a series 9xxx part number then it indicates an alternative part.

Points to note/known issues:

• There are so few optional parts. It can be decided to not make separate column (Optional: Yes/No) for that. Hvae left it in for now; can be removed if desired.
• Cost decimal places still an issue. Still looking for a solution. A potential solution would be to create a macro using VBA or iLogic? Don’t know. Or a workaround – include price for all the total quantities not per unit cost. How should we include cost consistently? Based on how many we order, or price per unit and then multiply by quantity? Or if the vendor is selling by the bunch (e.g. min. qty. is 75 units). We should decide this first then decide how to implement the cost. I reckon price per unit only. Scrap the x.xx/100. That is not how people might buy. If it does become cheaper when bought in bulk, then all the better.
• Right now our assemblies aren’t structured to include other assemblies. If we insert subassemblies into assemblies, then we could potentially just link the electrical stack in a assembly of the display_panel. And select the option when exporting about the first level children only/all levels. This is how we can handle multilevel BoMs
• Keywords can be made more sophisticated
• Total price estimates can be made using a simple iLogic add

Addressed issues: alternate items, alternate purchasing sources, total price estimates, links to custom part documentation vs. supplier Still to be discussed: pricing of consumable/uncountable materials, pricing at bulk quantities

[martukas]

Might want to also consider #1074 and make sure that whatever system we adopt helps manage this question as well.

[dheater]

Can someone link an example BOM .csv export file?

[KishanKoradiya]

Can someone link an example BOM .csv export file?

https://github.com/RespiraWorks/Ventilator/blob/issue_1011_automated_bom/manufacturing/internals/display_panel/display_and_PCB_stack_assembly_bom.csv

This is a sample csv. Not likely to be final product

[martukas]

This may be useful: https://ketiv.com/blog/intro-to-inventor-bill-of-materials/