Israel Lopez Consulting
Production · asked in Claude

“Can we build 40 batches of chocolate chip cookies (BB2005) by Friday? What's short?”

Yes on materials. Water isn't stocked and Main has no butter, so it shows what to transfer and what else is competing for it.

Fishbowl MCP connectedin Claude
Can we build 40 batches of chocolate chip cookies (BB2005) by Friday? What's short?
✓Checked your Fishbowl data · 2 read-only queries · 0.4 s

Yes on materials, with two things to resolve. Every ingredient for 40 batches of BB2005 is covered by available stock company-wide, except the items below.

IssueNeedStatus
Water13.2 fl ozZero on hand anywhere. It's set up as an Inventory part, so Fishbowl will treat it as short and the pick won't fully fill until stock is adjusted in.
Butter (BP203) at Main320 oz (20 lbs)Main has none. LA, MI and SLC each have 3,453 oz, so this only matters if you build at Main.

Everything else has plenty of room. The tightest ingredients are still well clear:

IngredientNeed for 40Available (all sites)Batches covered
Chocolate Chips (CR403)40 lbs349.5 lbs349
All Purpose Flour (B201)60 lbs591 lbs394
Chopped Walnuts (H902)20 lbs247.5 lbs495
White egg (BP204)80 ea1,455 ea727
Brown Sugar (C502)20 lbs481.5 lbs963

By site, LA and MI can each build all 40 from their own stock (apart from water). Main can too once butter is transferred in; its walnuts are the closest call at 25 lbs against 20 needed. SLC can't, since it only holds butter.

Two caveats:

  • This checks materials only. I didn't look at labor, oven capacity, or the schedule for the week, so "by Friday" depends on those.
  • Available stock already nets out current allocations, but not Entered manufacture orders that haven't been issued yet, which could be competing for the same ingredients.

Which site is the build planned for? I can check open manufacture orders there to confirm nothing else is competing for the butter or walnuts.

Main. Is anything else competing for the butter or walnuts there?
✓Checked your Fishbowl data · 3 read-only queries · 0.7 s

Two open manufacture orders at Main want the same ingredients, but they're small. Walnuts still cover everything with 2 lbs to spare, and the butter transfer needs to grow by about 3 lbs.

MOStatusScheduledBuildsButterWalnuts
45EnteredJul 181 × BB2005 Chocolate Chip Batch8 oz0.5 lb
46EnteredAug 81 × BB2003 Oatmeal Chocolate Chip Batch, 1 × FG2200 Sugar Cookie Batch40.5 oz2.5 lbs
Total competing48.5 oz (about 3 lbs)3 lbs

What that does to your 40 batches at Main:

IngredientOn hand at MainYour 40 batchesOther MOsResult
Walnuts (H902)25 lbs20 lbs3 lbs2 lbs left over
Butter (BP203)0320 oz48.5 ozTransfer in 368.5 oz (about 23 lbs) to cover all three

A few things worth knowing:

  • Both MOs are past their scheduled dates and still Entered, so neither has been issued and nothing is allocated yet. Whichever order gets issued and picked first takes the stock.
  • MO 45 is itself one batch of BB2005. If it's meant to be part of your 40, the real competition is only MO 46.
  • No open sales orders are selling butter or walnuts directly, and nothing is on order for either at Main, so the butter has to come from LA, MI or SLC (3,453 oz each).

I didn't check for transfer orders already moving butter toward Main.

A real 67-second session on our demo Fishbowl, replayed faster.

Behind the scenes

What your admin sees in the query log for this conversation.

5read-only queries
1.2 stotal query time
0changes to your data
0rows stored

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

Every query is checked before it runs: reads only, no system tables or password fields, and a row cap on every result. The question and the SQL are logged for your admins. The data itself is not stored.

More questions

What else your team could ask

“What does one batch of each cookie cost us to make?”

Materials cost per batch and per cookie for every recipe, from $2.23 to $72.64, and butter is what drives it.

Watch the answer →

“Which manufacturing orders are open, and what do they still need?”

Three manufacturing orders are open: 45, 46 and 49.

Watch the answer →

“what were my top product sales, and who bought them?”

Three cookies make up about 77% of your $29,544.54 in product sales, and Allen's Groceries is the main buyer of all three.

Watch the answer →

See all questions →

Ask this about your own Fishbowl

We'll set up ILC MCP on your Fishbowl and build the queries your team needs.