Inventory App | Products with Components

Long time listener, first time caller. Absolutely loving AppSheet.

I’m developing an app to manage component inventory for a circuit board manufacturer. On the surface the use-case seems a very straight-forward Inventory use-case. I have looked at all the Inventory-related Sample Apps which have gotten me fairly far…however none of them specifically address the Products w/ Components use-case.

This Topic is similar to a few others in the forums, however I’m starting a new topic to add a few additional elements of clarity to the use-case. Thanks in advance for any guidance!

Key considerations

  1. Circuit boards contain components (transistors, resistors, etc…)
  2. Different circuit boards contain different mixes of components (types/quantities)

Key outcomes
Users to input how many circuit boards of a particular type they have produced and have the component inventories updated appropriately.

Example use-case
A fully constructed ‘ALPHA’ circuit board contains (10)x-transistors, (8)y-transistors, and (5)z-resistors.

User submits he/she has produced 10 fully constructed ALPHA circuit boards. The inventory of x-transistors should be reduced by 100, the inventory of y-transistors should be reduced by 80 and the inventory of z-resistors should be reduced by 50.

I believe my questions relate to 1. Is this product/component schema possible within AppSheet? and 2. The data model (table structures) that would support these types of product/component inventory updates. Ultimately, we’re trying to manage to the ‘component’ inventory and I seem to be going round and round in my own head for the last couple of days on this thing…

Thank you in advance for any/all guidance.

My main advice I can give is to mimic the real world as closely as possible. This means have data tables that represent real world objects. Also, make updates as close as possible to when they actually happen. In your case, that means you want component updates made as soon as they are removed from Inventory and not wait until the circuit boards are completed.

You haven’t said so, but I’ll assume you want to also track the completed circuit boards in Inventory.

I think I would construct the app to focus on the state or progress of the circuit board(s) and not on the Inventory management itself - even though it might be primary purpose of the app.

For tables I would suggest:

  • Inventory - to track components, circuit boards and inventory specific info (e.g. shelf/bin location).
  • Circuit Boards - to manage and track construction of a set of circuit boards.
  • Products - Description of the various kinds of circuit boards being constructed.
  • Components - Description of various components used in circuit board construction.

Many people would combine Product and Component description info into the Inventory table. And that is acceptable. If you do, You would have a small extra coding to filter in the app for selection lists. In the future you might find yourself needing to separate the tables anyway IF business and related info expands.

General Processing

When a user is ready to create a set of Circuit Boards, they would create a new record in the Circuit Board table. I assume the user at this time gets all components they need to complete the set, so Inventory counts would be reduced at this time. This can be managed in the app several ways.

As the user works, they would update progress status. Many ways to do this and it does depend on elapsed time if its even necessary.

Once the set is completed, the user marks the record as done and this would trigger an update to increase the Inventory for that circuit board product.

You will want to have a function in the app to update component inventory when replacement orders are received to refresh the Inventory.


This is just one way to approach the problems and all very general so please do ask if you need more specific details.

4 Likes

Thank you for the well thought-out and detailed response, John. Very much appreciated.

I’ve already used your feedback to make a few valuable adjustments that has kept this project moving forward.

I’ll be digging in again later this evening so I may have more questions…but until then, THANKS.

1 Like