Update BOM spreadsheet

[JamesNewton]

https://docs.google.com/spreadsheets/d/1uk89q76vcK4OT9NTM6qxsPpkON_QM3-OrlhfjPigGuE

Bring up todate with kickstarter and HD tabs

[cfry]

I like this list of parts. It helps me get a feel for just how complex a Dexter is.

If the comment: "Bring up todate with kickstarter and HD tabs" is a "do list item" for this spreadsheet, I'd add to that

1. What is this a parts list for? The only title is "NOTE: THIS SHEET NEEDS TO BE UPDATED AND MAY BE INCORRECT"

2. Date.

3. state explicitly that the "sidebar" of "Total Hardware " is redundant with those same parts mentioned elsewhere (if that's the case). I like having the "total hardware" but it doesn't add to the total parts count if I understand it correctly.

4. make a total parts count for every one of the "sub-sub assemblies"

5. Make a total parts count for each subassembly.

6. Make a grand total parts count. this will help us compare the complexity of different Dexter versions. As I understand it, the HD Dexter has many fewer parts so it would be nice to have a comparison of the progress that's been made with the HD design.

On Tue, Aug 21, 2018 at 2:23 PM, JamesNewton notifications@github.com wrote:

https://docs.google.com/spreadsheets/d/1uk89q76vcK4OT9NTM6qxsPpkON_ QM3-OrlhfjPigGuE

Bring up todate with kickstarter and HD tabs

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/HaddingtonDynamics/Dexter/issues/34, or mute the thread https://github.com/notifications/unsubscribe-auth/ABITfaGVOpgawJPogsg6c0VCMglzfA6lks5uTFAZgaJpZM4WGWAH .

[bmv437]

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]

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]

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]

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]

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

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.

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.

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?

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]

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]

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]

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]

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]

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]

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.

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?

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]

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]

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.

[maxsu]

@JamesNewton, @AndrewSmart:

1. What's the status on the PBS-BoM update?
2. What's the status on the Onshape to F360 migration?
3. What's our current best thinking on unobtrusive PBS-style part/assembly numbering in F360?

@AndrewSmart, since F360 is freely available to potential collaborators and extensible /w python, I'd like to recommend we skip Freecad as a project dependency and work in F360. We can find or develop an add-in to update the BOM after substantive changes. Currently looking at your Freecad Macro - at first glance it will not be hard to port over (with the proviso that F360 has a similar assembly hierarchy.)

If we build the add-in from scratch, I'd like to reuse it in other F360-standardized open hardware projects (I'd like to use a similar PBS methodology for StanfordDoggo) - I think the existing macro's philosophy meets that requirement.

@AndrewSmart can you share a quick example of the CSV output from your FCMacro? I'd like to document the existing PBS.csv format.

Tentative next steps here:

1. Find or dev PBS.csv-compatible BoM add-in for F360
2. Locate Dexter F360 model; Add @AndrewSmart's PBS numbering
3. Integrate BoM add-in; draft updated BoM contributor workflow
4. Update and push the new PBS-BoM

[AndrewSmart]

@AndrewSmart can you share a quick example of the CSV output from your FCMacro? I'd like to document the existing PBS.csv format.

@maxsu It's in the last tab of the google sheet I made. I just imported the CSV into that 'ImportCSV' tab, then... I think I manually copied over the columns to the PBS tab, I don't believe that workflow was scripted.

More scripting would need to be done to automate updates, e.g. propagating parts added/removed from CAD model to the PBS document, handling conflicts or whatever (e.g. strikethrough of deprecated parts). Also I don't think grouping on the PBS tab was scripted, I did that manually. I didn't think scripting those features worth the effort if HD wasn't going to use it, but what I did met my needs so I didn't do more. But it does sound like they may use this workflow if it uses F360.

Yes I changed the PBS numbering a bit to handle the hierarchy depth.

EDIT: Also let me know if you want the code as CC BY-SA or whatever instead of GPL2.

[jonfurniss]

@maxsu We are currently on the tail end of finishing up the Fusion 360 port and assembly. To be able to more easily edit parts in the future, we redrew everything from scratch in Fusion. We are internally going through to test everything out and make sure it's correct, so we're not quite ready to share it publicly yet. We started using the PBS numbering system for our part numbers, which we have put as a prefix on each part as we redrew them, which I assume will make the scripting process work easier. We definitely welcome any help for automatically extracting a PBS BOM from Fusion 360 with an add-in or Python script.

[JamesNewton]

Kamino cloned this issue to HaddingtonDynamics/OCADO