Acumatica Generic Inquiries — The Definitive Guide

If I had to name the single highest-leverage tool in Acumatica, it wouldn't be the REST API or the customization framework — it would be Generic Inquiries. A GI is a saved, parameterized query built on the same DACs the rest of the system uses, rendered as a grid screen, exportable to Excel, publishable to dashboards and the mobile app, exposable through OData and the REST API, and usable as a lookup or a substitute list screen. On most of my projects, GIs replace a third of what the client assumed would be paid report development.

This guide covers how GIs actually work under the hood and the design decisions that separate a fast, maintainable inquiry from the five-minute-timeout monsters I get called in to fix.

How a GI executes

A GI is not stored SQL. The designer (screen SM208000) builds a BQL query at runtime from your Tables, Relations, Conditions, and Grouping tabs, and BQL compiles down to SQL against the tenant's data with row-level security, tenant filtering, and attribute-driven logic applied automatically. That's the key difference from writing a view in SQL Server: a GI respects PXProjection-style semantics — restriction attributes on DACs still apply, so a user can't see via GI what the security model hides from them on screens.

The practical consequence: your join and condition choices translate fairly directly into the generated SQL. A GI that joins eight tables with OR-heavy conditions produces exactly the SQL you'd fear.

Tables and relations: where GIs are won or lost

The Relations tab is a join editor. Each pair of tables gets a join type (Inner, Left, Full, Cross) and one or more field equations. The mistakes I see repeatedly:

• Wrong join type flipping row counts. Inner-joining SOOrder to SOShipment silently drops unshipped orders. If the requirement says "all orders, with shipment info where it exists," that's a Left join, full stop.
• Fan-out from joining two child tables. Join orders to both lines and shipments and you get a Cartesian blow-up per order. Aggregate one side first (or build two GIs) instead.
• Joining on non-key fields. Joining InventoryItem to transactions on InventoryCD instead of InventoryID works until it doesn't — always join on the integer identity keys.

Conditions and parameters

Conditions filter the result set; Parameters let the user supply values at runtime. The two combine: a condition like SOOrder.OrderDate >= [FromDate] references a parameter by its name in brackets. Give parameters sensible defaults — a date parameter defaulting to @monthStart spares users from accidentally querying five years of history. One detail worth knowing: a condition on a parameter can be made optional by ticking the parameter's "required" off and using the IsNull pattern in conditions, so a blank parameter means "don't filter."

Grouping and aggregation

The Grouping tab turns the GI into a GROUP BY query. Any field not in the grouping list must carry an aggregate (SUM, MIN, MAX, COUNT, AVG) on the Results Grid tab or it will return arbitrary values. Aggregated GIs are how you get "sales by customer by month" without a report — and paired with a formula column using functions like =Round([SOOrder.CuryOrderTotal], 2) or date-part expressions, they cover a lot of finance asks.

GIs as substitute list screens and entry points

Two features turn GIs from reports into UI. First, navigation: on the Results Grid you can make a column a link to the source screen, so clicking an order number opens SO301000 positioned on that order. Second, Entry Point / substitute screen: a GI can replace a screen's default list view entirely, which is how you give the sales team a list of orders with your custom columns, conditional highlighting, and filters as their daily driver. Add side panels (a GI or dashboard docked next to the grid) and you've built a mini-workspace with zero code.

Exposing GIs: OData, REST, dashboards

Tick "Expose via OData" and the GI becomes an OData feed at /odatav4/ — the cleanest way to feed Power BI or a warehouse without building an endpoint. GIs can also back dashboard widgets, mobile screens, and Business Events (a GI defines the monitored data; the event fires on changes to its result set). That last one is worth internalizing: a well-designed GI is often the trigger definition for your automation, not just a report.

Performance basics

Most slow GIs are slow for boring reasons: leading-wildcard LIKE conditions, joins on non-indexed custom columns, OR conditions that defeat index seeks, or simply returning 200,000 rows into a grid. Set a row limit while designing, check the generated SQL with the request profiler when something drags, and remember that an unindexed Usr field used as a join key needs an index adding via the customization project.

Wrapping up

Generic Inquiries are Acumatica's best power-to-effort ratio: real queries with security applied, rendered as usable screens, exposed to OData and events, all without code. Learn the Relations tab cold — join type and fan-out errors cause most wrong numbers — give every parameter a default, aggregate deliberately, and treat GI design as query design, because that's what it is.