Hi everybody,

today I want to share quite useful post for cases, if you'll need to get some limited dataset from Acumatica database. 

Take a look on the code below:

public PXAction<SOOrder> FilterColumns;
[PXButton]
[PXUIField(DisplayName = "Filter Columns")]
public IEnumerable filterColumns(PXAdapter adapter)
{
    var arTrans = SelectFrom<ARTran>.View.Select(Base).
            Where(t => t.Record.TranType == "CRM")
            .Select(r => new { r.Record.TranType, r.Record.RefNbr, r.Record.CuryTranAmt });
 
    Base.CurrentDocument.Current.DocDesc += arTrans.Sum(a => a.CuryTranAmt).ToString();
    Base.CurrentDocument.Cache.Update(Base.CurrentDocument.Current);
 
    return adapter.Get();
}

As you may notice, it reads from table ARTran, so nothing special. But tell me please, what columns will be read? All of ARTran columns? Three columns? You'll be surprised to learn, that only one column will be read. And that will be aggregated column.

Below goes a screenshot from SQL Server profiler:

as you see, in the output, Acumatica ORM generated single value and single column.

Summary

If you need optimize reading from database, then consider to use ordinary LINQ, as Acumatica ERP become more efficient.