15 Replies Latest reply on Mar 25, 2014 11:35 AM by Steven Dod

    Physical Inventory Management?

    Daen Hendrickson

      This is not an inquiry about SW or EPDM specifically. Rather I am interested in the software tools and processes people use to manage the physical inventory in their companies. Any good books on methods for managing this part of business? And also the general processes followed from the initiation of design to delivering to the customer.


      This post is LONG, so best to print it out and take it with you on your next trip to the bathroom.

      Our company has grown from 4-5 to 20+. Many of the informal processes that were adequate before are now falling flat on their faces. We are nearing the end of this current project and I finally have a chance to gasp for air... And review. And we sorely need to adjust/define many of our processes.

      We are a small company that is project oriented in the light manufacturing and assembly realm. We design and build high fidelity flight simulators. The prototype is the SW model and production quantities range from 1 to 4 units. The simulators have thousands of parts. Although we aim to commonize subsystems, each contract is typically for a unique design. The parts can be grouped as internally designed parts, standard hardware, and COTS modules like computers and other electronic black boxes and cables.

      Our design process is relatively stable. We use SW2008 and PDMWW (probably will be upgrading when this project is done to SW2009 SP5). However, we have an in-house Access DB to manage our inventory. The boss hired a friend's kid who was "real good with that sorta thing" to write the DB as a summer project. Two years later (hired him full time...) of continual work and it is still a very special experience to use. So much so I am driven to write this post!

      Google tells me there are myriads of inventory software products out there. But none of the handful I reviewed seems to do quite what I think we want. The asset tracking type software seems geared more towards fixed asset control. Warehouse inventory seems more geared to handling lots of parts in and lots of parts out. We will typically get four or five parts in, or a box of 50 screws, or a computer with a SN. These get located on a shelf. Then we need to capture the assembly. These parts are combined with some of the screws in that box and become a new part number (assembly number). Repeat that until you have the top level assembly. This assembly then will have its location changed to the final installation place for the customer. We will need to capture this final installation information for follow-on maintenance / warrantee / upgrade reasons.

      Our Inventory DB - although "special" as noted above - seemed to be functional. Until now! The DB hasn't changed, but what I am asking of it has. My job mostly involves the hardware side of things. Now that the simulators are built and running the biggest workload has been shifted to the software people. So my crew is now going back and doing the things left undone - which is a lot of filling in of the database.

      What I am realizing is that capturing the parent/child information into the database is redundant work. It already exists in our model. But the database is where the money tracking takes place so the boss says its got to be accurate. So we are recreating the same hierarchy of assembly. We also have many parts that are not in the database but need to be. We have a dedicated fab shop contracted to us (not in-house). As we begin building physical parts, we deliver a kit to the fab shop for a particular sub assembly. The kit consists of the purchased hardware and the drawings for "our" fabricated parts. We get back the subassembly. That subassembly is received into the database but not the parts it is comprised of. Well, not all. The purchased parts had to be entered in the DB to buy them but the parts created at the fab shop are not. And the partial quantity of the purchased parts are not associated with the subassembly. So with 1500+ parts in this thing, you can appreciate the amount of whining from my engineers to re-enter this information.

      On the front end of things is the issue of generating part numbers. We have a part numbering scheme. It is semi-intelligent in that all the parts of a particular subassembly have a common number grouping. When we were small I created a shared Excel spreadsheet to organize part numbers. This is a recognized area of "NEEDS Improvement".

      But the new realization to me is that I have all these different un-linked systems. Manually create a part number in the spreadsheet. Create the parts / assemblies in SW, capture the design in the vault. Then purchase materials which involves a manual review of a particular sub assembly and creating entries in the database of parts that are generally in SW, or the Vault, or Toolbox, or Library. Receive the parts, kit the parts, then request fabrication from our shop. Lots of re-entry of the same stuff.

      What I would like to see is an integrated system to generate / assign a part number. The generation of this part number would create an entry in the database and possibly create an empty model structure (anyone played with SW Labs Treehouse). Once designed in SW, a screen image of the model and some of the custom property info would be transferred to the DB. The parent / child structure from SW would also be transferred to the db.

      Still with me????

      So what products are you folks using for some or all of your steps for the total engineering and manufacturing steps. What part of this can / should EPDM do?

      Thanks for your patience in wading through my muddling. I know there isn't one piece of magical software to fix it all, but I am truly interested in what has worked for you. And I am interested in any good reads on what to consider when putting these processes together.


        • Re: Physical Inventory Management?
          Jeff Sweeney
          Have you considered using Enterprise's item explorer and making an item card to track inventory? I wrote a blog on this topic (and a few other item ideas) last week.
          • Re: Physical Inventory Management?

            Our company is going through a similar situation.  We have been struggling to find a manufacturing software to control inventory that fits our processes and does not consist of double entry.  We have imported bill of materials into QuickBooks and have been discouraged by the software’s lack of ability to deal with inventory vs. non-inventory assemblies and building of assemblies.  We are looking for an integrated manufacturing software that not only works directly with QuickBooks, but will accept SolidWorks BOMs.  Have you come across any products to help with your situation?

            • Re: Physical Inventory Management?
              Chad Schmidt

              You are probably looking for something bigger but we use parts and vendors.  It's incredibly powerful for the price.



              • Re: Physical Inventory Management?
                Hi Daen,

                It sounds like our companies are rather similar.  We used to manufacture low-volume machinery, sold to customers.  Now we only manufacture equipment for our in-house production lines.  The processes that I use for either are pretty much the same, though.  Here's how our processes work:

                I get the order to proceed on a job, then create a job number (sequential) in our job log (spreadsheet) and its associated top assembly number.  The assembly number and all part/assembly numbers are generated in an Access database.  I create all parts and assemblies, including fasteners and items for which I have no model (empty part files) and put them together in a top assembly file.  From that, I generate BOMs for the top assembly and all subassemblies.  The fun part is getting from SW to our MRP software.  I have to enter every part (including purchasing data) and assembly into the MRP system, manually.  If we had better software, I could probably extract data from SW and import it into the software to bypass the manual entry process.  Once the data is in the MRP software, the purchasing department is able to generate work orders, shortage lists, inventory transfers, etc.  The software is also used by the accounting department for AP, AR, billing, and a myriad of functions that are unknown to me.  By the way, our product manufacturing department (not related to engineering) also uses the MRP software for scheduling and material planning functions.

                All our parts are purchased from or manufactured by outside vendors and assembled in-house.

                If I were able to start from scratch and set the whole thing up again, I'd probably eliminate the smart part numbers because I'm not dealing with external customers any more.  That would allow the computer to generate part numbers instead of me having to do it.  That's one less step for me.  If you're selling mass-produced products to customers, where they need to find a part number in a catalog, you probably need smart part numbers.  If all your products are custom, low-volume, or in-house, you probably don't.  I would add a stocking location to the part and barcode everything.  That would allow me to group like items on the shelf without having smart part numbers, and allow computerized (barcode data management) inventory movement with no manual entry.  The most important part of the puzzle that I'd like to add is an MRP system that would allow me to extract data from SW and the barcode system.

                Unfortunately, I cannot recommend an MRP system to yours, as ours is terrible.  I would definitely recommend that you stay away from Epicor.

                I hope this is helpful to you.  If you'd like to discuss this further, I could send you my e-mail address or phone number.  Let me know.
                • Re: Physical Inventory Management?
                  Daen Hendrickson

                  I have not visited this post for a while and was please to see a few more folks have chimed in. I am begining to see a pattern of "misery loves company".


                  I have not had the oportunity to advance my cause much. We did get our database programmer to take a look at SW. He supposedly was able to write some code that extracted BOM info out of a SW assembly model and tried to match it to entries in the parts database. Lots of issues and problems, but perhaps a first step.



                  • Re: Physical Inventory Management?
                    Fraser Kiddle

                    We too are in the same situation. We have a couple products that are standard off the shelf relatively low volume (60 to 70 units per year) and then we do a lot of custom work.


                    Quickbooks is what we are using, but it just won't cut it and we are tired of all the redundant work of creating BOMs and part numbers in each system.


                    Isn't there someone that has this all figured out?

                    • Re: Physical Inventory Management?
                      Lee CS Young

                      I'm currently working with xTuple. I'm able to add and update items from EPDM to xTuple and I plan on doing some integration with BOMs but I haven't got that far yet. Soon though.


                      Anyway, xTuple is pretty decent and you can't beat their price point. It's miles ahead of Quickbooks, that's for sure.

                        • Re: Physical Inventory Management?
                          Brian Dalton

                          Lee, I'm looking into xTuple, too, but I can't find even 1/100 of the information I would need to get started importing data.  Can you point me to some resources that would explain what type of data I would need to import (export from EPDM) and into what format it would have to be put?  Seems a number of people are doing this but I can't for the life of me figure out how to get started...

                            • Re: Physical Inventory Management?
                              Lee CS Young

                              I moved away from xTuple and decided upon OpenERP, so it has been a while since I have looked at their API and what is required. I know they have a xTuple connect module that works as a go between if you're looking for a simple and straightforward solution.


                              IIRC their API isn't an API in the traditional sense; it uses a database schema called API that one imports data into or gets data out of. I wrote an EPDM add-in for xTuple that you can use for an example.


                              Sorry I couldn't be more help.

                                • Re: Physical Inventory Management?
                                  Jon Christensen

                                  How has OpenERP worked for you? Have you been able to take the EPDM created BOM and import it into the various modules of OpenERP well? Just curious how happy you are with the whole functionality and reliability of OpenERP and how well you have gotten it to talk with EPDM and the engineering/design side of things.



                                    • Re: Physical Inventory Management?
                                      Lee CS Young

                                      It works well. I've been able to create an add-in that adds and updates parts in OpenERP, but I haven't got around to doing the BoM. It wouldn't be too difficult to do though. (Either using xml or an addin.)


                                      Overall I like OpenERP because it is open source and I am able to add functionality. It has a rich feature set as well. Documentation is "okay", and support is difficult to come by, but once you start to understand the system it falls into place pretty easily.

                              • Re: Physical Inventory Management?
                                Andrew Schutte


                                You really touched on a lot of topics, inventory management being the most common factor. In short no matter what inventory management system you adopt, without a direct link into SolidWorks (for its BOM/parent child relationships) and your ERP system (for its shipping receiving/manufacturing/processing information, if you use an ERP system that is) what you’re asking for isn't really possible. I've seen lots of companies trying to leverage EPDM to accomplish inventory control, but EPDM lacks your job info, desired QTY, customer/order info, due dates, etc. Without one central ERP/MRP system it'll be extremely difficult to get everything working together without a huge amount of manual data entry between various systems.

                                www.smoothlogics.com offers a great ERP/MRP product that directly integrates with SolidWorks, and based on your original post, would accomplish everything you are asking for, without the need for manual data entry. They offer an Add-In which resides inside of SolidWorks to sync your property, parent/child relationships, revision, and order information between SolidWorks and their ERP/MRP system. For example, if you get kicked off on a job, you can design what's needed for that job in SolidWorks, and while working inside of SolidWorks, you can send order requests to purchasing, utilizing all the SolidWorks information. Smooth Logics ERP/MRP software would then manage the order requests from purchasing, pull items from inventory (if available), order what's not in inventory via PO's, receive those PO's (thus incrementing the inventory QTY), etc.

                                I think it would be worth your time to check out the offerings at www.smoothlogics.com.

                                • Re: Physical Inventory Management?
                                  Steven Dod

                                  A few years back I worked for a small manufacturing company that had a large MRP system that was too big and we had lots of issues.  I was tasked at that time to research smaller systems that gave us similar functionality but was easier to use and more affordable.  After many, many hours of research I narrowed my decision to 2 systems:


                                  DBA Manufacturing http://www.dbamanufacturing.com/ - this is a self install, self paced training with a lot of functionality for the price.


                                  E2 by Shoptech http://www.shoptech.com/ - this is the system they settled on.  I got everything ready for implementation then moved on before launch.  A contact there says it works very well.