New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Update BOM spreadsheet #34

Open
JamesNewton opened this Issue Aug 21, 2018 · 14 comments

Comments

Projects
None yet
4 participants
@JamesNewton
Collaborator

JamesNewton commented Aug 21, 2018

@JamesNewton JamesNewton self-assigned this Aug 21, 2018

@cfry

This comment has been minimized.

Collaborator

cfry commented Aug 22, 2018

@bmv437

This comment has been minimized.

bmv437 commented Nov 4, 2018

Have you considered using a PBS (Product Breakdown Structure) for the parts and assemblies?
Another very complex project I follow just posted a video about how a PBS changed the way they tackle the project:

https://www.youtube.com/watch?v=zVyEsMiwvVc

Here's their Blank PBS Template with analytics:
https://docs.google.com/spreadsheets/d/1eKW_-ygHTu2z4inSSGPFnjAoIolORW19d-Xu-uhDw9E/edit

@JamesNewton

This comment has been minimized.

Collaborator

JamesNewton commented Nov 4, 2018

I certainly have! If I could find the time I would do it. If you have time, please help us? P.S. I LOVE marble machine x!

@AndrewSmart

This comment has been minimized.

AndrewSmart commented Nov 21, 2018

PBS looks useful. I made an attempt.

By macro (javascript) it makes a list of 3D parts, hardware, and their quantity from the main tab. Sorted alphabetically. Run the populate_tabs macro to update the tabs. Another macro is in there to autogenerate the PBS #s.

The assemblies/parts match OnShape's instances list. Row groups, dashes, and spaces in the names help show hierarchy.

EDIT: OnShape has no free API to pull the hierarchy from AFAIK. Can automate this document creation/update by exporting OnShape into some other CAD suite, then translating that into the spreadsheet.

@JamesNewton

This comment has been minimized.

Collaborator

JamesNewton commented Nov 21, 2018

Wow @AndrewSmart that rocks! Thank you!

I hope you can help me understand it better?

  1. it looks like the harmonic drives aren't included in the hardware tab. Apparently, that's because they aren't assigned a type. Looking at line 110 / 110 in the list shows the type set to nothing. Is correcting that just as easy as changing the type to H for hardware?

  2. The "bottom section" and "second section" don't appear to have any detail in them. But those sections do have detail in them in OnShape. Was that part of the setup just not finished? I'd like to learn how to finish it if you have time for a call or other means of educating me.

  3. Is there a way of getting the default quantity to be 1 or some other way to total the e.g. number of ball bearings one needs? Looking at the Hardware tab there are a number of KP0056-01_SB6703ZZ_2_03 ball bearings, but no idea how many total you need to order. Is there a way of getting that information? I thought the Wintergatan PBS did that?

Again, thank you for this amazing effort, and I really hope we can work together to bring it to full use.

@AndrewSmart

This comment has been minimized.

AndrewSmart commented Nov 21, 2018

  1. it looks like the harmonic drives aren't included in the hardware tab. Apparently, that's because they aren't assigned a type. Looking at line 110 / 110 in the list shows the type set to nothing. Is correcting that just as easy as changing the type to H for hardware?

Yes H for hardware, then re-run the populate_tabs macro to update.
It seemed like the harmonic drives had multiple sub-parts, in this model are they the cycloidal drives? I couldn't tell at a glance. Multiple things in there I probably classified wrong (like the shafts, Idk which are steel, CF, or printed without searching the webinars for each one).

  1. The "bottom section" and "second section" don't appear to have any detail in them. But those sections do have detail in them in OnShape. Was that part of the setup just not finished? I'd like to learn how to finish it if you have time for a call or other means of educating me.

Correct I hadn't finished it by hand. Just showing what I had as a demonstration. I'd like to look into a more automatic way of making it.

  1. Is there a way of getting the default quantity to be 1 or some other way to total the e.g. number of ball bearings one needs? Looking at the Hardware tab there are a number of KP0056-01_SB6703ZZ_2_03 ball bearings, but no idea how many total you need to order. Is there a way of getting that information?

Via macro a sum of multiple instances could be tallied. A grouping structure on that page seems most appropriate to see where the instances are. I hadn't written that yet. e.g.:

   PBS #             NAME                 QUANTITY
+--total----  KP0056-01_SB6703ZZ_2_03     8
|--1000-01    KP0056-01_SB6703ZZ_2_03     2
|--2530-12    KP0056-01_SB6703ZZ_2_03     4
|--3120-03    KP0056-01_SB6703ZZ_2_03     2
....

I thought the Wintergatan PBS did that?

Not the publicly released document linked above by bmv437. It didn't have the analytics mentioned in the video; I'm guessing they kept their analytics private (EDIT: link to analytics in youtube video). The additional tabs/macros were made by me. The macros are written in javascript [1][2], easy stuff.

@JamesNewton

This comment has been minimized.

Collaborator

JamesNewton commented Nov 21, 2018

In the OnShape version (which was exported from some other CAD package... SolidWorks? I'm not the mechanical engineer) the Dexter is a version 1 (aka "Kickstarter" version) which certainly uses harmonic drives, not cycloidal. The new Dexter HD /may/ use cycloidal, but they are still in development.

@JamesWigglesworth probably knows a lot more about what CAD is going to be used going forward. James, we should ask Avery / Noah what features the PBS could have that would most help him with assembly / parts ordering. With that info, we can better design a macro to populate the tabs. And if we use it, then it will be kept up to date for others.

@AndrewSmart if we know the CAD system, then can you help us export the data in a way that can be imported into the PBS?

@AndrewSmart

This comment has been minimized.

AndrewSmart commented Nov 22, 2018

  1. Is there a way of getting the default quantity to be 1 or some other way to total the e.g. number of ball bearings one needs? Looking at the Hardware tab there are a number of KP0056-01_SB6703ZZ_2_03 ball bearings, but no idea how many total you need to order. Is there a way of getting that information?

Via macro a sum of multiple instances could be tallied. A grouping structure on that page seems most appropriate to see where the instances are. I hadn't written that yet. e.g.:

   PBS #             NAME                 QUANTITY
+--total----  KP0056-01_SB6703ZZ_2_03     8
|--1000-01    KP0056-01_SB6703ZZ_2_03     2
|--2530-12    KP0056-01_SB6703ZZ_2_03     4
|--3120-03    KP0056-01_SB6703ZZ_2_03     2
....

Ok, I implemented this grouping. You can easily see total hardware/print counts now.

I also fixed a bug with quantities not being calculated correctly when an assembly had multiple instances. Also PBS #'s now link to the PBS tab. I left a blank quantity to mean "1", I felt having 1's everywhere cluttered things up on the PBS tab, but I suppose they're fine on the other tabs.

if we know the CAD system, then can you help us export the data in a way that can be imported into the PBS?

If it's SolidWorks or some other proprietary system I probably can't, I don't have a copy. In the past I found a DLL in a free SolidEdge viewer, I used that to parse the SolidEdge files and display the geometry, but that was tricky and I don't have that code anymore. I don't want to deal with sparsely documented DLLs again.

If it's FreeCAD I could do it. I was just going to import the OnShape model into FreeCAD, then use Python in FreeCAD to traverse/output the hierarchy to a csv... import that into a new spreadsheet tab, run macro on it to update the spreadsheet. Probably the simplest approach.

This spreadsheet so far was just low hanging fruit for me! Hope it helps.

@JamesNewton

This comment has been minimized.

Collaborator

JamesNewton commented Nov 22, 2018

Thanks Andrew! I'm understanding that we used Inventor initially, and are now switching to Fusion 360. More when @JamesWigglesworth is available, probably next week.

@AndrewSmart

This comment has been minimized.

AndrewSmart commented Nov 30, 2018

I thought the Wintergatan PBS did that?

Not the publicly released document linked above by bmv437. It didn't have the analytics mentioned in the video; I'm guessing they kept their analytics private.

Nevermind, looks like their analytics document is separately linked to under their youtube video.

if we know the CAD system, then can you help us export the data in a way that can be imported into the PBS?

I was just going to import the OnShape model into FreeCAD, then use Python in FreeCAD to traverse/output the hierarchy to a csv... import that into a new spreadsheet tab, run macro on it to update the spreadsheet. Probably the simplest approach.

Ok, I've:

  1. Exported the STEP file from OnShape (or from any CAD Suite the model may be in). Caution: hidden parts were not exported, make sure to unhide all prior to export.
  2. Imported the STEP file into FreeCAD
  3. Ran a FreeCAD python macro I wrote to make the csv, and
  4. Imported that csv into a new tab in the PBS spreadsheet.
  5. Merged that into the PBS tab by hand (made a new column and compared hierarchy by eye).

I've not written the macro to update the PBS tab using said CSV tab, I thought (5.) in the procedure above is good enough. Code is all here.

As a suggestion so that parts can be classified automatically is to assign each type (hardware/carbon/plastic) a color in the CAD model (even a RGB channel off-by-1 is fine). That color is exported into the STEP file, and can then be utilized to classify the part via script. Everything could instead be classified by hand in the PBS tab (as it is now) so no big deal either way.

This BOM doesn't include stuff like the finishing nails as they are not in the CAD model, I expect you could add parts like that to the PBS tab by hand if you want to go that route, or add them to the CAD model.

@JamesNewton

This comment has been minimized.

Collaborator

JamesNewton commented Nov 30, 2018

Thanks Andrew! Couple of questions:

  1. I can't expand the collapsed sections on the PBS tab. It says I'm trying to edit a protected cell or object. But I can change the status and other columns...
  2. On the hardware tab, some items are totaled and others are not, I'm trying to understand what triggers that or misses that. e.g. lines 25-27 should probably be totaled. I'm looking for the code that makes that choice but I don't see it. Did you just do that manually?
@AndrewSmart

This comment has been minimized.

AndrewSmart commented Nov 30, 2018

  1. I can't expand the collapsed sections on the PBS tab. It says I'm trying to edit a protected cell or object. But I can change the status and other columns...

Ok strange, everyone with the link can edit the entire document. I did protect column A from edits as that is autogenerated. I guess that prevented others from expanding the row groups, so I've removed that protection, must be a bug with Google Sheets.
Should work now.

  1. On the hardware tab, some items are totaled and others are not, I'm trying to understand what triggers that or misses that. e.g. lines 25-27 should probably be totaled. I'm looking for the code that makes that choice but I don't see it. Did you just do that manually?

Nope did not do it manually, grouping is done automatically on the BOM tabs. The code that does it is function group_sheet.

I'll try to fix that real quick. EDIT: Ok works now.

@JamesNewton

This comment has been minimized.

Collaborator

JamesNewton commented Dec 2, 2018

Andrew, that's excellent! It really looks quite nice now.

Our lead mechanical engineer, @JamesWigglesworth, tells me that we are standardizing on Fusion 360 for new designs. Have you worked with that before? There are free (as in free to use, not open source) licenses for it that individuals can get for non-commercial work if you would like to try it. I assume the current process would be the same? Export the STEP files from Fusion, import into FreeCAD, run the macro, move the data into the sheet?

@AndrewSmart

This comment has been minimized.

AndrewSmart commented Dec 2, 2018

Thank you. Nope, I have not worked with Fusion 360.

I assume the current process would be the same? Export the STEP files from Fusion, import into FreeCAD, run the macro, move the data into the sheet?

Correct. Reminds me, I ought to share this document/workflow with the FreeCAD community.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment