dataexport
26 rows
This data as json, CSV (advanced)
Suggested facets: description, lastChangedUserId, dateCreated (date), dateLastModified (date)
| id ▼ | dataQuery | dateCreated | dateLastModified | description | lastChangedUserId | name |
|---|---|---|---|---|---|---|
| 5 | Select SO.*, MO.* From SO Inner Join MO on SO.Num = MO.Num Where SO.billToName like '%Rockwell%' ; | 2025-03-05 16:11:17.296000 | 2025-03-05 16:11:17.296000 | 46 | SO and MO Query | |
| 8 | Select bomitem.description, part.num, part.description, bomitem.quantity, bom.num, bom.activeFlag, bom.description From bomitem Inner Join part on bomitem.partid = part.id Inner Join bom on bomitem.bomid = bom.id where part.num like '10112' And bom.activeflag = 1; | 2025-03-26 12:44:30.350000 | 2025-10-29 18:46:39.889000 | Enter the part number on line 12 as 'XXX' as the part you wish to see a list of boms where it is used and then export. Only returns active BOMs | 46 | Where used |
| 9 | Select AccountID, name From customer; | 2025-04-04 13:00:42.527000 | 2025-04-04 13:58:58.143000 | 46 | Customer List with Account Number | |
| 10 | SELECT partnum, unitcost, MAX(datelastfulfillment) AS last_fulfillment_date FROM POitem GROUP BY partnum Order by partnum; | 2025-04-04 17:28:54.806000 | 2025-04-04 17:44:45.716000 | Shows the last purchase price of a component | 46 | Last Purchase Price |
| 11 | Select * From PO where customfields like '%D%' | 2025-05-20 13:15:04.686000 | 2025-05-28 12:37:33.723000 | 46 | NPI PO Report | |
| 12 | Select * From SO where customfields like '%D%' | 2025-05-28 12:38:30.924000 | 2025-05-28 12:38:30.924000 | 46 | NPI SO Report | |
| 13 | Select * From BOM where URL Like "F%" | 2025-05-28 12:41:03.545000 | 2025-05-28 12:44:26.052000 | 46 | URL | |
| 14 | select So.num, SOitem.soLineItem, so.billToName, so.customerpo, so.salesman, soitem.productnum, soitem.description, soitem.qtyordered, soitem.qtyfulfilled, soitem.unitprice, soitem.totalPrice, so.dateCreated, so.dateissued, soitem.dateScheduledFulfillment from so join soitem on so.id = soitem.soid join soitemstatus on soitem.statusId = soitemstatus.id where soitemstatus.name = "entered" | 2025-09-30 14:02:18.194000 | 2025-09-30 14:02:18.194000 | 46 | Production Schedule | |
| 15 | select SO.num, soitem.soLineItem, SO.customerContact, soitem.customerPartNum, soitem.productNum, soitem.description, soitem.qtytofulfill, so.dateCreated, so.dateIssued, soitem.dateScheduledFulfillment, sostatus.name, bom.activeflag from so join soitem on so.id = soitem.soid join Bom on soitem.productnum = bom.num Join sostatus on so.statusid = sostatus.id Where SO.statusId in (10,20) and bom.activeflag = 0 order by so.datecreated desc | 2025-10-15 14:17:30.379000 | 2025-10-22 17:22:36.193000 | Returns all open orders whre the BOM is not active | 46 | Document Demand Report |
| 16 | SELECT p.num AS `Part Number`, p.description AS `Part Description`, p.abcCode AS `ABC Code`, /* Next FIFO Cost = company-wide next FIFO from costlayer */ ( SELECT (cl.orgTotalCost / NULLIF(cl.orgQty, 0)) AS fifo_unit_cost FROM costlayer cl JOIN costlayerstatus cls ON cls.id = cl.statusId WHERE cl.partId = p.id AND cls.name = 'Open' AND cl.qty > 0 AND cl.orgQty <> 0 ORDER BY COALESCE( /* earliest inventory transaction date tied to the layer (if any) */ ( SELECT MIN(il.eventDate) FROM inventorylogtocostlayer ilt JOIN inventorylog il ON il.id = ilt.inventoryLogId WHERE ilt.costLayerId = cl.id ), cl.dateCreated ) ASC, cl.id ASC LIMIT 1 ) AS `Next FIFO Cost`, /* Last Purchase Price = last received PO unit cost, else last PO-issued line unit cost */ COALESCE( ( SELECT poi.unitCost FROM receiptitem ri JOIN receipt r ON r.id = ri.receiptId JOIN poitem poi ON poi.id = ri.poItemId WHERE ri.partId = p.id AND r.poId IS NOT NULL AND ri.dateReceived IS NOT NULL AND poi.unitCost IS NOT NULL ORDER BY ri.dateReceived DESC, ri.id DESC LIMIT 1 ), ( SELECT poi.unitCost FROM poitem poi JOIN po ON po.id = poi.poId WHERE poi.partId = p.id AND poi.unitCost IS NOT NULL ORDER BY COALESCE(po.dateIssued, po.dateCreated) DESC, poi.id DESC LIMIT 1 ) ) AS `Last Purchase Price per Unit` FROM part p ORDER BY p.num | 2026-02-18 09:26:28.566000 | 2026-02-18 09:47:17.381000 | Query to list Next FIFO Cast and compare to Last purchase price - not working yet | 17 | Not working - Query Next FIFO and Last PP |
| 17 | SELECT p.num AS PartNum, t.id AS TagId, t.num AS TagNum, t.qty, t.qtyCommitted, (t.qty - IFNULL(t.qtyCommitted,0)) AS AvailableQty FROM part p JOIN tag t ON t.partId = p.id WHERE p.num = '16204-17' AND t.locationId = ( SELECT l.id FROM location l JOIN locationgroup lg ON lg.id = l.locationGroupId WHERE CONCAT(lg.name,'-',l.name) = 'Main-ISI' OR l.name = 'Main-ISI' LIMIT 1 ) ORDER BY t.dateCreated, t.num; | 2026-02-18 09:28:47.340000 | 2026-02-18 09:28:47.340000 | returns the location.id plus a “DisplayName” in the same LG-LOC format you see in Fishbowl | 17 | Real Loc ID |
| 18 | SELECT p.num AS PartNum, CONCAT(lg.name,'-',l.name) AS DisplayLocation, t.num AS TagNum, t.qty, t.qtyCommitted, (t.qty - IFNULL(t.qtyCommitted,0)) AS AvailableQty, ri.dateReceived AS ReceiptDate, poi.unitCost AS PoUnitCost, il_first.eventDate AS FirstInvEventDate, il_first.cost AS InvLogCost, COALESCE(poi.unitCost, il_first.cost) AS ChosenCost FROM part p JOIN tag t ON t.partId = p.id JOIN location l ON l.id = t.locationId JOIN locationgroup lg ON lg.id = l.locationGroupId LEFT JOIN receiptitem ri ON ri.tagId = t.id LEFT JOIN poitem poi ON poi.id = ri.poItemId LEFT JOIN ( SELECT endTagNum, endLocationId, partId, cost, eventDate FROM ( SELECT il.endTagNum, il.endLocationId, il.partId, il.cost, il.eventDate, ROW_NUMBER() OVER ( PARTITION BY il.endTagNum, il.endLocationId ORDER BY il.eventDate ASC, il.id ASC ) AS rn FROM inventorylog il WHERE il.changeQty > 0 AND il.endTagNum IS NOT NULL ) z WHERE z.rn = 1 ) il_first ON il_first.endTagNum = t.num AND il_first.endLocationId = t.locationId AND il_first.partId = t.partId WHERE p.num = '16204-17' AND (CONCAT(lg.name,'-',l.name) = 'Main-ISI' OR l.name = 'Main-ISI') AND t.qty > 0 ORDER BY COALESCE(ri.dateReceived, il_first.eventDate, t.dateCreated) ASC, t.num ASC; | 2026-02-18 09:31:33.105000 | 2026-02-18 09:31:33.105000 | 17 | Return inventory location by PN | |
| 19 | SELECT p.num AS `Part Number`, p.description AS `Part Description`, pt.name AS `Inventory Type`, /* Last PO Purchase Price: 1) last RECEIVED PO unit cost (preferred) 2) else last PO-issued/created line unit cost (fallback) If neither exists => 'NoPOHist' */ IFNULL( CAST( COALESCE( ( SELECT poi.unitCost FROM receiptitem ri JOIN receipt r ON r.id = ri.receiptId JOIN poitem poi ON poi.id = ri.poItemId WHERE ri.partId = p.id AND r.poId IS NOT NULL AND ri.dateReceived IS NOT NULL AND poi.unitCost IS NOT NULL ORDER BY ri.dateReceived DESC, ri.id DESC LIMIT 1 ), ( SELECT poi.unitCost FROM poitem poi JOIN po ON po.id = poi.poId WHERE poi.partId = p.id AND poi.unitCost IS NOT NULL ORDER BY COALESCE(po.dateIssued, po.dateCreated) DESC, poi.id DESC LIMIT 1 ) ) AS CHAR ), 'NoPOHist' ) AS `Last PO Purchase Price`, /* Last Purchase Price Date: 1) receiptitem.dateReceived for last received PO 2) else PO issue/create date for last PO line If neither exists => 'NoPOHist' */ IFNULL( CAST( DATE( COALESCE( ( SELECT ri.dateReceived FROM receiptitem ri JOIN receipt r ON r.id = ri.receiptId JOIN poitem poi ON poi.id = ri.poItemId WHERE ri.partId = p.id AND r.poId IS NOT NULL AND ri.dateReceived IS NOT NULL AND poi.unitCost IS NOT NULL ORDER BY ri.dateReceived DESC, ri.id DESC LIMIT 1 ), ( SELECT COALESCE(po.dateIssued, po.dateCreated) FROM poitem poi JOIN po ON po.… | 2026-02-18 10:12:09.009000 | 2026-02-18 10:20:10.099000 | Last PO Purchase Price (most recent received PO unit cost; if none, falls back to most recent PO line unit cost). If no PO history exists at all, it returns NoPOHist | 17 | Last PO Purch Price Query |
| 20 | SELECT lg.name AS `Location Group`, p.num AS `Part Number`, p.description AS `Part Description`, /* Reorder levels (will be NULL when No ROP/OUL is true) */ pr.reorderPoint AS `Reorder Point`, pr.orderUpToLevel AS `Order Up To`, /* On Hand per Forecast definition: OnHand subtracts Not Available */ (COALESCE(qi.qtyOnHand, 0) - COALESCE(qi.qtyNotAvailable, 0)) AS `On Hand`, -- [1](https://help.fishbowlinventory.com/resource/advancedreports)[4](https://fishbowlhelp.com/files/database/tables/qtyinventory.html) /* On Order = totals across sources (PO + SO + TO + MO) */ (COALESCE(qi.qtyOnOrderPO, 0) + COALESCE(qi.qtyOnOrderSO, 0) + COALESCE(qi.qtyOnOrderTO, 0) + COALESCE(qi.qtyOnOrderMO, 0) ) AS `On Order`, -- [4](https://fishbowlhelp.com/files/database/tables/qtyinventory.html) /* Allocated = totals across sources (PO + SO + TO + MO) */ (COALESCE(qi.qtyAllocatedPO, 0) + COALESCE(qi.qtyAllocatedSO, 0) + COALESCE(qi.qtyAllocatedTO, 0) + COALESCE(qi.qtyAllocatedMO, 0) ) AS `Allocated`, -- [4](https://fishbowlhelp.com/files/database/tables/qtyinventory.html) /* Shortage Qty (Include No ROP/OUL): Needed = OrderUpTo - OnOrder - OnHand + Allocated Shortage = max(Needed, 0) For No ROP/OUL parts, OrderUpTo is treated as 0 via COALESCE. */ GREATEST( ( COALESCE(pr.orderUpToLevel, 0) - (COALESCE(qi.qtyOnOrderPO, 0) + COALESCE(qi.qtyOnOrderSO, 0) + COALESCE(qi.qtyOnOrderTO, 0) + COALESCE(qi.qtyOnOrderMO, 0) ) - (COALESCE(qi.qtyOnHand, 0) - COALESCE(qi.qtyNotAvailable, 0)) + (COALESCE(qi.qtyAllocatedPO, 0) + COALESCE(qi.qtyAllocatedSO, 0) + COALESCE(qi.qtyAllocatedTO, 0) + COALESCE(qi.qtyAllocatedMO, 0) ) ), 0 ) AS `Shortage Qty` -- [1](https://help.fishbowlinventory.com/resource/advancedreports) FROM qtyinventory qi JOIN part p ON p.id = qi.partId JOIN locationgroup lg ON lg.id = qi.locationGr… | 2026-02-24 16:00:52.049000 | 2026-02-24 16:00:52.049000 | Below is the updated query (NO BOM parts only) with Shortage Qty and No ROP/OUL included. | 17 | Shortage Report |
| 21 | Select part.num, part.description, bomitem.stage From bomitem Inner Join part on bomitem.partid = part.id where bomitem.stage = false and part.num not like "labor%" and part.num not like "ab%" and part.num not like "d%" and part.num not like "0%" and part.num not like "g%" and part.num not like "t%" and part.num not like "serv%" and part.num not like "b%" and part.num not like "f%" and part.num not like "rg%" and part.num not like "nre%" group by num | 2026-02-25 13:51:43.910000 | 2026-02-25 14:22:36.747000 | 46 | Purchased Part | |
| 22 | Select part.num, part.description, bomitem.stage From bomitem Inner Join part on bomitem.partid = part.id where bomitem.stage = true and part.description like "%label%" group by num | 2026-02-25 15:30:56.525000 | 2026-02-25 15:35:18.540000 | 46 | Labels to be removed from BOM | |
| 23 | Select part.num, part.description, bom.num, bom.activeFlag, bom.description From bomitem Inner Join part on bomitem.partid = part.id Inner Join bom on bomitem.bomid = bom.id where part.description like "%label%" and bomitem.stage = true | 2026-02-25 16:03:27.905000 | 2026-02-25 16:26:22.608000 | 46 | MasterLabelList | |
| 25 | Select part.num, part.description, bom.num, bom.activeFlag, bom.description From bomitem Inner Join part on bomitem.partid = part.id Inner Join bom on bomitem.bomid = bom.id where part.description like "%label%" and bom.num not like "0%" and bom.num not like "ab%" | 2026-02-25 16:32:00.638000 | 2026-02-25 16:34:24.039000 | 46 | Label BOMs | |
| 26 | SELECT pi.description AS "Part Description", pi.partnum AS "Part Number", v.name AS "Vendor", p.datecreated AS "Date Entered", p.id AS "PO_ID", p.buyer AS "Buyer" FROM po p JOIN poitem pi ON pi.poid = p.id JOIN vendor v ON p.vendorid = v.id WHERE datecreated > '2026-04-01' ORDER BY p.datecreated; | 2026-04-14 08:56:23.852000 | 2026-04-14 08:59:07.166000 | Change the date on line 5 to retrieve POs entered after that date. | 51 | Searching for POs after a specific date |
| 27 | SELECT p_comp.num AS component_part_number, p_comp.description AS component_description, u.name AS uom, b.num AS bom_number, m.num AS mo_number, w.num AS wo_number, m.dateScheduled AS mo_scheduled_date, SUM( (mi.qtyToFulfill - mi.qtyFulfilled) * bi.quantity ) AS total_demand, qt.QTYONHAND AS qty_on_hand, qt.QTYONHAND - SUM( (mi.qtyToFulfill - mi.qtyFulfilled) * bi.quantity ) AS net_available, qt.QTYONORDER AS qty_on_order, CASE WHEN qt.QTYONHAND < SUM( (mi.qtyToFulfill - mi.qtyFulfilled) * bi.quantity ) THEN 'SHORT' ELSE '' END AS status FROM mo m JOIN moitem mi ON mi.moId = m.id JOIN part p_mo ON p_mo.id = mi.partId JOIN bom b ON b.num = p_mo.num JOIN bomitem bi ON bi.bomid = b.id JOIN part p_comp ON p_comp.id = bi.partid JOIN parttype pt ON pt.id = p_comp.typeid JOIN uom u ON u.id = p_comp.uomid LEFT JOIN qtyinventorytotals qt ON qt.PARTID = p_comp.id LEFT JOIN wo w ON w.moItemId = mi.id WHERE m.statusId IN (10, 20, 50) AND mi.statusId NOT IN (60, 70, 80) AND m.dateScheduled <= '2026-05-31' AND mi.partId IS NOT NULL AND p_mo.activeFlag = 1 AND p_comp.activeFlag = 1 AND pt.id NOT IN (20, 21, 22, 60) GROUP BY p_comp.num, p_comp.… | 2026-04-14 17:09:10.783000 | 2026-04-14 17:09:10.783000 | Dataset to determine demand over a given period of time | 51 | Demand |
| 28 | SELECT l1.finished_good, l1.finished_good_description, l1.component_part_number, l1.component_description, l1.component_uom, l1.qty_per_unit, l1.make_buy, l1.qty_on_hand, l1.qty_on_order, 'Level 1' AS bom_level FROM ( SELECT b.num AS finished_good, p_fg.description AS finished_good_description, p_comp.num AS component_part_number, p_comp.description AS component_description, u_part.name AS component_uom, CASE WHEN bi.uomId = p_comp.uomId THEN bi.quantity ELSE bi.quantity * ( SELECT uc.multiply / uc.factor FROM uomconversion uc WHERE uc.fromUomId = bi.uomId AND uc.toUomId = p_comp.uomId LIMIT 1 ) END AS qty_per_unit, JSON_UNQUOTE(JSON_EXTRACT(p_comp.customFields, '$.\"42\".value')) AS make_buy, MAX(qt.QTYONHAND) AS qty_on_hand, MAX(qt.QTYONORDER) AS qty_on_order FROM bom b JOIN part p_fg ON p_fg.num = b.num JOIN bomitem bi ON bi.bomid = b.id JOIN part p_comp ON p_comp.id = bi.partid JOIN parttype pt ON pt.id = p_comp.typeid JOIN uom u_part ON u_part.id = p_comp.uomId LEFT JOIN qtyinventorytotals qt ON qt.PARTID = p_comp.id WHERE b.num IN ('06330','04777','06463','06464','06465','06476','05899', '06495','05828','05790','16645','06470','16646','06426','06355', '06414','03606','03612','03611','03839','01195','06062','AB1069', '06483','06490','06480','05930','04220','06251','04535','06500', '06446','05931','00826','AB1331','AB2357','AB1089','AB1170','05929', … | 2026-04-20 14:32:06.897000 | 2026-04-20 14:32:06.897000 | Change assembly numbers whereever b.num in is used to get data | 51 | Part_Data_Based_On_Assemblies |
| 29 | SELECT table_name, column_name FROM information_schema.columns WHERE table_schema = DATABASE() ORDER BY table_name, ordinal_position | 2026-04-30 06:48:53.140000 | 2026-04-30 06:48:53.140000 | table data | 75 | tables |
| 30 | SELECT p.num AS part_num, p.description, u.name AS uom, SUM(wi.qtyUsed) AS total_qty_used, COUNT(DISTINCT wo.id) AS wo_count FROM woitem wi JOIN part p ON p.id = wi.partId JOIN wo ON wo.id = wi.woId JOIN uom u ON u.id = p.uomId WHERE ( p.num LIKE 'CLAD%' OR p.num LIKE 'LF5%' OR p.num LIKE 'F2%' ) AND wo.dateFinished >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) AND wo.dateFinished IS NOT NULL GROUP BY p.id, p.num, p.description, u.name ORDER BY total_qty_used DESC | 2026-05-05 11:25:54.966000 | 2026-05-05 11:25:54.966000 | Chnage interval on line 16 | 51 | GLASS Usage |
| 31 | SELECT wo.num AS wo_number, wo.dateFinished, p.num AS glass_part_num, p.description AS glass_description, u.name AS uom, wi.qtyUsed FROM woitem wi JOIN part p ON p.id = wi.partId JOIN wo ON wo.id = wi.woId JOIN uom u ON u.id = p.uomId WHERE ( p.num LIKE 'CLAD%' OR p.num LIKE 'LF5%' OR p.num LIKE 'F2%' ) AND wo.dateFinished >= '2025-01-01' AND wo.dateFinished <= '2026-03-31' AND wo.dateFinished IS NOT NULL ORDER BY p.num, wo.dateFinished | 2026-05-05 12:35:48.918000 | 2026-05-05 12:35:48.918000 | Glass usage between two dates | 51 | Glass_Usage_Date |
| 32 | SELECT p.num AS label_pn, p.description AS label_description, COUNT(DISTINCT w.id) AS total_work_orders, SUM(w.qtyOrdered) AS total_cables_built, MIN(w.dateFinished) AS earliest_wo, MAX(w.dateFinished) AS latest_wo, ROUND( SUM(w.qtyOrdered) / NULLIF(TIMESTAMPDIFF(MONTH, MIN(w.dateFinished), MAX(w.dateFinished)), 0) , 1) AS avg_monthly_usage, v.name AS vendor_name, vp.leadTime AS lead_time_days FROM woitem wi JOIN part p ON wi.partId = p.id JOIN wo w ON wi.woId = w.id LEFT JOIN vendorparts vp ON vp.partId = p.id AND vp.leadTime = ( SELECT MAX(vp2.leadTime) FROM vendorparts vp2 WHERE vp2.partId = p.id ) LEFT JOIN vendor v ON vp.vendorId = v.id WHERE p.num IN ('7176','7055','7061','7679','7220','7054','7060','7345','7680') AND w.dateFinished IS NOT NULL AND w.statusId = 40 GROUP BY p.num, p.description, v.name, vp.leadTime ORDER BY avg_monthly_usage DESC | 2026-05-05 17:01:21.707000 | 2026-05-05 17:01:21.707000 | 51 | Labels Run rate | |
| 33 | SELECT wo.num AS wo_number, wo.dateFinished, p.num AS glass_part_num, p.description AS glass_description, u.name AS uom, wi.qtyUsed, fp.num AS finished_part_num, fp.description AS finished_part_description, mi.qtyFulfilled AS qty_produced, fu.name AS finished_uom FROM woitem wi JOIN part p ON p.id = wi.partId JOIN wo ON wo.id = wi.woId JOIN uom u ON u.id = p.uomId JOIN womoview wmv ON wmv.WOID = wo.id JOIN moitem mi ON mi.moId = wmv.MOID AND mi.typeId = 10 JOIN part fp ON fp.id = mi.partId JOIN uom fu ON fu.id = mi.uomId WHERE ( p.num LIKE 'CLAD%' OR p.num LIKE 'LF5%' OR p.num LIKE 'F2%' ) AND wo.dateFinished >= '2026-01-01' AND wo.dateFinished <= '2026-04-30' AND wo.dateFinished IS NOT NULL ORDER BY p.num, wo.dateFinished | 2026-05-06 11:37:23.752000 | 2026-05-06 11:37:23.752000 | Change dates to get length of finished goods as well | 51 | Glass_Usage_With_FG_Length |
Advanced export
JSON shape: default, array, newline-delimited, object
CREATE TABLE "dataexport" (
"id" INTEGERNOT NULL ,
"dataQuery" TEXT ,
"dateCreated" TEXT DEFAULT NULL,
"dateLastModified" TEXT DEFAULT NULL,
"description" TEXT DEFAULT NULL,
"lastChangedUserId" INTEGERDEFAULT NULL,
"name" TEXT DEFAULT NULL,
PRIMARY KEY ("id")
);