How to Automate Procurement in Your Shopify Store - Part 2

In our previous article about procurement automation, we went through the key requirements these systems must meet, and also shared the first Make scenario that will set you on course to build one without code.

The goal of that initial scenario was to keep track of Shopify inventory levels via Airtable, and check when stock levels for every product reach a certain point.

Now, for the second part we will iterate the system by showing you how to automatically add products and suppliers from Airtable to QuickBooks. 

Syncing Airtable and QuickBooks will help you align inventory with finances, and represents the previous step before we automate purchase orders, and by extension the entire procurement process.

Before we launch ourselves into the tutorial, let’s take a quick look at how the following solution works. 

Syncing QuickBooks and Airtable to keep track of inventory

After learning how to send Shopify inventory data to Airtable, it’s time to see how to send some of the resulting Airtable data to QuickBooks. 

To achieve this, we will create two simple scenarios using Make. 

The first one will add items from Airtable to QuickBooks, and also add item IDs from QuickBooks back to the table. This will help us prevent duplicate items in QuickBooks from the get-go.

In turn, the second scenario will add suppliers from the “Supplier” table in Airtable to QuickBooks. 

The vendor ID will be returned to Airtable, and also checked to avoid duplicates.

Since only the end user knows which vendors supply each product, the task of adding a “supplier ID” to each product on the inventory Airtable base will have to be fulfilled manually by this person.  

Needless to say, we will rely on the exact same Airtable base we used to create the initial scenario in this series.

It’s highly recommended that you check out Part 1 of this series to learn how to get to the scenario builder, search for apps and modules, create connections, and more. 

Now, we are going to jump straight into action.

Scenario A: Automatically create products/items in QuickBooks from items on Airtable

For this scenario, we will employ three Make modules, which are:

• Airtable > Search Records

• QuickBooks > Create an Item

• Airtable > Update a Record

In order to proceed, start by creating a new Make scenario from your dashboard, and get ready to rumble.  

Step 1: Adding the Airtable module

Once in the scenario builder, add the “Airtable > Search Records” module.

In the configuration box, select the “Shopify Inventory” base and choose the “Inventory” table. 

When the items are added to QuickBooks, the “Item ID” will be added to that item’s record on Airtable as well. 

It’s important to realize this, because when there’s no item ID on Airtable, it means that the item in question has not yet been added to QuickBooks.

To solve this situation, we will use a formula that will search for records on the table for items without a QuickBooks Item ID.

Simply copy and paste the formula below in the Airtable module to achieve this:

AND(NOT({Product Name} = "" ), ({Quickbooks Inventory Item ID} = ""))

Also, increase the “Limit” field count to 100.

Once you do this, click “OK”, and move on to the next step.

Step 2: Adding the QuickBooks module

After configuring the first module, go ahead and add the “QuickBooks > Create an Item” module to your scenario.

In the configuration box that follows, hit the “Add” button to connect your QuickBooks account to Make.

Then, map the “Product Name” element from Airtable to the “Name” field, select “Inventory” as the “Type”, and the “Inventory Asset” account.

Next, set the “Quantity on hand” for your products. For reference, a quantity of 20 is used in this tutorial, but you can input any figure here, as the actual stock level we are after will be pulled from Airtable, not from QuickBooks.

Ensure that the “Track quantity on hand” option is selected as well.

Now, scroll down to the “Inventory start date” field and map the “Now” variable located under the “Date and Time” functions (see the image below for reference).

Afterwards, configure the module as follows:

• Select your Income account (The account type in QuickBooks must be ‘Income’ and Detail Type ‘Sales of Product Income’)

• Select your Expense account (The account type in QuickBooks must be ‘Cost of Sales’ and Detail Type ‘Suppliers & Materials - COGS’)

• Map the Variant Name element to the Description field

• Map the Unit price element to the Unit price field

There are other options in this module that you can configure if needed; however it’s not required for this tutorial.

Once you are done, hit “OK”. 

This module is now ready to add items to QuickBooks from the previous module.

Now, it’s time to add Item IDs to Airtable automatically. 

Step 3: Updating Airtable records

To update Airtable item IDs with QuickBooks data, add the “Airtable > Update a Record” module to your scenario. 

In the configuration box, choose the “Shopify Inventory” base and the “Inventory” table. 

The module needs to know which record it needs to update, so map the “ID” element from the first Airtable module to the “Record ID” field.

Next, map the “Item ID” element to the “QuickBooks Inventory Item ID” field, and click the “OK” to finish the configuration of this module.

Before we wrap up this scenario, we need to add a filter between the first Airtable module and the QuickBooks module.

The reason why we need a filter is fairly simple: It will help us prevent errors.

If there are no records matching the formula in the “Airtable > Search Records” module when the scenario executes, the filter will prevent the scenario from continuing to the QuickBooks module and resulting in an error.

So, after you add a filter, start by naming it once the filter’s configuration box pops in front of you.

Then, map the “Total number of bundles” element, select the “Greater than” numeric operator, and enter “0” in the bottom field. 

If the total number of bundles is 0, it means no records were found, and the scenario will stop processing at the filter.

Step 4: Testing the scenario

Assuming that your Airtable table is already populated with data from the initial scenario in this series, it’s time if the new scenario is capable of adding the products to QuickBooks. 

Hit the “Run once” button - the scenario should work as shown below.

If the scenario works correctly, all you have to do is save it, and schedule it to execute just after the initial scenario does.

This way, it will include any new products that you may have added to Shopify.

Step 5: Assigning a rotation frequency to the items

In your store, the maximum quantity (SKUs) for each item varies. When the quantity reduces to a certain level, a purchase order must be created for the item.

We understand that your store might have hundreds of items, and that the minimum quantity levels that trigger purchase orders may vary. 

For example, let’s say you have product A, B, and C. Perhaps you want the system to send purchase orders when stock level for product A reaches 10 units. For product B, on the other hand, you want the system to do the same, but when the stock level reaches 25 units. 

Our system lets you manage this.

For the sake of brevity, we determined three types of rotation frequencies, which can be assigned to items that fit each one of the following categories:

• High: Order these items when the quantity drops below 50% of the maximum quantity (you can adjust the percentage accordingly)

• Medium: Order these items when the quantity drops below 20% of the maximum quantity (you can adjust the percentage accordingly)

• Low - Don't order this product again (or do it manually)

You can always add more categories as you require, and change an item’s rotation level at any time according to your specific needs.

Now, onto the second scenario of this post, and the third of the series.

Scenario B: Automatically create suppliers/vendors in QuickBooks from suppliers details on Airtable

This scenario is very similar to the one above. The main change is that it sends supplier data to QuickBooks instead of product data. 

To do so, it relies on the following modules:

• Airtable > Search Records

• QuickBooks > Create a Vendor

• Airtable > Update a Record

Now, let’s get at it again.

Step 1: Adding the Airtable module

Just like you did before, create a new scenario from your Make dashboard, and add the “Airtable > Search Records” module.

In the configuration box, select the “Shopify Inventory” base, but this time select the “Suppliers” table.

Copy and paste the formula below to the “Formula” field to search for suppliers without a Vendor ID on the table (and once again, to ensure that you don’t duplicate vendor records in QuickBooks):

AND(NOT({Supplier Name} = "" ), ({Quickbooks Vendor ID} = ""))

Once ready, click “OK”. 

Step 2: Adding the QuickBooks module

Now, it’s time to add the “QuickBooks > Create a Vendor” module. 

In the configuration box, map the Supplier Name, Email, Phone number and Billing Address elements to the corresponding fields, as shown below:

Once you do this, hit the “OK” button, and add the final module.

Step 3: Adding the Airtable module

To wrap this scenario up, add the “Airtable > Update a Record” module. 

In the configuration box, map the record ID and the Vendor ID accordingly.

As in the previous scenario, add a filter between the first and second module, and configure it the exact same way you did before to prevent errors.

Step 4: Testing the scenario

Once you are finished, hit the “Run once” button to see if the scenario works as expected. 

Again, your “Suppliers” table should have some records in it; otherwise, no vendors will be created in QuickBooks. 

Below you can see what it looks like when the suppliers are created as vendors in QuickBooks and the Vendor IDs are added to the table.

You can schedule this scenario to execute at the same time as Scenario A.

Before we wrap this up, let’s not forget about a very important detail, which also happens to be the only manual task required to make this work.

Step 5: Adding the supplier/vendor to an item on the table

Now that you have the items with their corresponding QuickBooks IDs, and the suppliers/vendors with their IDs on their respective tables, let’s go over to the Airtable Base and open the Inventory table. 

Here, you need to manually add the supplier for each product to the corresponding product records. 

To do this, simply enter the Vendor ID from the Suppliers tab in the QuickBooks Vendor ID column. 

This data is important, as it will be used in Part 3 of this series, where we will automate the creation of purchase orders. 

Needless to say, you can input vendor names here (instead of Vendor IDs) if you want to create the orders manually.

Conclusion

Once again, congratulations! You are now closer to the goal of having your own fully automated procurement system that:

• Checks inventory levels of your Shopify store

• Lets you know when certain items reach certain stock levels

• Creates purchase orders and sends them to the corresponding vendors

Stay tuned for the next post in this series, and make sure you bookmark this post.

Happy automating!