Inventory

Inventory is a parts inventory database that I am creating to help me generate a bill of materials and order parts. Today I track this information in spreadsheets. Not only is this time consuming, but it is very difficult to reuse part details between projects. The most time consuming activities are selecting a vendor for a particular part and determining if that part is already in my part inventory. This project will attempt to automate both tasks.

For now I'm building the database in Microsoft Access. I would really like to build it based on a good web user interface, but that is way beyond my web skills. I can build simple Access forms and reports in my sleep. Maybe the information I document here can help someone else recreate the database using other technologies.

Table Of Contents

Concept

The concept for this project is to automate two primary activities, selecting vendors for a part and ordering parts for a bill of materials based on current inventory. A secondary activity is to automate the creation of a project reference web page based on information in the database.

Terms

Project - the object used to generate a project documentation web page. Assemblies are associated with a single project; a future code update might allow an assembly to be used in multiple projects.
Assembly - a single PCB, enclosure, etc. and all the components of that object.
Manufacturer Part - focal point of the entire database and is referenced by one or more vendors, one or more assemblies, one or more builds, and one or more POs.
Vendor - a supplier of a specific manufacturer part
Inventory - a shadow of (or same as) the manufacturer part database. Contains the dollar cost averaged purchased price and quantities for parts in inventory.
Build - lists total quantities of each part required to build a given quantity of the assembly and created from an assembly bill of materials. The build process also automates inventory, PO creation, and total cost calculations.
PO - represents an order to a vendor and is the object that is responsible for adding to inventory. Because it adds to inventory, a PO is used to add a completed build into inventory even if it was created internally automating cost tracking.

Not Supported (yet?)

• Referencing Assemblies in multiple Projects - this is easy to add, it just requires an additional table, UI updates, and some logic
• Complex work flow automation for assemblies of assemblies - the database supports subassemblies, however the work flow associated with builds, POs, and inventory tracking can get quite complex. I need to "use" the software for a while before I'll know the best way to support this work flow. I may need to rethink the notion of using a PO to receive a completed build, but a PO is really needed when using a 3rd party assembly house.

Use Cases

• ACD all Tables

Screens are provided to maintenance of all tables. Generally data entry for supporting tables is accomplished using pop-ups during data entry on the main work flow screens. Fields allow entry of undefined values which then generate a pop-up screen to complete the data entry for that supporting table.

• Import / Update Assembly from Netlist

This activity populates the Assembly from a schematic netlist.

• Assign Mfg Parts to Assemblies

During this activity the user selects a manufacturer's part for each assembly component. For a PCB the user can also select a part footprint from the manufacturer's part table.

• Push Updates back to the Schematic

Pushes back the information from the Mfg Part selection (e.g. footprint). This activity really needs to be customizable by the user. For example some users will want to push back part numbers, tolerances, and descriptions, some will not. Some (like me) will want to maintain footprints in the inventory system, some will not.

• Open a Build - status = open

The build process is a major point of automation for inventory and cost tracking. When opening a build the user completes the following tasks from the build screen:

• Select an Assembly and quantity for the build
• View inventory on hand and on order vs quantity required for the build
• View suppliers and prices for each part that needs to be ordered
• Selecting a vendor to order from updates an open PO or generates a new PO for that vendor
• One open PO line item is associated with each build line item on order
• PO line items are updated, added, and deleted as the user makes vendor selections
• Initiate a Build - status = building

This activity is available once all parts are in inventory (i.e. open POs have been ordered and received). Initiating a build takes the following actions:

• Creates a PO for the build allowing the completed product to be received into inventory and updating inventory to show the product on order.
• Removes parts from inventory
• Prints a pick list for parts
• Complete a Build - status = complete

Completing a build marks the PO received, adding the product to inventory. This activity can be selected from either the PO screen or the Build screen.

• Submit PO to Vendor - status = OnOrder

Includes a work flow screen that lists all open POs and allows selection of a PO to submit

• Manually update quantities to match price breaks

Future features should add tracking for multiple price breaks in the vendor part table.

• Marks the PO ordered so that future build processes will not add more items to the PO
• Presents various methods for submitting the PO (e.g. print a list). Methods might include some EDI automation in the future.
• Receive a PO - status = received
  • Subtract quantity from qty on order
  • Add quantity to qty on hand
  • Dollar cost average the item cost
  • Update the cost in the vendor part table

Project Web Page Layout

Database Schema

Screen Shots