English Flag English Español Flag Español Deutsch Flag Deutsch Italiano Flag Italiano Polski Flag Polski Nederlands Flag Nederlands Português Flag Português Français Flag Français 中文 Flag 中文 日本語 Flag 日本語 हिन्दी Flag हिन्दी اللغة العربية Flag اللغة العربية Русский Flag Русский Українська Flag Українська עִברִית Flag עִברִית Ελληνικά Flag Ελληνικά Türk Flag Türk Latvietis Flag Latvietis Dansk Flag Dansk Norsk Flag Norsk Íslenska Flag Íslenska 한국어 Flag 한국어 Suomen Flag Suomen Gaeilge Flag Gaeilge Bahasa Melayu Flag Bahasa Melayu Svenska Flag Svenska Čeština Flag Čeština
User IconLog In
English Flag English

Log In


How to count on-hand inventory

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:

  • Product codeAutoincrement 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.