Step by step guide
In this article you will learn how to easily get the on-hand inventory for the further planning of your business strategy. On-hand inventory is usually calculated by subtracting the items that have already been "picked" for sale (or already sold) from the total amount of starting stock.
Let's start by creating a simple inventory form. The Products form contains the following fields:
Fill in the table with products:
Create the Orders form linked with the Products table using the "Product" Relationship field.
Create another form - Order form, where the previous form (Orders) is used as a Subform.
Complete the table with orders. Enter the number of product units sold (Qty column):
To calculate the remaining amount of inventory, you need to return to the Products table. Since we have a relationship between the Products and Orders table, we can add the Linked records count field to the Products form:
Using this field type, you can show the number of records in related tables. In the table this column will display the quantity of sold products.
Now it's enough to create a formula that will subtract the quantity of sold products from the initial stock quantity (Quantity column):
As a result, we get the inventory on-hand (Remaining stock) column in the table: