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:

• Product code - Autoincrement field generates the product code.
• Name - product name (Single line).
• Price - product price (Number field).
• Barcode - Formula field which takes the value from the Product code field with the "tv" prefix. To display a barcode, activate the 'Generate Barcode in the table' option.
• Quantity - initial stock quantity (Number field).

Fill in the table with products:

The next step is to create an order form. We have explained in detail how to create an order form using a Subform. You can find instructions with an example by this link.

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:

Now you can perform additional operations with this column, calculate the sum and average, and generate reports.