Acumatica · Generic Inquiries

Acumatica Generic Inquiries — The Definitive Guide

A complete reference to Acumatica Generic Inquiries (GIs) — building blocks, joins, parameters, pivots, conditions, row-level security, and the patterns that turn GIs into a real reporting layer.

John Kihiu16 min read

The Generic Inquiry is Acumatica's low-code reporting and data-access tool. It is the single most underused feature in the platform — used casually by business analysts, leveraged at scale by people who understand how to push it. This guide is the second kind.

1. What a GI is, exactly

A GI is a saved query that:

Under the hood, a GI compiles to a SQL view (or an inline TVF for parameterised ones). The platform manages this for you.

2. Building blocks

3. The art of the join

Joins are where most GI performance problems start. Three rules:

  1. Join only the tables you actually need a field from.
  2. Prefer Acumatica's predefined relations over ad-hoc joins.
  3. For very large tables, denormalise via a view that pre-joins.
Filter at the GI level, not in the UI A GI that returns 5 million rows and filters in the presentation layer will bring the page to its knees. Move the filter to the Conditions tab and let SQL Server handle it with an index.

4. Parameters — the right design

Parameters are what turn a GI from a static report into a real analytical tool. The patterns that work:

GI · DEFAULT VALUE
// First of current month
=DateSerial(Year(Now), Month(Now), 1)
// Today
=Today()
// Current branch (AccessInfo)
=GetBranch()

5. Calculated columns

Calculated columns are expressions over the joined fields. The expression language is Acumatica-specific but SQL-flavoured. Use them for:

6. Grouping and aggregation

Group By collapses rows that share a key. Aggregations (Sum, Count, Avg, Min, Max) compute over the group. Use them for "sales by customer", "invoices by branch", "stock by warehouse".

7. Pivot tables

Acumatica GIs support pivot output — rows × columns × values. Powerful, but expensive. Pivot GIs do not perform well past ~100k source rows. If you need pivot output over more data, pre-aggregate into a summary table or use a report instead.

8. Row-level security

A GI respects Acumatica's row-level security by default. If a user has access to only certain branches, the GI shows only those branches. You can tighten this further by adding a condition that filters by GetUserID() or GetBranch().

Always test row-level security Switch to a user with restricted access and re-run the GI. If the GI exposes rows the user should not see, you have a leak. Add the security condition explicitly.

9. GIs as data sources

A GI is more than a screen — it can be:

10. Publishing and access

You can publish a GI to a workspace, a dashboard, a site map, or the Acumatica Self-Service Portal. Each placement has its own access right. Set these deliberately — an open GI in a public workspace is a common data leak.

11. Performance checklist

  1. All join columns indexed? Check the schema.
  2. All filter columns indexed? Check.
  3. Group By columns indexed? Helps aggregation.
  4. No SELECT * on joined tables. Select only the fields you display.
  5. Limit the data set in Conditions, not in the UI.
  6. Test with production-scale data, not 100 test rows.

12. Common mistakes

Related reading

Going deeper: production-grade patterns

The patterns above cover the basics. In production, the same patterns have to survive three things: scale, edge cases, and the next Acumatica upgrade. Here are the patterns that distinguish a working customisation from a great one — the ones I have applied to every client project in East and Southern Africa, and the ones that make the difference between a customisation the user trusts and a customisation they curse.

Defensive coding for the unexpected

Production is where the assumption dies. Every customisation that "works in test" fails in production the first time a customer name has a special character, an invoice is in a foreign currency, or a record has a null in a field you thought was required. The defensive habit is to explicitly handle the null, the empty, the special character, and the foreign currency in every event handler and every code path. The cost is 20% more code. The payoff is 95% fewer production tickets.

Three patterns I apply everywhere:

C# · DEFENSIVE PATTERN
public class DefensiveExt : PXGraphExtension<BaseGraph>
{
    protected void _(Events.RowSelected<MyDAC> e)
    {
        var row = e.Row;
        if (row == null) return;                          // null-safe
        var ext = row.GetExtension<MyDACExt>();
        if (ext == null) return;                         // null-safe extension
        var value = ext.UsrField ?? "DEFAULT";           // null-coalesce
        var ok = decimal.TryParse(value, out var n);    // try-parse
        if (!ok) { /* handle */ }
    }
}

Performance: the patterns that scale

Five performance patterns I apply on every customisation, in order of impact:

  1. Move heavy logic out of RowSelected. Push validation to RowPersisting, side effects to a graph action triggered by a button. RowSelected fires for every row on every render.
  2. Index the join columns. Every BQL Where<> filter needs an index. Check the execution plan before you ship.
  3. Filter at the GI, not the UI. A GI that returns 5 million rows and filters in the presentation layer will time out. Push filters into the Conditions tab.
  4. Batch the work. Loop with 1,000 calls is slow; loop with 10 calls of 100 records is fast. Batch where you can.
  5. Cache the static. Tax schedules, account lists, and other static reference data can be cached for the lifetime of the app pool. Reduce the database load.

For the full performance playbook, see the performance tuning guide and the SQL Server indexing guide.

Upgrade survival

The customisation that breaks on the next Acumatica upgrade is the one that took a shortcut. The patterns that survive:

C# · USR PREFIX CONVENTION
// Base field — Acumatica owns this
[PXDBString(40)]
public string RefNbr { get; set; }

// Your field — always Usr prefix, never collides
[PXDBString(40)]
[PXUIField(DisplayName = "External Ref")]
public string UsrExternalRef { get; set; }

// Your DAC extension — soft extension, survives table drops
[PXTable(IsOptional = true)]
public class MyDACExt : PXCacheExtension<MyDAC>
{
    #region UsrCustomField
    [PXDBString(60)]
    public string UsrCustomField { get; set; }
    public abstract class usrCustomField :
        PX.Data.BQL.BqlString.Field<usrCustomField> { }
    #endregion
}

Testing: the habit that pays for itself

If you are not testing your customisation with the Acumatica Unit Test Framework, you are running blind. The framework ships with every installation, costs nothing, and pays for itself the first time an upgrade changes a method signature on you. The minimum coverage:

For the full test framework walkthrough, see the unit test framework guide.

Operations: what to do after the customisation is live

A customisation is not "done" when it ships. It is "done" when it has run in production for a quarter without a critical incident. The operational habits that get you there:

For the broader operational patterns, see the monitoring guide and the licence concurrency guide.

The migration off the old customisation

Every customisation is eventually replaced. Plan for that day from the start. The patterns:

For the broader migration patterns, see the data migration guide.

Wrapping up

That is the working approach I use on Acumatica projects. The same patterns show up whether you are in Nairobi, Johannesburg, Kigali, Lusaka or Harare — and they are the things that keep work moving when an upgrade lands at 6 PM on a Friday. If you are stuck on something specific, reach out or keep reading through the rest of the Acumatica blog.

Related reading