Track current inventory with transactions

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:

 

Products Table: 

 

As you can see in the screenshot above, our products table has 5 fields: 

 

1. Image - to store image of the product
2. Product Name - for the product name
3. Original Quantity - the original quantity you enter when you originally add the product
4. 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 
5. 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: