3 Replies Latest reply on Mar 10, 2013 11:59 AM by Greg Rupp

    Parent Child in BOM for ERP Input (QAD)

    Jim Hefkey

      When outputing a BOM as a CSV file a heirarcial list is generated giving a decimal deliminated parent child relationship. These can be nested several layers deep - children become parents of other children which in turn can become parents........

      Our ERP system (QAD) requires two columns, one showing the part number of the child and the other showing the part number of its IMMEDIATE parent, no matter how far down or up it is nested. The 'child' column is simply a copy of the 'Part Number' column but the 'parent' column needs generating.  I am struggling to do this in Excel ( I am not very familial with VBA macros).

      Has anyone any experience or solutions?

      Has anyone had to transfer Solidworks EPDM BOM's through Excel into QAD database input?


        • Re: Parent Child in BOM for ERP Input (QAD)
          Corey Hinman



          Is this for a mass upload, like you would typically do upon go live with the ERP system? The reason I ask is that after your up and running, most of your changes will just be to single level BOM's, not a fully indented top level.


          If you're just uploading 1 subassembly and maybe a component has changed, you only would upload that assy part number, not all levels up, or all levels down. That would make populating that column much easier, but still not what you're looking for.

            • Re: Parent Child in BOM for ERP Input (QAD)
              Jim Hefkey


              It is to bring our mass data up to date but also ongoing for each project. We build custom tailored machinery. Each time we build a new machine the BOM is loaded into the ERP system to generate purchase and work orders. A machine may have 200 or 1500 parts, and the number of new parts could be a handful to several hundred. The ERP system finds parts from the BOM that are not in its database and adds them before generating the internal and external paperwork.

              Cheers, Jim

            • Re: Parent Child in BOM for ERP Input (QAD)
              Greg Rupp

              Hi Jim, this should do what you want. Follow these steps.

              1. Export your bom with LEVELS.
              2. Open your export
              3. insert a blank column inbetween columns B and C
              4. in the new blank column "C", put a header in C1. I put mine as "Parent"
              5. in cell C2, put the following code




              =IFERROR(IF(IFERROR(LEFT(A2,FIND("^^",SUBSTITUTE(A2,".","^^",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))-1), "error1") = "error1","Top Level",TRIM(VLOOKUP(LEFT(A2,FIND("^^",SUBSTITUTE(A2,".","^^",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))-1),A:B, 2, FALSE))), TRIM(VLOOKUP(VALUE(LEFT(A2,FIND("^^",SUBSTITUTE(A2,".","^^",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))-1)),A:B, 2, FALSE)))


              Now do a fill down, so the entire column is filled with this code.


              If you get "Top Level", then that item is associated to the top level. It's parent would be the original Part number you generated the BOM from.


              3-10-2013 10-44-04 AM.jpg


              Message was edited by: Greg Rupp