Stock Management In Airtable

Let's make Stock Management In Airtable easy. In this article, you will learn how you can create records in Airtable using junction tables. This will be helpful in your Airtable stock management system to perform many different types of operations with your data. This article will cover how to create sales records and relevant sales items dynamically from them. We will develop a script that allows us to extract sales table records and create multiple records in a junction 'line items' table. We will use the Airtable scripting app in this process. Let's start by going over the current Airtable base structure, which is necessary for the system we are building.

For this example, I have the following 3 Tables in my base:

1. Products table : All the products are stored in this table.
2. Sales Table: All sales records are in this table.
3. Junction table : This table contains the sales items (all the records that connect a specific product to a specific sales record).

I haven't created linked fields in this article to make it simple and easy to understand, but you can easily enrich it with lookup fields to take the most out of it. First, you need to install a scripting app. You can install it from the top right corner. Once you install the app, you need to copy and paste the code. Click Here to get the code. Once you copy-paste the code in your app, simply create a new field in the sales table of the 'button' type and link it with the scripting app where you pasted the code as shown in the image below.

Once the button is linked to the app, each time the button is clicked, it creates a new record in the junction table. Now let's see it in action! So, we have a script that will be executed each time the button is clicked. We need to click the button when we want to create the records in the junction table. Most probably we would like to create records when a new entry is created in the sales table. We can automate this process but we are not doing it in this example, I will explain to you why later on. Now let's see what the script is doing and how the process works.

Process

1. First we click on the button
2. Then our script is executed
3. Our script automatically takes the data of the record whose button was clicked.
4. Then it creates the record in the junction table by executing a loop.

Here the format of the fields is very important. For example, in our case we have a sales records table as follows:

As you can see in the image above, we follow the following format for products and quantity fields: 'Product 1' and 'Product 1 Quantity', 'Product 2' and 'Product 2 Quantity' etc. This is important because while going through the loop with the provided script we need this format. The benefit of using this format is that you don't have to worry if you want to add new columns in the sales table or if some fields are empty as it automatically adapts to any situation Why have we not used a completely automated process, avoiding the button click? Because in the basic free airtable plan you cannot execute scripts in airtable automation option and I wanted this tutorial to be available to everyone. To achieve a fully automated workflow, you need a paid plan, if you have one, follow the steps below in order to make this process completely automated.

Automation solution

1. Create a custom automation
2. Select the trigger 'When a record is created' and select the sales table
3. Now select the scripting app

With this, you need to modify the script a little and identify the record created through the form and execute the script on it. If you need any help regarding this workflow or other Airtable processes, feel free to contact me for an initial free consultation. Your junction table is now ready to rock. You can improve visualization by grouping records by-products or another field, depending on what you want to show. The following image gives you an example of such a grouped view:

Feel free to contact me for a free Airtable consultation by Clicking Here