# Fishbowl SQL Query for Inventory by Location

> Source: https://netray.co/erp-help/fishbowl-sql-report-inventory-by-location-tag-table
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: ERP Help

## Short answer

Fishbowl stores physical inventory as rows in the tag table, one per part per location per tracking combination. Join tag to part and location, group by part and location, and sum quantity to get on hand by location. Run it read-only through Fishbowl's reporting module or a read-only MySQL user, and verify column names against your version.

## Kind

how-to

## System

small-erp

## Erp

Fishbowl Inventory

## Module

Reporting / Database

## Query

Fishbowl SQL query inventory by location

## Alternate Queries

- Fishbowl tag table quantity on hand by location
- Fishbowl custom report SQL inventory
- Fishbowl query part quantity per location
- Fishbowl inventory report from database

## Applies To

Fishbowl Inventory with the MySQL database back end. Table and column names are stable across many releases but confirm in your version's schema before production use.

## Steps

### How to query inventory by location

**Items:**

- Decide where to run it: a custom report in Fishbowl's reporting module, or a read-only MySQL account connected to the Fishbowl database.
- Never use the main Fishbowl database login for ad hoc queries, and never run UPDATE or DELETE against inventory tables.
- Inspect the tag, part and location tables with DESCRIBE to confirm column names in your version.
- Join tag to part on the part id and to location on the location id.
- Group by part number and location name and sum the tag quantity.
- Compare the total with Fishbowl's own inventory report for a few parts to validate the query.
- Add filters for location group, part type or a minimum quantity as needed, and save the query for reuse.
- Schedule or export results rather than re-running heavy queries during busy picking hours.

## Sections

### How Fishbowl stores inventory

**Body:**

Fishbowl does not keep one quantity per part. It keeps tags: rows representing a quantity of a part in a location, with optional tracking values such as lot, serial number or revision. Summing tag quantities gives what is physically in a location. Totals per part come from summing across locations.

Fishbowl also records every movement in an inventory log, which explains how a quantity got where it is. When a tag total disagrees with a user's expectation, the log is where you find the receipt, move, pick or adjustment responsible.

### The query

**Body:**

The example below returns on-hand quantity by part and location. Table and column names reflect common Fishbowl schemas; treat them as a starting point and confirm them with DESCRIBE in your own database before relying on results.

**Code:**

SELECT p.num AS part_number,
       l.name AS location,
       SUM(t.qty) AS on_hand
FROM tag t
JOIN part p ON p.id = t.partId
JOIN location l ON l.id = t.locationId
GROUP BY p.num, l.name
HAVING SUM(t.qty) <> 0
ORDER BY p.num, l.name;

### Validating and extending it

**Body:**

Pick three parts and compare your result with Fishbowl's inventory quantity report. If they differ, check whether your query includes locations that Fishbowl treats differently, such as shipping, receiving or non-available locations, and whether committed quantity matters to the report you are matching.

To extend it, join the location group table to filter to pickable locations, add part type to separate stock from non-inventory items, or join recent inventory log rows to show the last movement date per location. Keep each addition tested against a known part.

### Safety and performance

**Body:**

Use a read-only database account, run on a copy or during quiet hours for large queries, and avoid selecting every column from large tables. Fishbowl's database is the live system of record; a long-running or locking query can slow warehouse operations.

Do not write to inventory tables directly. Fishbowl maintains quantities, costs and the accounting link through its own logic, and direct edits can produce stock that does not reconcile with the general ledger. Use inventory adjustment, move and cycle count features instead.

## Pitfalls

- Writing to the tag table or inventory log directly, which bypasses costing and accounting logic.
- Assuming column names without running DESCRIBE on your version.
- Using the application login for ad hoc queries instead of a read-only account.
- Forgetting that tracking values create several tag rows per part and location, then reading rows as duplicates.
- Comparing a physical on-hand total to a report that excludes certain location types.

## Ai Angle

A grounded assistant such as DataRay or ERPray can take a plain-language question about stock by location, generate the SQL against a read-only connection, show the query it ran and return the table, so users do not need to remember schema names. It does not write to the database. For a one-off known report, Fishbowl's built-in reports or a saved custom report may be all you need.

## Faq

### Which Fishbowl table holds quantity on hand?

**Answer:** Physical quantities live in the tag table, with one row per part, location and tracking combination. Summing tag quantity by part and location gives on hand. Confirm column names in your version with DESCRIBE before using the query in production.

### Can I run SQL directly against Fishbowl?

**Answer:** Yes, Fishbowl uses MySQL, and many customers run read-only queries or custom reports. Use a read-only account, avoid writes to inventory tables and run heavy queries off-hours so you do not slow warehouse operations.

### Why does my SQL total differ from the Fishbowl report?

**Answer:** Fishbowl reports may exclude certain location types, include committed or allocated quantities differently, or filter by part type. Align the filters first, then compare a few parts and check the inventory log for recent movements.

### Is it safe to adjust quantities with an UPDATE statement?

**Answer:** No. Fishbowl links inventory to costing and accounting through its own logic, so direct edits can leave stock that does not reconcile. Use inventory adjustments, moves or cycle counts inside Fishbowl instead.

## Search terms

Fishbowl SQL query inventory, Fishbowl tag table, Fishbowl custom report SQL, Fishbowl quantity on hand by location, Fishbowl database schema, Fishbowl MySQL read only, Fishbowl inventory log, Fishbowl inventory report

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json