Inventory Movements? GraphQL query?

[ChrisBradley]

Hey All,

is there an query where I can see all the products that that have had a movement with a period?

for example, I wanted to see products that have had fulfillments/returns/etc for yesterday

Thanks

Chris

[KyleG-Shopify]

Hey @ChrisBradley, good news since we last chatted. The shopifyqlQuery endpoint is now available in the Admin API (changelog). This means you can now run analytics queries through Graphql. For example:

{
  shopifyqlQuery(query: """
    FROM inventory_adjustment_history
    SHOW inventory_adjustment_change, inventory_adjustment_count
    GROUP BY product_variant_sku, day, reference_document_type
    HAVING inventory_adjustment_change != 0
    SINCE -1d
    UNTIL today
    ORDER BY day DESC
    LIMIT 100
  """) {
    tableData {
      columns { name dataType displayName }
      rows
    }
    parseErrors
  }
}

The reference_document_type field shows the movement type (Order, Return, etc.), so you can filter or group by that. Your app will need the read_reports scope.

Pro tip, sidekick in your admin can be a great help in building out some of these queries. Here’s a skill to get you started via Sidekick in your admin.

[ChrisBradley]

Dame

@KyleG-Shopify

{
  shopifyqlQuery(query: """
    FROM inventory_adjustment_history
    SHOW inventory_adjustment_change, inventory_adjustment_count
    GROUP BY product_variant_sku, day, reference_document_type
    HAVING inventory_adjustment_change != 0
    SINCE -1d
    UNTIL today
    ORDER BY day DESC
    LIMIT 100
  """) {
    tableData {
      columns { name dataType displayName }
      rows
    }
    parseErrors
  }
}

Thanks @KyleG-Shopify

Damn that is really useful. I must have missed this changelog over the christmas period.

have tested this out, and I was kind of assuming there would be a 3 lines within the report for a specific GID, but I have 10?

I kind of understand why there is a Purchase (to move inventory from Available to Committed), then the order fulfilled (from Commited to dispatched)

what I dont understand is why there is a None and they there are more that lineitems in the purchase?

GID is this gid://shopify/Order/6064403185819

[KyleG-Shopify]

Hey @ChrisBradley,

The extra rows are because the inventory is tracked across multiple states (Available, Committed, Unavailable, On hand, Incoming). Each state change is a separate row in the adjustment history.

The row with None for the SKU and variant ID is a possibly a variant that doesn’t have a SKU assigned.

If you want a cleaner view, you can filter by inventory_change_reason to show only the movement types you care about. Fulfillments for example:

FROM inventory_adjustment_history
SHOW inventory_adjustment_change
WHERE inventory_change_reason = 'Order Fulfilled'
SINCE -1d UNTIL today
GROUP BY product_variant_sku, day
ORDER BY day DESC
LIMIT 100

That’ll give you one row per SKU per reason per day.