This article walks you through building an Inventory Manager app, allowing you to track current inventory levels for your products. Current inventory is updated by tracking incoming shipments and outgoing orders.
The main feature is using Parent-Child Connections and Formulas to aggregate total inventory received from incoming shipments and compare it against total inventory fulfilled via orders.
You can see the example app here: https://www.knackhq.com/inventory-manager/
Define the Objects
You will be adding several objects to store different kinds of records. Think of each object as its own spreadsheet.
Products
Add a Products Object. This stores the details about each individual product being tracked. Knack offers a Quick Add option you can start with:
Add a Number field called “Starting Inventory” that will be used to track how much inventory each product starts with. By default, our product will always start with 0 quantities:
Purchases
Add a Purchases object. This stores the details about each product purchase made towards your products.
Replace the first field for an Auto Increment field instead, giving you a unique identifier for each purchase in the database:
Add a Connection Field:
- Product - Connects to Products
From the Connections panel on the right, click “Add Connection” and select the “Products” object and add it as a one-to-many connection:
Add a Number Field called “Quantity” that will be used to track how much stock will deplete from the total inventory:
Orders
Add an Orders object. This stores all the details about each outgoing Order, such as the product needed and total quantity ordered.
This object requires the exact same fields as the Purchases object, so we can copy that into a new object and call it Orders:
Define the Formulas
The Products object is going to need some Sum Formulas to total the quantity of Inventory Received and Inventory Sent.
Inventory Received
Add a Sum field that will track total Quantities from the Purchases connection:
Inventory Sent
Add a Sum field that will track total Quantities from the Orders connection:
Inventory On Hand
Add an Equation field that adds Starting Inventory to Inventory Received and subtracts the Inventory Sent:
Build the Live App
Now that the data is defined, it's time to build the pages for the live app, so others can use the app as well.
Current Inventory
This page manages the Product records.
From Pages, add a new page and select the Products object. When it comes down to selecting the view types, you can select multiple views at once.
Select the Menu view with a form attached to add a new Product record.
Select the Table view to display all Products. Include the Details view for the table and any desired child views (such as a table of all Purchases made for the selected Product):
Incoming Purchases
This page manages Purchase records. When you need to increase inventory for a specific Product, a new Purchase record must be created.
Add a new page and select the Purchases object.
Just like the Current Inventory page, select the Menu view with the form and the table view along with a details view of the selected purchase:
Outgoing Orders
This page manages Orders records. When you need to send a specific Product out, a new Order record must be created.
Add a new page and select the Orders object.
Nothing different here, select the Menu view with the form along with the table view of records:
Extend the App
- Add multiple line items to a single order with a Line Items object
- Track your inventory across multiple warehouse locations
- Set up scheduled tasks for automated inventory reminders
- Enable user logins so users can place their own orders