Shopify Flow list all open orders containing an archived / deleted sku

[vanillamt]

I need to create a shopify flow such that:

- When a product is deleted or archived

- Search through all paid orders that are either unfulfilled or partially fulfilled that have the deleted sku as an unfulfilled line item

- send a slack message with a list of these orders to a slack channel

I have tried the following:

- created a flow with the trigger inventory item deleted

- used this advance query

{
orders(first: 100, query: "status:open fulfillment_status:(fulfilled OR partial) line_items.product_id:{{inventoryItemId}}") {
edges {
node {
id
name
createdAt
fulfillmentStatus
lineItems(first: 100) {
edges {
node {
id
title
quantity
}
}
}
}
}
}
}

- used the send slack message action with this message:

An product {{inventoryItemId}} has been deleted and the following orders are affected:

{% for getOrderData_item in getOrderData %}
{{getOrderData_item.name}}
{% endfor %}

{% for getOrderData_item in getOrderData %}
{{getOrderData_item.customer.email}}
{% endfor %}

- My output however excludes the order data, this is what I get

An product (shopify url) has been deleted and the following orders are affected:

Can anyone point me in the right direction to fix this please?

[paul_n]

This part of your query is not valid:

line_items.product_id:{{inventoryItemId}}

The available query fields are here:

https://shopify.dev/docs/api/admin-graphql/2023-01/objects/QueryRoot#connection-queryroot-orders 

You can query by sku got find order that match the lineIitems. I hightly recommend you do this query in a something like postman first to make sure you are getting what you expect. The API will currently return all results if you include invalid filters, so testing it up front helps a lot.

FWIW, I think this is only solvable by using queries.