The Tool Nobody Shows You in the Demo
Demos love dashboards. Nobody demos the Query Generator, because watching a man pick tables out of a list is not exciting television. But here is the thing about owners: you do not actually want a dashboard. You want answers, in a grid, that you can sort and send to somebody. The Query Generator is exactly that. You open it from Tools → Queries → Query Generator, pick your tables on the left, tick the fields you want, add your conditions, and Business One writes the query for you. Save it under a name you will recognize at 7 a.m., and from then on it lives in your Queries menu like it was always there. Everything below is built that way — point, click, save.
Recipe One: Who Owes Me Money, and How Late
This is the first query I build at every single client, because cash questions outrank everything. Tables: OINV for the invoice header. Pull DocNum, DocDate, DocDueDate, CardCode, CardName, DocTotal, and PaidToDate. Add a condition that DocStatus is open — that is the letter O in the field — and that DocDueDate is earlier than today. Add one calculated column the Generator lets you type in by hand: DocTotal minus PaidToDate, and call it Balance Due. Sort by DocDueDate, oldest first. What you get is your collections call list, in the order you should work it. I have watched owners print this on day one and recover more money in a week than the query took minutes to build.
Recipe Two: Dead Stock, Sitting There Quietly
Dead inventory is rent you pay on your own money. Tables: OITM for the item, OITW for the warehouse quantities. Pull the item code and name, the OnHand from the warehouse table, and the item cost. The trick is the condition: OnHand greater than zero, and then sort by how long it has been since the item moved. The Generator cannot hand you "last sold date" without a little help from a saved query against your invoice lines, and this is where owners usually call me — that part is honest work, not a checkbox. But even the simple version, everything in stock sorted by value sitting still, will find you the pallet in the corner that everybody forgot. Every warehouse has one. Most have six.
Recipe Three: Orders That Were Due Yesterday
Tables: ORDR for open sales orders. Pull DocNum, CardName, DocDate, DocDueDate, and DocTotal, with DocStatus open and DocDueDate in the past. That is your "why has this not shipped" list. Pair it with the matching purchase order recipe — same trick on OPOR, open status, due date passed — and you can see both sides of a late job: what you promised, and what you are still waiting on to keep the promise. Owners tell me this pair of queries replaced a Monday meeting. I believe them, because the meeting was mostly people reading these two lists out loud anyway.
Recipe Four: The Margin Leak Finder
This one uses the invoice lines, INV1, joined back to OINV for the dates. Pull the item, the quantity, the price you sold it at, and the Gross Buy Price — the cost Business One carried on the line. Add a column that divides the difference by the sale price, and you have margin by line. Condition it to the last ninety days and sort the worst margin to the top. What floats up will teach you something every time: the customer who gets a friendly price nobody approved, the item whose cost went up in March while the price list slept through it, the salesperson whose discount habit has a shape. You cannot fix a leak you have not seen. This query is how you see it.
Recipe Five: What Did This Vendor Charge Us
Tables: OPOR and POR1, purchase order header and lines. Filter on one CardCode, last two quarters, and pull the item, quantity, and unit price by date. What you are looking for is drift — the same item creeping up four percent at a time, small enough that nobody flags a single order, big enough to matter by December. Owners get negotiated hard in year one and drifted on in year three. This recipe is the difference between knowing your costs and remembering them fondly.
Two Habits That Make Queries Worth Keeping
First, use the parameter trick: when you type a condition, the Generator lets you insert [%0], [%1], and so on as placeholders, and then Business One asks you for the value every time the query runs. One query with a date parameter beats nine copies of the same query named by month. Second, name things like a person, not a programmer. "Who Owes Us — Overdue" will still make sense in a year. "QRY_AR_04_FINAL" is a cry for help from past you. I have inherited both kinds of query menus. Be kind to the next person. The next person is also you, in March, in a hurry.
Frequently Asked Questions
Do I need to know SQL to use the Query Generator?
No. The Generator builds the query from the tables and fields you pick, and shows you the result in a grid. If you later want to tweak the generated text by hand, you can — plenty of owners graduate to that — but nothing in these five recipes requires typing code.
Can I schedule these queries to run automatically?
Business One does not email you a query result on a schedule by itself. What owners actually do is save the query and run it as part of a Monday routine, or pair it with an alert on the conditions that matter. The saved query is the hard part. Running it takes thirty seconds.
Will these queries slow down my system?
These are read-only reports against your own data, and at the size of a typical small or mid-size company they run in seconds. If a query ever does run slow, that is information too — it usually means a table has grown wild, which is worth a conversation with your partner.
Where do saved queries live for my team?
Saved queries sit in the Queries menu, organized in categories you control, and user authorizations decide who can see and run each one. Set the money queries to the people who should see money. That part is worth five careful minutes.
