In this article we'll look at how to create an inventory tracking system. Our goal is to be able to track every time a product is added or sold and see the resulting balance in the products table.
For example, say we have a Products table with all our products listed. Say we have a product called ProductA with quantity of 100 and we add a new transaction for ProductA with a value of 25, our goal is to deduct a quantity of 25 from the ProductA record, resulting in a balance of 75 for ProductA in the Products Table.
Now that we've defined our goal, let's go over how to accomplish this.
For the purpose of this use case we'll have two tables:
Table 1: Products
Table 2: Transactions
In the Products table we'll add and list all our items/products and quantity balances.
In the Transactions table we'll add each transaction or quantity usage.
When a new transaction is added, it will update the Products table to reflect the new updated quantity.
Let's take a more detailed look at how to accomplish this in the builder:
As you can see in the screenshot above, our products table has 5 fields:
- Image - to store image of the product
- Product Name - for the product name
- Original Quantity - the original quantity you enter when you originally add the product
- Amount Used - This is a complex formula that Sums all the values from the Transactions table to see the quantity that needs to be deducted from inventory
- Balance - This is a Basic Formula field to subtract the Amount Used from the Original Quantity to show us the updated balance in the Products Table
As you can see in the screenshot below, we have a connection from the Transactions table to the products table which means we can SUM all the "Quantity" from the transaction table and update the "Balance" field in the Products table. This is done using the Amount Used field and the Balance field. Let's look more closely at how we structured these fields:
In the Products table, the Amount Used field will sum the values from the Quantity field in the Transactions table.
Once we sum the quantity used, we need to subtract the Amount Used from the Original Amount which will yield our Balance.
Let's see how this all looks in action! We'll start with an action shot of building out our structure:
Now that we've built our pages, let's see what happens when we add new transactions for our iPad Product. You can see we open the iPad details page, add a new transaction, and the Balance is updated correctly: