Acumatica GI Navigation Not Working with Custom SQL Views?
Custom SQL views are a powerful way to extend Acumatica's reporting capabilities. They let you pre-join complex data, perform aggregations, and present information that would be difficult or impossible to achieve with standard Generic Inquiry (GI) configuration alone. But there's a frustrating scenario that catches many developers: the GI runs perfectly, the data displays correctly, the navigation links appear as clickable blue text—and then nothing happens when you click them.
⚡ Key Insight: If navigation appears but doesn't work on a GI backed by a custom SQL view, the first thing to check is your DAC definition—specifically, whether your key fields are properly marked with IsKey = true and whether your view returns duplicate records.
The Problem
You've created a custom SQL view, generated a DAC from it, and built a Generic Inquiry. The GI displays your data flawlessly. Acumatica even recognizes the navigation targets—those fields that should link to standard forms like Invoices and Memos (AR301000) or Sales Orders (SO301000) render as clickable links. But when you click them, the browser does nothing. No pop-up, no navigation, no error. Just silence.
This behavior is confusing because the navigation appears to be configured correctly. The GI knows the field has a default navigation target. The link is rendered. So why doesn't it work?
The Root Cause: DAC Key Definition
Acumatica's navigation system relies on the DAC's key structure to identify records uniquely. When you click a navigation link, the system needs to know which record to open. It determines this by looking at the fields marked with IsKey = true in your DAC.
When you generate a DAC from a SQL view using the "Generate Members from Database" option, Acumatica creates the field properties but does not automatically identify which fields are the key fields [citation:8][citation:29]. The generated DAC will have fields, but none of them will be marked with IsKey = true.
Without a proper key definition, Acumatica cannot resolve the navigation target. It doesn't know how to construct the URL parameters needed to open the destination form. The link renders because the field has a default navigation target defined in the source code, but the click handler cannot execute because the record identity is ambiguous.
⚠️ Critical: This is not a bug in Acumatica—it's a configuration issue in your custom DAC. The framework needs explicit key definitions to function correctly.
The Second Suspect: Duplicate Records
Even if you mark your key fields correctly, navigation can still fail if your SQL view returns duplicate records for the same key combination. This is a common issue when views involve joins that produce multiple rows per logical record.
Acumatica's navigation system expects each row in the GI results to correspond to a unique, identifiable record. If your view returns two rows with the same key value—even if the other columns differ—the system cannot determine which record to navigate to. The result is the same: links appear but do nothing.
This is a known issue with custom views in Acumatica, and the solution is well-documented in the developer community: ensure your DAC has correct key definitions, and verify that your view doesn't produce duplicate rows for the same key [citation:6][citation:18].
How to Fix It
Step 1: Identify Your Key Fields
First, determine which fields in your view uniquely identify a record. This should be the same combination of fields that would be used to look up a single record in the underlying table. For example, if your view is based on sales orders, the key might be OrderType and OrderNbr.
Step 2: Mark the Key Fields in Your DAC
Open your DAC code and add IsKey = true to the attributes of the fields that form the unique key. Here's an example:
// Before (generated, no key)
[PXDBString(2, IsFixed = true)]
[PXUIField(DisplayName = "Order Type")]
public virtual string OrderType { get; set; }
[PXDBString(15, IsUnicode = true)]
[PXUIField(DisplayName = "Order Nbr")]
public virtual string OrderNbr { get; set; }
// After (with key definition)
[PXDBString(2, IsFixed = true, IsKey = true)]
[PXUIField(DisplayName = "Order Type")]
public virtual string OrderType { get; set; }
[PXDBString(15, IsUnicode = true, IsKey = true)]
[PXUIField(DisplayName = "Order Nbr")]
public virtual string OrderNbr { get; set; }
💡 Pro Tip: The order of IsKey = true in the attribute doesn't matter. What matters is that all fields forming the unique key are marked. If your view has a single unique identifier column, mark only that column.
Step 3: Check for Duplicates in Your View
Run your SQL view directly in SQL Server Management Studio and check for duplicate key combinations:
SELECT YourKeyField1, YourKeyField2, COUNT(*) AS DuplicateCount
FROM YourCustomView
GROUP BY YourKeyField1, YourKeyField2
HAVING COUNT(*) > 1
If this query returns any rows, your view is producing duplicates. You'll need to modify the view to eliminate them—either by adding a DISTINCT, adjusting joins, or adding aggregation.
Step 4: Republish and Test
After making these changes, republish your customization and test the navigation again. The links should now work correctly.
A Note on DAC Generation Order
If you're creating a new SQL view and DAC from scratch, the order of operations matters. A common mistake is to create the DAC before publishing the database script that creates the view. This causes errors and can lead to incomplete DAC generation.
The correct sequence is:
- Add the SQL script to Database Scripts in the customization project
- Publish the customization to create the view in the database
- Then create the DAC and use "Generate Members from Database"
Following this order ensures that Acumatica can read the view's structure and generate the DAC fields correctly [citation:8].
Summary
When navigation links appear but don't work on a Generic Inquiry backed by a custom SQL view, the issue almost always comes down to one of two things:
✅ Missing IsKey Attributes
The generated DAC doesn't have key fields marked. Add IsKey = true to the fields that uniquely identify records.
✅ Duplicate Records in the View
The SQL view returns multiple rows for the same key. Check with a GROUP BY query and fix the view logic.
Both issues are easy to fix once identified, but they can be frustrating to diagnose because the symptom—clickable links that do nothing—doesn't point directly to the cause. By checking your DAC key definitions and verifying your view's uniqueness first, you'll save hours of troubleshooting.
📌 Key Takeaway: Acumatica's navigation system relies on explicit key definitions in your DAC. When you generate a DAC from a SQL view, always review the generated code and manually add IsKey = true to the appropriate fields. This simple step prevents one of the most common GI navigation issues with custom views.
🔍 Further Reading: For more information on creating DACs from SQL views and configuring navigation in Generic Inquiries, refer to the Acumatica Developer Guide and the Acumatica Community forums.