Ask your Fishbowl data questions in ChatGPT or Claude

Every step the AI took

1. 1
   Checked the saved queries
   6 saved queries
2. 2
   Read the Fishbowl notes index
   13 topic notes
3. 3
   Can we build 40 batches of chocolate chip cookies (BB2005) by Friday? What's short?

   Saved query bom_build_requirements, approved by your admin

   ✓ passed read-only checkrow cap applied

   Show the SQL

   SELECT b.num AS bomNum, p.num AS rawPart, p.description, bi.quantity AS qtyPerBuild, lu.code AS bomUom, @builds AS builds, bi.quantity * CASE WHEN bi.oneTimeItem = 1 THEN 1 ELSE @builds END AS qtyNeeded, ROUND(bi.quantity * CASE WHEN bi.oneTimeItem = 1 THEN 1 ELSE @builds END * CASE WHEN bi.uomId = p.uomId THEN 1 ELSE uc.multiply / uc.factor END, 4) AS qtyNeededStockUom, pu.code AS stockUom, ROUND(COALESCE(inv.onHand, 0), 4) AS qtyOnHand, ROUND(COALESCE(inv.available, 0), 4) AS qtyAvailable, ROUND(COALESCE(inv.onOrder, 0), 4) AS qtyOnOrder, ROUND(GREATEST(bi.quantity * CASE WHEN bi.oneTimeItem = 1 THEN 1 ELSE @builds END * CASE WHEN bi.uomId = p.uomId THEN 1 ELSE uc.multiply / uc.factor END - COALESCE(inv.available, 0), 0), 4) AS shortfall, CASE WHEN bi.oneTimeItem = 1 THEN NULL ELSE FLOOR(COALESCE(inv.available, 0) / NULLIF(bi.quantity * CASE WHEN bi.uomId = p.uomId THEN 1 ELSE uc.multiply / uc.factor END, 0)) END AS buildsCoveredByAvailable, bi.oneTimeItem + 0 AS oneTimeItem, bi.variableQty + 0 AS variableQty, bg.name AS optionGroup, bi.groupDefault + 0 AS isGroupDefault, sb.num AS subAssemblyBom
   FROM bom AS b
   JOIN bomitem AS bi ON bi.bomId = b.id AND bi.typeId = 20
   JOIN part AS p ON p.id = bi.partId
   JOIN uom AS pu ON pu.id = p.uomId
   LEFT JOIN uom AS lu ON lu.id = bi.uomId
   LEFT JOIN uomconversion AS uc ON uc.fromUomId = bi.uomId AND uc.toUomId = p.uomId
   LEFT JOIN bomitemgroup AS bg ON bg.id = bi.bomItemGroupId
   LEFT JOIN bom AS sb ON sb.id = bi.stageBomId AND bi.stage = 1
   LEFT JOIN (SELECT t.PARTID AS partId, SUM(t.QTYONHAND) AS onHand, SUM(t.QTYONHAND - t.QTYALLOCATED - t.QTYNOTAVAILABLE) AS available, SUM(t.QTYONORDER) AS onOrder
   FROM qtyinventorytotals AS t
   GROUP BY t.PARTID) AS inv ON inv.partId = p.id
   WHERE b.num = @bom_num AND (@part_num IS NULL OR p.num = @part_num)
   ORDER BY bi.sortIdConfig, bi.id
   LIMIT 101

   11 rows · 184 ms
4. 4
   Read Fishbowl notes · MANUFACTURING.md
5. 5
   Can we build 40 batches of BB2005 by Friday, what's short: check part type of Water and per-warehouse availability of each raw good

   Follow-up to saved query: per location group breakdown and part type

   ✓ passed read-only checkrow cap applied

   Show the SQL

   SELECT p.num, pt.name AS partType, lg.name AS locationGroup, ROUND(t.QTYONHAND, 2) AS onHand, ROUND(t.QTYONHAND - t.QTYALLOCATED - t.QTYNOTAVAILABLE, 2) AS available, u.code AS uom
   FROM part AS p
   JOIN parttype AS pt ON pt.id = p.typeId
   JOIN uom AS u ON u.id = p.uomId
   LEFT JOIN qtyinventorytotals AS t ON t.PARTID = p.id
   LEFT JOIN locationgroup AS lg ON lg.id = t.LOCATIONGROUPID
   WHERE p.num IN ('B201', 'BD301', 'BP203', 'C502', 'BP204', 'BD302', 'B202', 'Water', 'B204', 'H902', 'CR403')
   ORDER BY p.num, lg.name
   LIMIT 101

   33 rows · 250 ms
6. 6
   Is anything else competing for butter (BP203) or walnuts (H902) at Main: open manufacture orders consuming them

   ✓ passed read-only checkrow cap applied

   Show the SQL

   SELECT m.num AS moNum, ms.name AS moStatus, lg.name AS locationGroup, CAST(m.dateScheduled as CHAR) AS dateScheduled, pb.num AS bomNum, pmi.qtyToFulfill AS builds, p.num AS rawPart, mi.qtyToFulfill AS qtyNeeded, mi.qtyFulfilled AS qtyDone, u.code AS uom, mis.name AS lineStatus
   FROM mo AS m
   JOIN mostatus AS ms ON ms.id = m.statusId
   JOIN locationgroup AS lg ON lg.id = m.locationGroupId
   JOIN moitem AS mi ON mi.moId = m.id AND mi.typeId = 20
   JOIN moitemstatus AS mis ON mis.id = mi.statusId
   JOIN part AS p ON p.id = mi.partId
   LEFT JOIN uom AS u ON u.id = mi.uomId
   LEFT JOIN moitem AS pmi ON pmi.id = mi.parentId
   LEFT JOIN bom AS pb ON pb.id = pmi.bomId
   WHERE m.statusId IN (10, 20, 50) AND mi.statusId NOT IN (50, 60, 70) AND p.num IN ('BP203', 'H902')
   ORDER BY lg.name, m.dateScheduled, m.num
   LIMIT 101

   5 rows · 239 ms
7. 7
   Is anything else competing for butter (BP203) or walnuts (H902) at Main: allocation and on-order breakdown per warehouse

   Second query: inventory totals breakdown (allocated, on order) by location group

   ✓ passed read-only checkrow cap applied

   Show the SQL

   SELECT p.num, lg.name AS locationGroup, t.*
   FROM qtyinventorytotals AS t
   JOIN part AS p ON p.id = t.PARTID
   JOIN locationgroup AS lg ON lg.id = t.LOCATIONGROUPID
   WHERE p.num IN ('BP203', 'H902')
   ORDER BY p.num, lg.name
   LIMIT 101

   6 rows · 277 ms
8. 8
   Is anything else competing for butter (BP203) or walnuts (H902) at Main: open sales orders selling these parts directly

   Third query: open sales order lines for the two parts

   ✓ passed read-only checkrow cap applied

   Show the SQL

   SELECT 'SO' AS src, so.num AS orderNum, ss.name AS status, lg.name AS locationGroup, p.num AS part, si.qtyToFulfill AS qtyOrdered, si.qtyFulfilled AS qtyDone, u.code AS uom, CAST(si.dateScheduledFulfillment as CHAR) AS dateScheduled
   FROM soitem AS si
   JOIN so ON so.id = si.soId
   JOIN sostatus AS ss ON ss.id = so.statusId
   JOIN locationgroup AS lg ON lg.id = so.locationGroupId
   JOIN product AS pr ON pr.id = si.productId
   JOIN part AS p ON p.id = pr.partId
   LEFT JOIN uom AS u ON u.id = si.uomId
   WHERE so.statusId IN (10, 20, 25) AND si.qtyToFulfill > si.qtyFulfilled AND p.num IN ('BP203', 'H902')
   ORDER BY lg.name, so.num
   LIMIT 101

   0 rows · 203 ms