Building an Inventory Management System

Transcript Summary

Introduction

Hey everybody, it's Tim from Database here, and this is Inventory Management. Today, we’re going to build an inventory management application using database and all its native features and tools. When we're finished, you'll have a fully built application to manage your products, inventory, warehouses, and customer orders.

Application Overview

We’ll cover how to:

Finished Application Preview

You’ll have pages that display information about products, including charts and graphs, and a list of products for a quick overview. Viewing product details will show inbound and outbound inventory records, letting you track how many products are in any location or warehouse.

We’ll also manage suppliers, customers, and track the movement of products using inbound and outbound inventory records.

Inbound and Outbound Inventory Records

The key function is tracking:

Warehouses and Storage Locations

We’ll track warehouses and storage locations within them, like shelving or bins. These will indicate specific places where inventory is stored.

Building the Application: Data Structure

To create the application, we’ll focus on:

Creating the Inbound and Outbound Inventory Tables

Automating Inventory Management

We’ll set up a rule to automatically pull connected data (product, supplier, warehouse, storage location) from the inbound inventory record when creating or editing an outbound inventory record. This ensures that every outbound record is accurate and consistent.

Calculating Quantity on Hand

Next, we’ll use complex formulas to calculate:

Final Steps

We’ll set up similar calculated fields at the product level to sum the quantities from all storage locations and get a total count for each product across the organization.

That’s it for the theory—let’s jump into the builder and start creating our inventory management application!

Handling Incoming Statuses and Inventory Tracking

In this section, we will cover how to handle the incoming statuses for products, such as when items are ordered, how many have been received, how many are still expected, and whether reordering is necessary. We'll start by building fields that calculate these statuses using equation fields and sum functions.

1. Total Ordered:

   • We want to know how many items were ordered, starting with the sum of incoming quantities from the inbound inventory data table.

2. Total Received:

   • Next, we calculate how many items have been received by summing incoming quantities where the status is marked as "delivered."

3. Total Waiting:

   • Similarly, we determine how many items are still pending by summing incoming quantities where the status is "ordered."

4. Total Outbound:

   • We use the outbound inventory data table to sum the quantities that have been shipped out.

5. Total On-Hand:

   • We calculate the grand total of all products on hand across storage locations by summing inbound inventory records. This will provide a total count of available stock.

Expected Total and Stock Match Status

Now that we have the total ordered, received, and outbound quantities, we can create a more complex formula to compare expected stock levels with the actual quantities on hand.

1. Expected Total:

   • The expected total on hand is calculated using the formula: (Total Received - Total Outbound) + Total Waiting. This should match the actual total on-hand inventory.

2. Stock Match Status:

   • To ensure accuracy, we create a new field, "Stock Match Status," that compares the expected total with the actual total on hand. If they match, the field will display "Stock Match"; otherwise, it will show "Stock Mismatch."

Reordering Indicator

Finally, we'll create a reorder indicator to alert when stock falls below a minimum quantity:

1. Reorder Indicator:
   • This field will display "Time to Reorder" when the total on hand is less than the minimum reorder quantity.

Building the Data Structure

At this point, our data structure is complete. We have created all the necessary data tables and fields to accurately track product quantities, incoming and outgoing inventory, storage locations, and supplier details. Now, we’ll move on to populating the system with data and displaying the information on the pages we’re about to create.

Importing Data and Creating Pages

After importing and creating sample data (e.g., product names, images, minimum quantities), we see that all our calculated fields, such as total ordered, received, and on-hand, are being populated based on connected data. This allows us to track the movement of items and inventory levels.

Managing Pages and CRUD Operations

For each data table (products, suppliers, customers, warehouses, and storage locations), we’ll create pages that allow users to:

• View records
• Add new records
• Edit existing records
• Delete records

Detail Pages and Connected Data

We will also create detailed pages that provide an overview of each product, showing:

• Inbound inventory records (connected to the product)
• Outbound inventory records (connected to the product)
• Supplier, warehouse, and storage location details

We repeat this process for suppliers, customers, warehouses, and storage locations to ensure complete inventory tracking from every perspective.

Warehouse Pages and Mapping

For warehouses, we will add a map display to visualize the warehouse location alongside the inbound and outbound inventory. This provides a complete overview of each warehouse's storage details.

Summary

We have now created the full structure for managing and tracking inventory, including handling incoming statuses, expected stock levels, reorder indicators, and connected detail pages. This system allows for efficient tracking and monitoring of all inventory movements across products, suppliers, customers, and warehouses.

Setting Up Outbound Inventory Form

But I would likely go through here and make sure that all of these fields are required for adding our data into our application. Now, next for our outbound inventory, this is gonna be essentially the same thing, but slightly more complex in how we actually configure the form.

We're gonna add records to, or display records from our outbound inventory table with a form to add new ones. We'll finish populating the fields here and then go into our form component and start building it out.

Since we created those table rules to automatically populate the connected product, supplier warehouse, and storage location, we don’t need them on the form. All we need is the inbound inventory record—where are we pulling the inventory from a customer, where it’s going (this may or may not be required), and an outbound quantity (this one is definitely required).

We'll make sure to require the relevant fields. Now, the inbound inventory record, when we created this field, we connected outbound inventory to inbound inventory and chose a display field. If we chose something like product, supplier, or warehouse, there might be duplicates—multiple inbound inventory records for the same product, supplier, or warehouse.

To avoid confusion, we either select a unique display field or configure the connection field to show multiple connected values. We’ll change the connection field style from a standard dropdown to a list or table view, showing multiple pieces of information like SKU, product name, supplier, and warehouse. Now we can select the product from a table format with all necessary details.

Adding Charts for Better Overview

Now, I’d like to add charts at the top of the products page to give us an overview of our data. We’ll add a row with three columns and move this row to the top of the page.

The first chart we’ll add is a pie chart for outbound inventory, showing our most popular products. We'll make it a donut chart, group by product, and display the total number of records.

We could also display outbound quantity instead of just the number of records, showing how many units went out. I’ll leave it as outbound quantity and change the theme to Google for a bright look.

Next, we’ll add a bar chart grouped by product name, showing the total quantity of products on hand. Finally, we’ll add a status overview of all the products we’re ordering from suppliers, grouped by their incoming status (delivered or ordered).

Finalizing the Application

At this point, the application is almost done. We could ship this product into production, and you’d have a functioning application to import and track inventory in your warehouses and storage locations, along with managing suppliers and customers.

While there’s still more that can be expanded, this is a good starting point. One of the next expansions could be the customer ordering process, which I’ll show you now.

Building the Customer Ordering System

We'll add another data table called "Orders" to track a parent-level object, which will keep track of multiple line items (outbound inventory records). We’ll create an order connected to a customer or another facility, with multiple items in it.

First, we add the orders table, connect it to customers, and add order types and statuses (new, in progress, fulfilled, shipped, canceled). Then, we go into the outbound inventory table and add two new fields: one to connect the outbound record to an order and another to track item status (unfulfilled or fulfilled).

After creating these fields, we’ll set up the pages to create, update, and delete orders, along with adding connected items to the order. We'll display a table of orders with options to add a new order, view details, and add outbound inventory records to the order.

When adding outbound inventory to the order, we automate some of the form fields, such as setting the customer field from the connected order. We’ll also automate setting item status to unfulfilled for new items, which can later be updated as they are fulfilled.

Conclusion

At this point, we’ve completed a fully functional application that tracks both inbound and outbound inventory, manages customer orders, and displays relevant data in charts. You now have an application that can be customized and expanded based on your needs.

Thank you for watching, and I’ll see you next time.