Wednesday, 8 June 2016

Using PXView in DataView Delegate

Hi Everyone,

Today I want to share with you one way how you can keep your code simpler.

Lets assume you have some big data view declaration - PXSelect<Table, <InnerJoin, .... <AnotherJoin, ... <MoreJoins, .... <Where<, .... <MoreWheres< .... SomeDataView
And something like this for 10-20-30 lines of code. I'm pretty sure that you can understand what do i mean.
And also this select declaration may be inside Acumatica base code and you want to customize it.

Your task is to create a new data view delegate and select the same data with some additional filtering or dynamic calculations. But we cannot just create delegate and call dataview data, as we will have stack-overflow exception.

First idea to resolve it is to just copy the PXSelect statement and put it into your delegate. But this idea is not perfect, becouse:
  • You will store BQL command twice, and if you need to modify it, you have to do it twice
  • If this statement is from Acumatica source code you have a risk that Acumatica will update this command in the future, but your copy will be untouched.
So to resolve this problem we can use PXView declaration inside the data view delegate with reference to the original BQL Statement.

public IEnumerable dataView()
{
       PXView select = new PXView(this, true, DataView.View.BqlSelect);

       Int32 totalrow = 0;
       Int32 startrow = PXView.StartRow;
List<object> result = select.Select(PXView.Currents, PXView.Parameters,        
       PXView.Searches, PXView.SortColumns, PXView.Descendings, 
       PXView.Filters, ref startrow, PXView.MaximumRows, ref totalrow);
       PXView.StartRow = 0;

       foreach (PXResult<Contract, ContractBillingSchedule, CSAnswers> row in result)
       {
              //Do any dynamic calculations
       }

       return result;
}

Some important points here:
  • DataView.View.BqlSelect - using this construction you can get access to the BQL command that lies under the PXSelect<>.
  • Int32 startrow = PXView.StartRow - is required to support proper paging. By default Acumatica will select just some rows that can be shown on user interface. Other rows should be skipped for better performance.
  • PXView.MaximumRows - will be exactly top xx records that will be selected from database.
  • PXView.StartRow = 0; - required to notify base code that you have already filtered data and select required records count. Otherwise base code will trim records one more time, because by default system thinks that you will select all records and return untrimmed list. 
Full code snippet:

Have a nice Development!

No comments: