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.
What people ask
- What's the difference between on hand, allocated, committed, and available inventory?”
- Show me current inventory with what's allocated and what's actually available”
- On-hand, committed, and available quantities for all parts”
The query
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
| Part # | On hand | Committed | Allocated | Available |
|---|---|---|---|---|
| BB-10042 | 8,310 | 800 | 1,200 | 7,110 |
| FRAME-200 | 640 | 40 | 90 | 550 |
| WHEEL-26 | 1,204 | 120 | 300 | 904 |
Notes & gotchas
- Available = On Hand − Allocated − Not Available + Drop Ship — Fishbowl's standard formula.
- Committed is a subset of Allocated (picked, ready to ship) — don't double-subtract it.
- typeId = 10 limits to inventory parts; activeFlag = 1 drops discontinued parts.
- qtyInventoryTotals pre-aggregates quantities across all locations per part.
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.
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;
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.
| Part # | On hand | Committed | Allocated | Available |
|---|---|---|---|---|
| BB-10042 | 8,310 | 800 | 1,200 | 7,110 |
| FRAME-200 | 640 | 40 | 90 | 550 |
| WHEEL-26 | 1,204 | 120 | 300 | 904 |
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.