How to Build an Inventory Manager App

In this article, you can learn how to build an Inventory Manager app with a step-by-step walkthrough.

​Overview

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 features used are parent-child connections and formulas to aggregate the total inventory received from incoming shipments and compare it against the total inventory fulfilled via orders.

---

This article covers the following steps:

• • Step 1: Creating Tables & Fields

  • Step 2: Adding a Connection

  • Step 3: Create an Order Table

  • Step 4: Defining Formulas

  • Step 5: Build Your Pages

  • Step 6: Extend the Functionality and Design of Your App (Optional)

 

Step 1:  Creating Tables & Fields

​To begin, you will need to add several tables to store different kinds of records. Think of each table as its own spreadsheet and the table's fields as columns in the spreadsheet.

 

Products Table:

First, you'll create a "Products" table. This table will store the details about each product being tracked.

​

Next, you'll need to create a number field in the Products table named “Starting Inventory” that will be used to track how much inventory each product starts with. By default, the products will always start with a zero quantity, so it will be necessary to include the default value of "0" in the field's settings:

 

Purchases Table:

​Then, you'll need a "Purchases" table. This table will store the details about each product purchase made towards your products.

In the Purchases table, you'll need to create an auto increment field. This auto increment field will give you a unique identifier for each purchase in the database:

Step 2:  Adding a Connection

Now, from the Purchases table, you can create a connection field that connects to the Products table. 

From the Connections panel on the right side of the Builder, select the "+” button and then select the Products table to add it as a one-to-many connection:

Then, you can create a number field named “Quantity” in the Purchases table that will be used to track how much stock will be depleted from the total inventory:

 

Step 3: Create an Orders Table

You'll now need to create an "Orders" table. This will store all the details about each outgoing order, such as the product and the total quantity ordered.​

​This table requires the same fields as the Purchases table (including the one-to-many connection field to the Products table), so you can easily copy the Purchases table's fields into a new table and name the new table "Orders".

 

Step 4:  Defining an Equation & Formulas

​The Products​ table is going to need three sum formula fields to total the quantity of Inventory Received and Inventory Sent. You'll also need an equation field that adds the Starting Inventory to the Inventory Received and then subtracts the Inventory Shipped.

"Inventory Received" sum field:

First, you'll need to create a sum field named "Inventory Received" in the Products table that will track total quantities from the Purchases table connection. For the "Field to sum" selection, choose Purchase (Product) > Quantity : 

"Inventory Shipped" sum field:

Next, a sum field will need to be created in the Products table that will track total quantities from the Orders table connection. For the "Field to sum" selection, choose Order (Product) > Quantity and you can name this field "Inventory Shipped":

"Inventory On Hand" numeric equation field:

​Lastly, you'll need to create a numeric equation field named "Inventory on Hand" in the Products table that adds the Starting Inventory to the Inventory Received and then subtracts the Inventory Shipped. To do this, in the equation editor, you will input:

{Starting Inventory} + {Inventory Received} - {Inventory Shipped} 

 

Step 5:  Build Your Pages

​Now that the data is defined, it's time to build the pages for the Live App so that your users can utilize the app.

 

"Current Inventory" Page:

​First, you can start by creating a page that will manage the Product table's records.​ You can name this page "Current Inventory".

The following views can be added for a complete page:

• A menu view that links to a new page (this will automatically be a child page of the Current Inventory page) that has an add record form view to allow users to add Product table records

• A grid view that displays all Product table records and includes a link to view details of each Product record (adding the details view link will automatically create a child page with a details view, see image below).

• In the same grid view as above, include any desired child page views (such as a grid of all Purchases made for the selected Product):

"Incoming Purchases" Page:

This page will be designed to handle Purchase records, allowing your users to increase inventory for a specific Product table record by creating a new Purchase table record.

Just like the Current Inventory page, you can add a menu view with a link to a new child page that possesses a form view to allow users to add a Purchase table record. Then, on the same page, add a grid view to display all Purchase records. 

 

"Outgoing Orders" Page:

​This page will be used to manage the Orders table's records. This page is essential when you are ready to dispatch a specific Product; a new Orders table record must be created.

You can follow the same steps for this page as you did for the "Incoming Purchases" page: 

 

Step 6: Extend the Functionality and Design of Your App (Optional)

Here are a few ways you can extend the functionality and look 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​ and establish different user roles so that your app users can place their orders with your app. 

• Design the look and feel of your app to match your brand by utilizing our Live App Design settings or by implementing custom code in the API & Code section in the Settings of the Builder.

• Check out Knack's videos and tutorial library to learn more: Knack Videos & Tutorial Library

---