Fishbowl SQL Query for Inventory by Location

How to query inventory by location

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

The query

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;

Safety and performance

Common 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.