Generic Inquiry Changes in Acumatica 5.3 explained

In Acumatica version 5.3 you may have noticed some mysterious warnings appearing on your generic inquiries. I could not find any documentation on why this started showing up. After a little digging this is what I discovered.

Problem

Let’s start with a simple test generic query.

Just use the SOOrder table

On the Results Grid add OrderNBR and CustomerID_Description and you will see the warning.

Generic Inquiry has automatically joined to the BAccount table to get the Account Name (customerID_Description – BAccount.AcctName) to be displayed.

Acumatica created the following SQL Statement to retrieve the SOOrder records. Not so easy to understand, lots of things going on.

SELECT TOP (33) [SOHeader].[OrderType] [SOHeader_OrderType], [SOHeader].[OrderNbr] [SOHeader_OrderNbr], [SOHeader].[CustomerID] [SOHeader_CustomerID], [SOHeader].[NoteID] [SOHeader_NoteID], (SELECT TOP (1) [NoteText] FROM [dbo].[Note] WHERE ([dbo].[Note].CompanyID IN (1, 7) AND 32 = SUBSTRING([dbo].[Note].CompanyMask, 2, 1) & 32) AND [Note].[NoteId] = [SOHeader].[NoteID]) [SOHeader_NoteText], (SELECT TOP (1) COUNT(*) FROM [dbo].[NoteDoc] WHERE ([dbo].[NoteDoc].CompanyID IN (1, 7) AND 32 = SUBSTRING([dbo].[NoteDoc].CompanyMask, 2, 1) & 32) AND [NoteDoc].[NoteId] = [SOHeader].[NoteID]) [SOHeader_NoteFiles], [SOHeader].[CuryID] [SOHeader_CuryID], [SOHeader].[CuryInfoID] [SOHeader_CuryInfoID] FROM SOOrder [SOHeader] WHERE ([SOHeader].CompanyID = 7) AND ([SOHeader].BranchID IS NULL OR [SOHeader].BranchID IN (5, 6, 10, 11, 12, 13)) ORDER BY [SOHeader].[OrderType] ASC, [SOHeader].[OrderNbr] ASC OPTION(OPTIMIZE FOR UNKNOWN) /* 00.00.00.00 */

To simplify, we can manually create a simplified SQL statement that will provide what we need to see to understand what is Generic Inquiry is doing.

First it returns all of the SOOrder header records.

Select OrderNbr, CustomerID From SOOrder Where CompanyID = 7 Order By SOOrder.OrderNbr

Next, it then loops through each of the customer ids and retrieves the AcctName. It caches the results so that for each name it only does the BAccount look up once.

Select AcctName From BAccount Where BAccountID = 2470 And CompanyID = 7

From BAccount Where BAccountID = 260 And CompanyID = 7

Select AcctName From BAccount Where BAccountID = 262 And CompanyID = 7

Example of this in SQL

With bigger record sets this can get very inefficient.

Solution

So how do we improve the performance and get rid of the warning. Easy!

We add the BAccount table to the Tables tab on the generic inquiry.

Create the relations between the Tables (join).

Replace the default customerID_Description with AcctName from the BAccount Table and warning disappears.

Now when Acumatica executes the query it runs the equivalent of:

Select SOOrder.OrderNbr, BAccount.AcctName

From SOOrder Inner Join BAccount On SOOrder.CompanyID = BAccount.CompanyID

And SOOrder.CustomerID = BAccount.BAccountID

Where SOOrder.CompanyID = 7

Order By SOOrder.OrderNbr

A much more efficient SQL query. Mystery solved.

Changing Acumatica Grid Column Headings at Runtime

Changing Acumatica Grid Column Headings at Runtime

Acumatica allows developers to dynamically change the headings of grid columns when the user changes the values of fields that are not in the grid.

For this to work, there are three things that the developer must do. First, the controls that the non-grid fields are bound to must have their CommitChanges property set to True. This is to ensure that changes to their values will cause a postback and that the RowSelected event of the associated cache will be fired.

Next, within the RowSelected event handler of the non-grid cache, the developer must call the PXUIFieldAttribute.SetDisplayName method to specify what the new column heading will be.

Finally, the Grid’s RepaintColumns must be set to True in order to ensure that the grid gets repainted when the postback completes.

Below is an example implementation of the RowSelected event that changes the heading of the Sales Orders screen’s Free Item column. This will change the column heading from “Free Item” to “Ext Free” or “Free” based on the value of the CustomerRefNbr field.

protected void SOOrder_RowSelected(PXCache cache, PXRowSelectedEventArgs e)

{

if (e.Row == null) return;

SOOrder data = (SOOrder)e.Row;

PXUIFieldAttribute.SetDisplayName<SOLine.isFree>(Base.Transactions.Cache, (data.CustomerRefNbr == “A1”) ? “Ext Free” : “Free”);

}