Query builder

The Query builder lets you ask your LogisticaHQ database any question in SQL and see the answer at once: stock by bin, orders late by customer, adjustments by reason, picks per person, anything the data holds. It is built for people who know a little SQL. Everyone else can start from the ready-made examples and change a date or a code.

Open Setup > System > Query builder. The Query builder is for administrators only: it does not appear in anyone else's menu, and anyone else who opens its address sees Only administrators can use the Query builder.

The Query builder with the Stock on hand by client example in the editor: the Read-only badge and Examples and Clear buttons above it, the Tables list and parameters on the right, and the result below

It can only read

The Query builder runs one SELECT query at a time, and nothing else. Three separate safeguards make sure of it:

Before anything reaches the database, LogisticaHQ reads the query with SQL Server's own grammar and refuses anything that is not a single SELECT: INSERT, UPDATE, DELETE, MERGE, TRUNCATE, SELECT ... INTO, EXEC, CREATE, ALTER, DROP, GRANT, several statements in a row, and so on.
The query may only read your company's own tables. Other databases, SQL Server's system views and external data sources are refused.
Passwords, password hashes, API keys and webhook tokens cannot be read. The ConfigEntries (settings) and Webhooks tables are refused, as are the ApiKey, PasswordHash, LastPasswordHashes and SmallParcelBrokerApiKey columns. On Users and Clients, list the columns you need instead of using SELECT *. None of these appear in the Tables list or the suggestions.
The query runs with a read-only database login, inside a transaction that is always undone, whatever the query did.

The Read-only badge above the editor is a reminder. A refused query shows why below the editor:

DELETE FROM Products refused: Only SELECT queries can run here. Nothing can be added, changed or deleted from the Query builder.

Run a query

Type the query in the editor, or click Examples and choose one.
Click Execute, or press Ctrl+Enter in the editor.
The status beside the editor shows how many rows came back and how long it took, and the rows appear below.

The result of Stock on hand by client: 12 rows in 0.1 seconds, one row per client with its SKUs and quantity

The results work like every other list: search, filter per column, choose columns, and Export to Excel. The column headings are the names your query gives the columns, so use AS to name them the way you want them in the spreadsheet: SUM(il.Quantity) AS [On hand].

Examples

The Examples menu open at the Clients group: Stock on hand by client, One client's stock by product and bin, Open orders by client, Orders shipped by client

Click Examples for 22 ready-made queries. Choosing one replaces what is in the editor. Run it as it is, or change it first: the period, a warehouse, a SKU.

Group	Examples
Inventory	Stock on hand by warehouse · Stock by product and bin · Expiry exposure · Blocked stock · Dead stock (no movement 90 days) · Empty bins by zone
Receiving	Open purchase orders by age · Receipts by vendor (30 days)
Orders and shipping	Open orders by age · Orders held short · Shipments by customer (30 days) · Shipping by day (30 days)
People and activity	Adjustments by reason (30 days) · Picks per operator (7 days) · Work per operator and task (7 days)
Clients	Stock on hand by client · One client's stock by product and bin · Open orders by client · Orders shipped by client (30 days) · Receipts by client (30 days) · Warehouse activity by client (30 days) · Invoices by client

Many more are in the Database query library, also linked at the bottom of the side panel.

Separating data by client

In a 3PL warehouse, each client's products, orders, purchase orders, returns and pallets carry the client they belong to, in a ClientId column. The client's name is in the Clients table. Every audit record also carries it, in the Client column. To split any figure by client, join the Clients table and group by its name:

SELECT ISNULL(cl.Name, '(no client)') AS [Client],
       SUM(il.Quantity) AS [Quantity]
FROM InventoryLocations il
JOIN Products p ON p.Id = il.ProductId
LEFT OUTER JOIN Clients cl ON cl.Id = p.ClientId
WHERE il.Quantity > 0
GROUP BY ISNULL(cl.Name, '(no client)')

Use LEFT OUTER JOIN, so the warehouse's own stock, which belongs to no client, is kept and shows as (no client).
For one client only, filter on the name: WHERE cl.Name = 'Client name', typed exactly as on the Clients screen. The example One client's stock by product and bin does this.
Stock belongs to a client through its product (Products.ClientId). Orders, purchase orders and returns carry their own ClientId.

Find tables and columns

Tables in the side panel lists every table. Type in Search tables and columns to narrow it, by a table name (Pick) or a column name (BinCode).
Click the arrow beside a table to see its columns and their types.
Click a table or column name to insert it where the cursor is in the editor.
As you type, the editor suggests only what fits where the cursor is:
- after FROM or JOIN: table names only;
- after a table's alias and a dot (p. in FROM Products p): that table's columns;
- anywhere else: the columns of the tables your query names, the parameters, common functions such as SUM and DATEADD, and the keywords a SELECT uses.
Press Ctrl+Space to open the suggestions yourself, the arrow keys to move through them, and Enter to accept one.

The Tables panel searched for BinCode, with the Bins table unfolded to show its columns and their types

Typing SELECT * FROM prod suggests only tables: Products, ProductBundles, ProductionOrders, ProductPacksizes

Typing SELECT Sk suggests columns, with Sku first

Parameters

Two values are always available to a query:

Parameter	Value
`@UtcNow`	The current date and time, in UTC. For example `WHERE Timestamp >= DATEADD(day, -7, @UtcNow)` for the last seven days.
`@ClientId`	The client of the person running the query. It is always empty here, because only administrators, who see every client, can use the Query builder. Reports use it to show client users only their own data.

When a query fails

The reason appears in red below the editor: SQL Server's own message for a mistake, for example Invalid column name 'Qty', or the reason a query was refused. Correct the query and run it again. The Tables panel is the quickest way to check a name.

Limits

A query returns at most 10,000 rows. When there are more, LogisticaHQ says so. Add a WHERE or use TOP to narrow it. An administrator can change the limit with Maximum number of records a SQL query should return in Configuration.
Only administrators can use the Query builder.
Dates in the database are stored in UTC. Your screens show them in your own time zone, so a query for "today" may need to allow for the difference.

Common tables

Table	Holds
Products	Products: Sku, Description, dimensions, weight, cost.
InventoryLocations	Stock: one row per product and location (BinId or LicensePlateId) with Quantity, lot, serial and expiry.
Bins, Zones, Warehouses, LicensePlates	Where stock is kept: bins (BinCode) sit in zones (ZoneCode) in warehouses (WarehouseCode); pallets (Sscc) sit in bins.
PurchaseOrders, PurchaseOrderLines	Purchase orders and their lines.
PickTickets, PickTicketLines	Orders and their lines.
Customers, Vendors, Clients	Who you ship to, buy from, and store for.
AuditRecords	Every stock movement and user event. See Audit for its fields.

Was this page helpful?

Previous Audit Next History search