Flow: Unfulfilled lines with available stock

[amberukr]

Hello, 

Flow Goal: We are trying to create a flow that will send an internal email with a compiled list of orders, older than 26hours, with unfulfilled lines but where the line item has inventory available. 

Current attempt:

The flow is set to trigger on a daily schedule. 

We then used 'Get Order Data' with the below query:

created_at:<='{{ scheduledAt | date_minus: "1 days" }}'
fulfillment_status:unfulfilled
AND financial_status:paid 
AND NOT status:canceled 
AND status:open
AND lineItems_item.product.leadTime.value:1 
AND lineItems_item.variant.inventoryQuantity:>0 
AND lineItems_item.product.totalInventory:>0
AND NOT tags_item:on_hold OR tags_item:delayed_delivery

I have screenshot of the full flow below:

 

We have been able to get the email to come through with a list of orders. However, the returned list doesn't meet our desired criteria:

- It contains orders where items have been removed (and therefore don't need to be sent) but these still must be marked as 'unfulfilled' in some way or another. We'd like to exclude these. 

- It also pulls through orders where the line item hasn't been fulfilled but there is also <=0 inventory on the variant, which is the opposite of what we want to see in this list. 

Theyre is clearly something wrong within our query. Although I am now starting to think that maybe this just cant be done because we are trying to pull order data and then drill down into that data to get variant inventory amounts. 

We have tried to achieve our flow goal using conditions instead of a query on the get order data, but to no avail. 

We have also tried to use the 'get fulfillment order data' but again we didn't get the desired result, in fact we got more orders. 

For additional context, we allow customers to order out of stock items, these are dispatched when the stock arrives, hense why we are trying to 'catch' lines which have stock but haven't been fulfilled.

We have a live stock feed coming from our warehouse system into shopify every 15 minutes.

All of our orders also import into our warehouse system, including ones with out of stock items. If the customer orders a mix of both in stock and out of stock items, theire order is split into multiple orders our warehouse system and the in stock goods are dispatched. 

Any help would be greatly appreciated. 

Thank you! 

[joshbeckman]

You can read about the supported query filters for orders here: https://shopify.dev/docs/api/admin-graphql/2024-07/queries/orders

The query you are using includes

AND lineItems_item.product.leadTime.value:1 AND lineItems_item.variant.inventoryQuantity:>0 AND lineItems_item.product.totalInventory:>0

Which is not supported in that query API - it doesn't support filtering/querying by line item data.

---

An alternative way of building this kind of automation on your shop would be to:

- create a workflow in Flow that runs on an hourly schedule to query for relevant orders (based on fulfillment status

  - use For Each to iterate over them

  - add a condition inside the For Each iterator to check the inventory/lead-time of line items

  - add a tag to the order if the condition passes

- modify this workflow to instead query orders with that tag

  - count those orders and send the email