Pagination In Custom Inquiry

Here are instructions for applying Pagination in Custom Inquiry Pages. View the code below:

public class YourGraphInquiry : PXGraph
{
public PXCancel Cancel;
public PXFilter Filter;

[PXFilterable]
public PXSelect>,
Or, IsNull>>,
And<
Where>,
Or, IsNull>>>>> Details;

public virtual IEnumerable details()
{
PXView cmd = new PXView(this, true, Details.View.BqlSelect);
var currentFilter = Filter.Current;

var s = (currentFilter.PageNbr ?? 0) * (currentFilter.PageSize ?? 0);
int startRow = s > 0 ? s : PXView.StartRow;
int totalRows = 0;
int maxRows = (currentFilter.PageSize ?? 0) == 0 ? PXView.MaximumRows : currentFilter.PageSize ?? 0;
foreach (var result in cmd.Select(new[] { currentFilter }, null, PXView.Searches,
PXView.SortColumns, PXView.Descendings, PXView.Filters, ref startRow,

maxRows, ref totalRows))
{
yield return result;
}
startRow = 0;
}
public YourGraphInquiry ()
{
Details.Cache.AllowInsert = false;
Details.Cache.AllowDelete = false;
Details.Cache.AllowUpdate = false;
}

}

[System.SerializableAttribute()]
public class YourDACFilter : PX.Data.IBqlTable
{
#region SomeID
public abstract class SomeID : BqlInt.Field
{
}

protected int? _SomeID;

[PXDBInt()]
[PXDefault()]
[PXUIField(DisplayName = "Some", Visibility = PXUIVisibility.Visible)]
[PXSelector(typeof(Search), new Type[]{ typeof(MVP.CWSome.SomeCD), typeof(MVP.CWSome.description), typeof(MVP.CWSome.SomeCD) }, SubstituteKey = typeof(MVP.CWSome.SomeCD))]
public virtual int? SomeID
{
get { return this._SomeID; }
set { this._SomeID = value; }
}

#endregion SomeID

#region SomePartId

public abstract class SomePartId : BqlInt.Field{
}

protected int? _SomePartId;

[PXDBInt()]
[PXDefault()]
[PXUIField(DisplayName = "Some Part", Visibility = PXUIVisibility.Visible)]
[PXSelector(typeof(Search>>>),
new Type[] { typeof(CWSomePart.SomePartCD), typeof(CWSomePart.SomePartFullCD) }, SubstituteKey = typeof(CWSomePart.SomePartCD))]
public virtual int? SomePartId
{
get { return this._SomePartId; }
set { this._SomePartId = value; }
}
#endregion SomePartId

#region Page number
public abstract class pageNbr : BqlInt.Field { }
[PXInt]
[PXUIField(DisplayName = "Page Number")]
[PXDefault(0)]
public virtual int? PageNbr { get; set; }#endregion Page number

#region page size

public abstract class pageSize : BqlInt.Field { }
[PXInt]
[PXUIField(DisplayName = "Page Size")]
[PXDefault(3)]
public virtual int? PageSize { get; set; }

#endregion page size
}
}

This code example has a few important features: With this code, you can achieve fragmentation of reading your data. In order to avoid returning everything, use the structure of yield return. It’s efficient because it does not return the whole array, but elements of the array one by one. At the end of the details method, you can see startRow = 0. Wonder why? The reason is simple. After all elements in DB are executed, that line of code will reset the reading of pages to the beginning. cmd.Select allows you to read a chunk of data from the DB in a very similar manner to SelectWindowed.If you check generated SQL in the SQL Server Profiler, you’ll see:

SELECT TOP (3) [YourDACDetails].[Column1], [YourDACDetails].[Column2], .....

For the second and next pages, generated SQL will not be very efficient, but on UI Side you will notice it. For example, when I’ve set Page number = 3, and page size = 5, I’ve got following SQLl:

exec sp_executesql N'SELECT TOP (20) [YourDACDetails].[Column1],[YourDACDetails].[Column2], .....

This leads to the conclusion that the Acumatica framework on the later stage just throws out redundant values.

SUMMARY:

After reviewing these details you may wonder when you need pagination for a custom inquiry. Pagination is most useful for Web Services Synchronization. If you want to achieve pagination for some of your data, then the simplest way is to follow the pattern shown.