Automating a Manual Order Fulfillment Task

Here’s an example automation I made to aid a split-fulfillment model of a small online storefront. Normally, orders go directly to the warehouse to get shipped, but a few new items are only made on demand and shipped from a separate supplier. Here’s the automation that saved my day and makes sure this still happens even when I’m on vacation.

The Situation and Constraints

The online store had some constraints that I had to plan around. For example, I couldn’t set the few products (hats and coins in this case) to be delivered by API any differently than the drop shipped products. As well, the vendor that makes these few on-demand items preferred to be notified by email of new orders.

Luckily, my store can already send me an email for orders that include products I care about here. There’s one particular caveat, but it’s not hard to handle. Although the email only arrives if there is one of these special products in the order, the attachment lists all items — including items I don’t need to send to the special on-demand provider.

If any order placed includes products I defined in the store, I’ll get a single email for that order with a CSV attachment listing all the line items purchased in that order. For this triggered email, I use a subject line I can reference in Power Automate later.

The Flow in a nutshell

This flow is triggered by a new email in the “Inbox” folder with the subject “Special Product Order” and an attachment. It parses the attached CSV file row by row, extracts relevant information about hat or coin products, and adds a row to an Excel tracker sheet for each one. It then generates an email with order details for just those special products, and finally it moves the processed email to a different folder for safe keeping.

The Flow Details

There are some intermediate steps that assist with the flow. For example, at the top, I initialize a few text string variables and populate them with HTML that helps table formatting later.

Going in Loops

This flow uses two loops: first there is a “for each attachment” which is the outer loop. We’ll only ever see it execute once since every email has only a single attachment (although it would work fine if there were more).

The first action inside this loop is to check if the attachment is the correct file type (CSV) and ignore it in that unlikely case.

Next, there is the “for each row of the CSV”, which is the inner loop. This usually runs once or twice on most orders because the store here only sells a handful of products. It is during this inner loop that any products not including hat or coin in their name get ignored and skipped.

Any rows that do represent a hat or coin item in the CSV attachment, get added to a spreadsheet that tracks these special orders. Information from the original CSV is copied over like the customer info, and this spreadsheet is shared with the vendor that fulfills and ships the orders.

As the inner loop adds rows to the tracking Excel file, it also concatenates this data into an HTML table text so that later we can send a single email to the vendor which lists all items needing fulfilled on that order.

At the conclusion of the flow, once all loops are complete, we move the original email to a sub-folder so we know it’s been handled.

Meanwhile, all other items sold in the store continue to transmit by API to the original supplier that fulfills all regular orders.