Acumatica GI Cross-Table Joins — A Deep Dive

Every GI beyond a single-table lookup is a join, and the GI Designer's Tables tab makes adding a join dangerously easy — drag a related table in, pick a relation, done. What it does not make easy is understanding what that join does to your row count, and that gap is where most "the GI shows duplicate rows" and "the total is double what it should be" tickets come from.

Relation type controls both correctness and row count

The Tables tab offers Left Join, Inner Join, and a couple of Acumatica-specific relation shortcuts derived from the DAC's declared foreign keys. The choice affects two independent things people conflate: whether parent rows without a matching child survive, and how many rows a matching parent produces.

SOOrder  InnerJoin  SOLine  On SOLine.OrderNbr = SOOrder.OrderNbr
-- orders with zero lines vanish entirely (rare, but possible on drafts)
-- orders with 5 lines produce 5 output rows, each repeating the order header

SOOrder  LeftJoin   SOLine  On SOLine.OrderNbr = SOOrder.OrderNbr
-- orders with zero lines survive, with SOLine columns null
-- orders with 5 lines still produce 5 output rows

Neither join type avoids the fan-out — that is inherent to joining a one-to-many relationship at all. If someone asks for "one row per order" and your GI joins to line detail, you either accept multiple rows per order (fine for a line-level export) or you need an aggregate (SUM the lines) instead of a raw join, which changes the grain back to one-per-order at the cost of losing line-level detail in that same GI.

The double fan-out: two children joined to one parent

This is the join mistake I see most often in inherited GIs. Someone wants an order's lines and its shipments on one inquiry, joins both children to the order header, and gets a cross-product: 5 lines × 3 shipments = 15 output rows, none of which correspond to anything real, and any SUM on the order total is now inflated 3x. There is no join-type setting that fixes this — it is structural. The fix is one of:

• Split into two GIs, one per child relationship, if both need full detail.
• Aggregate one side before joining — sum shipment quantities per order in a sub-select/projection, then join that single summary row per order to the line detail.
• Use a subreport-style pattern if the destination is a printed document rather than an interactive grid (see the subreports post for the report-layer equivalent of this problem).

Joins across unrelated modules need an explicit key

The designer's relation shortcuts only exist for foreign keys Acumatica's DACs already declare. Joining, say, a custom GL cost-center dimension to InventoryItem has no built-in relation, so you add the table manually and write the On condition by hand, referencing the actual key fields. Get the key wrong (a common one: joining on a code field that is not actually unique per the intended grain) and you get a fan-out that looks like a legitimate one-to-many join but is really a data-quality bug in your join key.

Wrapping up

Every join in a GI is either narrowing (inner) or preserving (left) on the parent side, and separately, every one-to-many join fans rows out — that part is unavoidable and not a setting. Watch for the double fan-out when two child tables join to the same parent, verify row counts against a known record before shipping, and hand-write join conditions carefully when there is no built-in relation to lean on.