All examples
Query · SQL

Inventory availability — allocated, committed, available

On-hand, allocated, committed, on-order, and the true available quantity for every part — using Fishbowl's own availability formula.

Type: Query Output: TableData: Demo sandbox

What people ask

The query

inventory-availability.sql
SELECT p.num AS part_num, p.description,
  COALESCE(it.totalOnHand,0)    AS qty_on_hand,
  COALESCE(c.total,0)           AS qty_committed,
  COALESCE(it.totalAllocated,0) AS qty_allocated,
  ROUND(COALESCE(it.totalOnHand,0) - COALESCE(it.totalAllocated,0)
      - COALESCE(it.totalNotAvailable,0) + COALESCE(it.totalDropship,0),2) AS qty_available
FROM part p
LEFT JOIN (SELECT partId, SUM(qtyOnHand) totalOnHand, SUM(qtyAllocated) totalAllocated,
             SUM(qtyNotAvailable) totalNotAvailable, SUM(qtyDropship) totalDropship
          FROM qtyInventoryTotals GROUP BY partId) it ON it.partId = p.id
LEFT JOIN (SELECT partId, SUM(qty) total FROM qtyCommitted GROUP BY partId) c ON c.partId = p.id
WHERE p.typeId = 10 AND p.activeFlag = 1
ORDER BY p.num;

Preview · demo data

3 rows · demo data
Part #On handCommittedAllocatedAvailable
BB-100428,3108001,2007,110
FRAME-2006404090550
WHEEL-261,204120300904

Notes & gotchas

A locked session — the real conversation that produced this report. Open it in the app to fork and continue.
What's the inventory availability — allocated, committed, on hand, and actually available — for all parts?
Agent Query session

Fishbowl computes availability as On Hand − Allocated − Not Available + Drop Ship. I'll pull the pre-aggregated qtyInventoryTotals (summed across locations) and join committed separately so you can see it, even though it's already inside allocated.

inventory-availability.sql
SELECT p.num AS part_num, p.description,
  COALESCE(it.totalOnHand,0)    AS qty_on_hand,
  COALESCE(c.total,0)           AS qty_committed,
  COALESCE(it.totalAllocated,0) AS qty_allocated,
  ROUND(COALESCE(it.totalOnHand,0) - COALESCE(it.totalAllocated,0)
      - COALESCE(it.totalNotAvailable,0) + COALESCE(it.totalDropship,0),2) AS qty_available
FROM part p
LEFT JOIN (SELECT partId, SUM(qtyOnHand) totalOnHand, SUM(qtyAllocated) totalAllocated,
             SUM(qtyNotAvailable) totalNotAvailable, SUM(qtyDropship) totalDropship
          FROM qtyInventoryTotals GROUP BY partId) it ON it.partId = p.id
LEFT JOIN (SELECT partId, SUM(qty) total FROM qtyCommitted GROUP BY partId) c ON c.partId = p.id
WHERE p.typeId = 10 AND p.activeFlag = 1
ORDER BY p.num;
Agent Query session

Here it is against demo data. Note committed is shown for reference only — the available math uses allocated, not committed, so we don't double-count.

preview · demo data
Part #On handCommittedAllocatedAvailable
BB-100428,3108001,2007,110
FRAME-2006404090550
WHEEL-261,204120300904

Want one like this for your Fishbowl?

Send a short brief — an ILC expert builds and tests it, and you get the file ready to import. The quote is free.

Related examples