Extend Generic Inquiry to Show the Number of records

Question

Is it possible to extend the Generic Inquiry screen so that it shows the number of records retrieved? Or perhaps is it possible to use PXGenericInqGrph to get the number of records of a Generic Inquiry?

However, it is important, for performance reasons that I only retrieve one record with the total from the Database. and not getting all records from the database and doing a Count at the Application layer.

score 3 · Accepted Answer · answered May 24 '18 at 16:48

At least up until Acumatica 7.207.0029 there is no method to extend the Generic Inquiry results screen.

If you only need the record count, what you can do is edit your GI or create a copy to get the total and use the special <Count> field to get the record count.

Of course this requires you to set a GroupBy field and you need this to be the same for all records if you want a total record count.

If your query has a field you know to be equal to all records, you can use that field in the GroupBy tab. If not, there is a way to do this by adding a join to an number table.

Number Table Workaround

This technique uses a table with numbers to create specific queries. In this case we can join it to your query to add a known common value to all rows.

Here is the XML for a Customization Project that creates this table and makes it available as the Is.Objects.Core.ISNumbers DAC.

<Customization level="200" description="Number utility table" product-version="17.207">
    <Graph ClassName="ISNumbers" Source="#CDATA" IsNew="True" FileType="NewDac">
        <CDATA name="Source"><![CDATA[using System;
using PX.Data;

namespace IS.Objects.Core
{
  [Serializable]
  public class ISNumbers: IBqlTable
  {
    #region Number
    [PXDBInt(IsKey = true)]
    [PXUIField(DisplayName = "Number", IsReadOnly = true)]
    public int? Number { get; set; }

    public class number : IBqlField{}
    #endregion
  }
}]]></CDATA>
    </Graph>
    <Sql TableName="ISNumbers" CustomScript="#CDATA">
        <CDATA name="CustomScript"><![CDATA[IF OBJECT_ID('ISNumbers', 'U') IS NOT NULL DROP TABLE ISNumbers;

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO ISNumbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE ISNumbers ADD CONSTRAINT PK_ISNumbers PRIMARY KEY CLUSTERED (Number)]]></CDATA>
    </Sql>
</Customization>

Just add the table to the GI and crate an INNER JOIN relation where the value of the number field equals 1:

Then you can use this field in the GroupBy condition.

Then you can add the Numbers field and set its value to <Count>. Leave all your other result fields to keep the logic but hide them if you don't need them (they will be automatically grouped by max value).

All queries performed by GIs are executed in the DB so you don't need to worry about it running in the App side.

Extend Generic Inquiry to Show the Number of records

1 Answers1