In this article, you can learn how to build an Inventory Manager app.
This article walks you through building an Inventory Manager app that allows 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.
Tip: You can view the sample app here.
1. Defining the Tables
You will be adding several tables to store different kinds of records. Think of each table as its own spreadsheet.
Products
Create a Products table. This stores the details about each individual product being tracked.
Create 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
Create a Purchases table. This stores the details about each product purchase made towards your products.
Create an Auto Increment field for the first field in the table. This auto increment field will give you a unique identifier for each purchase in the database:
2. Adding a Connection Field
Now, from the Purchases table, we can create a connection field that connects to the Products table.
From the Connections panel on the right, select the green "+” button and select the “Products” table to add it as a one-to-many connection:
Create a Number Field called “Quantity” in the Purchases table that will be used to track how much stock will deplete from the total inventory:
Orders
Create an "Orders" table. This stores all the details about each outgoing Order, such as the product needed and total quantity ordered.
This table requires the exact same fields as the Purchases table, so we can copy that into a new table and name it "Orders".
Tip: You can learn more on how to copy a table's fields here.
3. Defining the Formulas
The Products table is going to need a few Sum Formulas to total the quantity of Inventory Received and Inventory Sent.
Inventory Received
Create a Sum field that will track total Quantities from the Purchases connection:
Inventory Shipped
Create a Sum field that will track total Quantities from the Orders connection:
Inventory On Hand
Create an Equation field that adds Starting Inventory to Inventory Received and subtracts the Inventory Sent:
4. Build the Live App
Now that the data is defined, it's time to build the pages for the live app so that others can use the app as well.
Current Inventory Page
This page will manage the Product records.
- From the Pages section, create a new page and select the Products table.
- Select the Menu view with a form attached to add a new Product record.
- Select the Grid view to display all Products. Include the Details view for the grid and any desired child views (such as a grid of all Purchases made for the selected Product):
Incoming Purchases
This page will manage Purchase records. This page will be used when you need to increase inventory for a specific Product; a new Purchase record must be created.
- Create a new page and select the Purchases table.
- Just like the Current Inventory page, select the Menu view with the form and the grid view along with a details view of the selected purchase:
Outgoing Orders
This page will manage Orders records. This page will be used when you need to send a specific Product out; a new Order record must be created.
- Create a new page and select the Orders table.
- Select the Menu view with the form along with the grid view of records:
Extending the Functionality of Your App
- Add multiple line items to a single order with a Line Items table.
- Set up scheduled tasks for automated inventory reminders.
- Enable user logins so users can place their own orders