# What has Allen's Groceries ordered from us, and how often?

> A real Claude session on a Fishbowl database (ILC's demo company, sample data), connected through ILC MCP for Fishbowl. The answer below is what the AI said; the queries are what it ran. Page: https://israellopezconsulting.com/fishbowl-mcp/questions/customer-history

## Question

What has Allen's Groceries ordered from us, and how often?

### Answer

Allen's Groceries (customer #54) has 5 sales orders on record, all issued between July 10 and August 30, 2026: four sales and one credit return, netting to $17,638.80. There is no regular cadence: three came in the week of July 10–15, then nothing until August 30, and nothing since.

| SO | Issued | Status | Lines | Total |
|---|---|---|---|---|
| 1000 | Jul 10 | Fulfilled | 12 Sugar Cookie (BB2001), 24 Snickerdoodle (BB2004), 12 Chocolate Chip (BB2005) | $15,936.00 |
| 1017 | Jul 10 | Closed Short | 20 Chocolate Chip (BB2005), 240 fl oz Fresh Lemon Juice (P701, only 67 shipped), 320 Strawberry Lemonade 32oz (PB3003) | $165.30 |
| 1040 | Jul 15 | Fulfilled | 100 oz Salt (B204), 100 lbs Butter (BP203) | $1,510.00 |
| 1057 | Aug 30 | Fulfilled | Credit return of 1 Sugar Cookie (BB2001) | -$2.50 |
| 1024 | Aug 30 | Issued (open) | 5 lbs Baking soda (B202), nothing shipped yet | $30.00 |

- **Repeat items:** Chocolate Chip is the only product they have ordered more than once (12 on SO 1000, 20 on SO 1017). Everything else is a one-off.
- **Pricing looks inconsistent:** SO 1000 prices cookies at $250–$378 each, while SO 1017 has Chocolate Chip at $1.50 and the Sugar Cookie credit was $2.50. SO 1000 is about 90% of this customer's total, so check whether those were case or batch prices entered against an "ea" unit.
- **Dates are off on two orders:** SO 1000 and SO 1040 show completion dates (April 11 and January 8) earlier than their issue dates, so I used issue dates for the timeline.

Dates are as stored in Fishbowl, on an order (issued) basis rather than shipped.

## Behind the scenes

Every step the AI took, as recorded in the query log. All queries are read-only and row-capped.

1. Checked the saved queries — 6 saved queries
2. Read the Fishbowl notes index — 13 topic notes
3. Ran a read-only query: Find the customer record for Allen's Groceries and its sales order count, to report what they have ordered and how often — 1 row, 222 ms

   ```sql
   SELECT c.id, c.name, c.number, c.activeFlag + 0 AS active, COUNT(so.id) AS soCount, MIN(so.dateIssued) AS firstIssued, MAX(so.dateIssued) AS lastIssued
   FROM customer AS c
   LEFT JOIN so ON so.customerId = c.id
   WHERE c.name LIKE @pat
   GROUP BY c.id, c.name, c.number, c.activeFlag
   LIMIT 101
   ```

4. Read Fishbowl notes (SALES.md)
5. Ran a read-only query: List every sales order and line item for customer Allen's Groceries to report what they ordered and how often — 10 rows, 239 ms

   ```sql
   SELECT so.num AS soNum, ss.name AS soStatus, so.dateCreated, so.dateIssued, so.dateCompleted, so.totalPrice, so.customerPO, si.soLineItem, sit.name AS lineType, si.productNum, si.description, si.qtyOrdered, si.qtyFulfilled, u.code AS uom, si.unitPrice, si.totalPrice AS lineTotal, sis.name AS lineStatus
   FROM so
   JOIN sostatus AS ss ON ss.id = so.statusId
   LEFT JOIN soitem AS si ON si.soId = so.id
   LEFT JOIN soitemtype AS sit ON sit.id = si.typeId
   LEFT JOIN soitemstatus AS sis ON sis.id = si.statusId
   LEFT JOIN uom AS u ON u.id = si.uomId
   WHERE so.customerId = @cid
   ORDER BY so.dateIssued, so.num, si.soLineItem
   LIMIT 501
   ```

Learn more: https://israellopezconsulting.com/fishbowl-mcp · More examples: https://israellopezconsulting.com/fishbowl-mcp/questions