How to Automate Procurement in Your Shopify Store - Part 3
Previously on this series, we have gone through the steps required to build an automated procurement system from scratch.
In our first procurement automation article, we showed you how to check inventory levels by means of a simple Shopify and Airtable integration.
Next, we went on to introduce solutions that send product and vendor information to QuickBooks once inventory levels reach pre-defined levels.
Today is the time to show you how to automatically create purchase orders and send them to the corresponding vendor once the quantity of a product reaches a certain level and status.
Before getting started, let’s take a quick look at what you will encounter here.
Purchase order automation: An overview of the process
In this tutorial, we will build an Make scenario that creates purchase orders and sends them to the corresponding suppliers automatically.
To do so, we’ll rely on the following Make modules:
Airtable > Search Records
Array Aggregator
Iterator
Quickbooks > Create a Purchase Order
Quickbooks > Make an API call
Quickbooks > Get a Vendor
Gmail > Send an email
Airtable > Update a Record
The scenario works in a rather simple fashion.
The first module searches the inventory table for items that fall under certain criteria.
If the criteria is met, suppliers for that product are aggregated and iterated, so we can search for all the products for that supplier that needs to be ordered. This is done to avoid sending multiple purchase orders to the same vendor.
Then, the purchase order with all the items required from a supplier is created and emailed.
Bear in mind, the tutorial doesn’t end here. In the next and final part of this series, we will build a scenario that receives the invoices from suppliers, sends them to finance, and updates the inventory table once the items have been received.
Now, let’s get to work: We are closer than ever to creating an automated procurement system with everyday apps.
Step 1: Retrieving items that reach certain inventory levels
In part 2, you assigned a rotation frequency to each item, which plays an important role here.
As a reminder, the frequencies (and the percentages) used in this demo are: the frequencies with its percentages used in this demo:
High - purchase order gets sent when the item’s quantity drops below 50% of the maximum quantity
Medium - purchase order is sent when the item’s quantity drops below 20% of the maximum quantity
Low - No purchase order is sent automatically (you can send it manually, change the frequency, or eventually remove the item from your store)
So, now it’s time to see how to retrieve items with a High or Medium frequency that need to be ordered, and that have not been ordered already (more on the Ordered field later).
To kick things off, you will need to create a new Make scenario from your dashboard.
Once in the visual builder, add the “Airtable > Search Records” module.
In the configuration box, select the “Shopify Inventory” base and the “Inventory” table.
Paste the formula below to search for items that meet the required criteria mentioned above:
OR((AND(({Quantity} < {Maximum Quantity for item} *0.5), ({Ordered} = 0), ({Rotation} = 'High'))),(AND(({Quantity} < {Maximum Quantity for item} *0.2), ({Ordered} = 0), ({Rotation} = 'Medium'))))
You can adjust the percentages in red to suit your needs (0.5 refers to when inventory level of an item drops below 50% of the max quantity, 0.2 when it does below 20%).
Also, don't forget to increase the number of records retrieved in the Limit field when the scenario executes (it’s set to 10 by default, 100+ would be better).
Once the item records have been retrieved, the suppliers for these items also need to be fetched.
It's quite common for a vendor to supply more than one item for your store. In such cases, you want to send a single purchase order with all the items needed from that vendor instead of an order for each item.
Example:
Supplier 1 - Purchase order for product A, product B.
Supplier 2 - Purchase order for product C.
The process that we will follow to accomplish this works as detailed below:
The retrieved vendors IDs are aggregated first (individual data merged into a single bundle)
The items are then iterated (split into different parts for processing)
By iterating the data, each vendor is matched to all their items you are ordering from them
The items are then aggregated so they can be sent in one purchase order to the vendor
Step 2: Aggregating the data
Open the Tools menu at the bottom of the visual builder and add the “Array Aggregator” module to the scenario.
In the configuration box, simply tick the “Quickbooks Vendor ID” field to aggregate them like you see below:
Once done, click “OK”, and move to the next step.
Step 3: Adding filters
As with the previous scenarios we created, filters are required here as well. The goal is the same: To stop the scenario from processing when no records are found.
So, click the little wrench sign between the Airtable module and the array aggregator to add a filter.
In the configuration box, add “Total number of bundles” as the condition, select “Greater than” as the data operator in the dropdown, and “0” in the field that follows.
Once done, click the “OK” button.
Step 4: Iterating the data
Now, it’s time to add an “Iterator” module to split the data into separate parts for processing.
To do this, select it from the Tools menu at the bottom of the builder, just like you did with the array aggregator.
Why? Simple: Since there can be multiple items with the same vendor (retrieved in the first module), that vendor ID will also appear multiple times in the array.
As a result, we need to remove these duplicate IDs so that the vendor doesn't receive the same purchase order several times.
To do this, we will use the “deduplicate” function, which you’ll find in the “Functions for working with arrays” menu.
Map the Array[] element, and enter Quickbooks Vendor ID within the brackets as shown below:
After this, click the “OK” button and move to the next step.
Step 5: Matching the vendors to their items
Thanks to the iterator, vendors IDs will pass through the scenario one at a time.
We can now match the vendors to the items you’re ordering by using these IDs.
To do this, first add the “Airtable > Search Records” module.
In the configuration box, paste the following formula and map the “Quickbooks Vendor ID” element outputted by the Iterator within the quotation marks (“”).
OR((AND(({Quantity} < {Maximum Quantity for item} *0.5), ({Ordered} = 0), ({Rotation} = 'High'), ({Quickbooks Vendor ID} = ""))),(AND(({Quantity} < {Maximum Quantity for item} *0.2), ({Ordered} = 0), ({Rotation} = 'Medium'), ({Quickbooks Vendor ID} = ""))))
Also, don’t forget to change the percentages like you did in the previous formula if needed.
Step 6: Creating the purchase order and aggregating the items
You have reached the main part of the entire use case series: Creating the purchase order automatically.
In the previous module, the vendors with their items were fetched.
Now, the goal is to aggregate the items to one purchase order, which is key when you are ordering multiple items from the same vendor.
To do this, add another array aggregator to your scenario, and select the “Airtable > Search Records” as the Source Module.
It’s important that the structure of the array is in line with the structure of the purchase order. This will let us map the data in the subsequent module.
However, under the “Target structure type”, you’ll only find the Custom option available, which is not the one we need.
For the right structure to appear in the list, you’ll need to add and configure the “Quickbooks > Create a purchase order” module first, and then open the “Target structure type” menu again.
So let’s do that. We’ll come back to the array aggregator in a bit!
Once you add the “Quickbooks > Create a purchase order” module after the second array aggregator, configure it as follows:
Select the AP Account
Turn the map switches on for the two fields below
Map the “Quickbooks Vendor ID” element to the “Vendor” field
Map the “Array[]” element to the “Lines” field, which contains all the data for the order we’ll create in the array aggregator shortly
Once you do this, click the “OK” button, and open the second array aggregator module.
In the configuration box, select the “Quickbooks > Create a purchase order Lines” option that now appears in the list.
Now, let’s map the data to the fields that appear below.
To calculate the amount, use the “sum” function from the “Math functions”. Then, you’ll have to do the following:
Map the “Maximum Quantity for item” element (outputted by the previous “Search records” module)
Subtract the quantity on hand by using the “subtraction” operator, and map the “Quantity” element
After the bracket, multiply it by the unit price by using the “multiplication” operator, and map the “Unit price” element
Also, map the “Variant Name” to the “Description” field and select “Item based” under “Type”.
We are not done here yet, but see the image below for reference:
Now, let’s proceed with the configuration of this module. When you scroll down:
Map the “Quickbooks Inventory Item ID” element to the “Item” field
In the “Quantity” field, again map the “Maximum Quantity for item” element and subtract the current “Quantity”
Lastly, map the “Unit price” element to the “Unit price” field
Congratulations!
You have now configured the array aggregator and the “Create a purchase order” modules.
At this point, execute the scenario, so that the ID of the order can be outputted by the “Create a purchase order” module.
This is required so the next module works as we intend to. You’ll see how it's used shortly!
Change the quantity of an item/s to 0 on the Inventory table, and hit “Run once”.
In the next step, we’ll see how to email the purchase orders to the vendors.
Step 7: Downloading and emailing the purchase orders
The purchase orders that are created in the previous module need to be downloaded before you can send it to the vendors.
Currently, there’s no dedicated QuickBooks module to download purchase orders, so we need to use Make’s “Make an API call” module to do this.
So, add that module to your scenario, and then paste the following in the “URL” field: purchaseorder//pdf
Then, map the ID (that is now available after you executed the scenario) as shown in the screenshot below:
The file is a PDF, so under “Headers” make the following changes:
Change the Value of the Content-Type Key to: application/pdf
Add a new item: Key - acceptValue - application/pdf
See the image below for reference:
The purchase orders will be downloaded using this module. Now, you just need the vendor’s email address to send the PO right away.Add the “Quickbooks > Get a vendor” module to your scenario, and simply map the “Vendor: Value” element from the “Quickbooks > Create a purchase order” module.
Time to send the email!
To do this, add the Gmail (or Email; both work here) “Send an email” module to your scenario.
This module is fairly straightforward to set up.
You first need to toggle the map switch in the “To” field, and map the “Primary email address: address” element from the previous module (which is the vendor’s email address).
You can also CC your finance department so they can keep a record of the purchase orders.
For the “Subject line” and “Content”, you are free to add anything you like.
And for the most important part - attaching the purchase order - scroll down to “Attachments” and click the “Edit” button.
Give it a name and ensure it ends in .pdf.Pro tip: Use the “Doc number” element to make it unique.
Then, map the “Body” element outputted by the “Quickbooks > Make an API call” module to the “Data” field.
Good job!
The purchase orders will be created and emailed exactly how you expect. There’s one more step needed to complete this automation. Let’s take a look.
Step 8: Updating the “Order” field
If you were wondering about the “Order” field (which we’ve seen in the formulas provided earlier), we are going to explain it right now.
This field on the table is very important because it prevents the automation from sending purchase orders that have already been sent whenever the automation executes.
If the field is ticked, no orders will be created for the corresponding item.
After the purchase order for an item has been emailed, the scenario must update the order field for that item.
In other words, you need to untick this field for that item in Airtable when the items are received in your shop.
To accomplish this, add the “Airtable > Search Records” module to your scenario.
Use the formula to search for the item that has just been ordered from the vendor. Map the “Vendor ID” as shown in the screenshot below the formula.
OR((AND(({Quantity} < {Maximum Quantity for item} *0.5), ({Ordered} = 0), ({Rotation} = 'High'), ({Quickbooks Vendor ID} = ""))),(AND(({Quantity} < {Maximum Quantity for item} *0.2), ({Ordered} = 0), ({Rotation} = 'Medium'), ({Quickbooks Vendor ID} = ""))))
And to conclude, add the “Airtable > Update a Record” module to your scenario.
In the config box, map the “ID” element from the previous “Search Records” module to the “Record ID” field to let the module know which record to update.
Scroll all the way to the bottom and select “Yes” under “Ordered”.
And that’s it! Now, let’s make sure the scenario works as intended.
Step 9: Testing the scenario
After you save the scenario, go to the Airtable table and change the quantity of a few items to a number that’s below the defined ordering quantity level.
For example, if you tagged an item as “High” rotation (meaning that a purchase order will be created when inventory for that item drops below 50%) and the inventory level for that item is say 100, change the quantity for that item to 20.
Next, change the email address in the Gmail module to yours, so that the vendor doesn’t receive the test purchase order.
Be sure to change it back once the test is completed!
If your scenario is set up correctly the execution should look like this:
To conclude, don’t forget to schedule the scenario to execute as often as you feel is necessary (i.e. once a day, twice a day, and so on).
And that, friends, is how we automatically send purchase orders to vendors once inventory levels drop below a certain threshold.
What’s next?
In the final part of this series, we will show you how to watch for invoices from vendors, and send notifications to your finance team (or whoever you need to).
Quick note: You probably noticed on the purchase orders that there was no electronic signature and date. This is actually something Quickbooks has been working on for a while.
If you wish to include those, check out this thread on their support forum: How can i add signature and date on the purchase order?
Finally, as we get closer to the finish line, it’s time to consider what you’ll be doing with your newly earned competitive advantage. In the end, that is what automation allows for.
Happy automating!
