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


Please, login to QuintaDB to be able to post comments
Share the discussion in the social networks: