Skip to main content
Solved

How to filter a View Delegate and Sort the return result?

  • June 5, 2022
  • 4 replies
  • 981 views
aaghaei
Captain II
Forum|alt.badge.img+10

Hello Gang,

 

I have some customization on Acumatica Cost Projection screen that reuirs adding a subtotal for each “Project Task” under the related records. I have done the coding but my code instead of each task subtotal, repeats all records total. Also subtotals are added to end of my result as I can not sort them correctly before returning the result. Below is my code. Can someone advise how I can I

  • show the correct subtotals by filtering each task related records ​​​
  • sort the return result set?
        public IEnumerable details()
        {
            var selectDetails = new PXSelect<PMCostProjectionLine,
                Where<PMCostProjectionLine.projectID, Equal<Current<PMCostProjection.projectID>>,
                    And<PMCostProjectionLine.revisionID, Equal<Current<PMCostProjection.revisionID>>,
                        And<Where<Current<UDCTPMCostProjectionFilter.taskID>, IsNull, Or<Current<UDCTPMCostProjectionFilter.taskID>, Equal<PMCostProjectionLine.taskID>>>>>>,
                OrderBy<Asc<PMCostProjectionLine.projectID,
                    Asc<PMCostProjectionLine.taskID,
                        Asc<PMCostProjectionLine_Extension.sortOrder,
                            Asc<PMCostProjectionLine.costCodeID,
                                Asc<PMCostProjectionLine.accountGroupID,
                                    Asc<PMCostProjectionLine.inventoryID>>>>>>>>(Base);

            PXDelegateResult delegateResult = new PXDelegateResult();
            delegateResult.Capacity = 202;
            delegateResult.IsResultFiltered = false;
            delegateResult.IsResultSorted = true;
            delegateResult.IsResultTruncated = false;

            var view = new PXView(Base, false, selectDetails.View.BqlSelect);
            var startRow = PXView.StartRow;
            int totalRows = 0;

            var resultSet = view.Select(PXView.Currents, PXView.Parameters, PXView.Searches, PXView.SortColumns, PXView.Descendings, PXView.Filters, ref startRow, PXView.MaximumRows, ref totalRows);
            PXView.StartRow = 0;

            delegateResult.AddRange(resultSet.RowCast<PMCostProjectionLine>());

            foreach (PMTask task in PXSelect<PMTask, Where<PMTask.projectID, Equal<Required<PMTask.projectID>>>, OrderBy<Asc<PMTask.taskCD>>>.Select(Base, Base.Document.Current.ProjectID))
            {
                // I want to filter the "view" here by "Base.Document.Current.ProjectID", "Base.Document.Current.RevisionID", "task.TaskID". How can I do that?
                view.WhereAnd<Where<PMCostProjectionLine.projectID, Equal<Current<PMCostProjectionLine.projectID>>,
                                And<PMCostProjectionLine.revisionID, Equal<Current<PMCostProjectionLine.revisionID>>,
                                    And<PMCostProjectionLine.taskID, Equal<Current<PMCostProjectionLine.taskID>>>>>>();

                List<PMCostProjectionLine> viewResult = new List<PMCostProjectionLine>(selectDetails.Select().RowCast<PMCostProjectionLine>());

                PMCostProjectionLine viewTotal = viewResult.CalculateSumTotal(selectDetails.Cache);
                PMCostProjectionLine_Extension viewTotalExt = viewTotal.GetExtension<PMCostProjectionLine_Extension>();

                viewTotal.ProjectID = Base.Document.Current.ProjectID;
                viewTotal.RevisionID = Base.Document.Current.RevisionID;
                viewTotal.LineNbr = null;
                viewTotal.TaskID = task.TaskID;
                viewTotal.CostCodeID = null;
                viewTotal.AccountGroupID = null;
                viewTotal.InventoryID = null;
                viewTotal.Description = "Subtotal - " + task.Description;
                viewTotal.UOM = null;
                viewTotal.Mode = null;

                viewTotalExt.SortOrder = 1;

                delegateResult.Add(viewTotal);
            }

            // I want to sort the result by ProjectID, TaskID and SortOrder before returning the delegate result. How can I do that?
            return delegateResult;
            //return delegateResult
            //.OrderBy<Asc<PMCostProjectionLine.projectID,
            //    Asc<PMCostProjectionLine.taskID,
            //        Asc<PMCostProjectionLine_Extension.sortOrder>>>>;
        }

 

Best answer by aaghaei

@Yuriy Zaletskyy @Chris Hackett 

Here is what I did to get this output.

1- I added a virtual DAC

//**************************************** PMCostProjectionFilter ****************************************//
[PXCacheName("Cost Projection Filter")]
[Serializable]
[System.Diagnostics.CodeAnalysis.ExcludeFromCodeCoverage]
public class UDCTPMCostProjectionFilter : IBqlTable
{
    #region TaskID
    public abstract class taskID : PX.Data.BQL.BqlInt.Field<taskID> { }
    protected Int32? _TaskID;
    [ProjectTask(typeof(PMCostProjection.projectID), DisplayName = "Task", AlwaysEnabled = true, DirtyRead = true, Visibility = PXUIVisibility.SelectorVisible, Required = false, Enabled = true, Visible = true)]
    public virtual Int32? TaskID
    {
        get
        {
            return this._TaskID;
        }
        set
        {
            this._TaskID = value;
        }
    }
    #endregion

    #region DisplayTaskTotal
    public abstract class displayTaskTotal : PX.Data.BQL.BqlBool.Field<displayTaskTotal> { }
    protected Boolean? _DisplayTaskTotal;
    [PXDBBool()]
    [PXDefault(false)]
    [PXUIField(DisplayName = "Display Task Total", Visibility = PXUIVisibility.SelectorVisible, Required = false, Enabled = true, Visible = true)]
    public virtual Boolean? DisplayTaskTotal
    {
        get
        {
            return this._DisplayTaskTotal;
        }
        set
        {
            this._DisplayTaskTotal = value;
        }
    }
    #endregion

    #region DisplayGrandTotal
    public abstract class displayGrandTotal : PX.Data.BQL.BqlBool.Field<displayGrandTotal> { }
    protected Boolean? _DisplayGrandTotal;
    [PXDBBool()]
    [PXDefault(false)]
    [PXUIField(DisplayName = "Display Grand Total", Visibility = PXUIVisibility.SelectorVisible, Required = false, Enabled = true, Visible = true)]
    public virtual Boolean? DisplayGrandTotal
    {
        get
        {
            return this._DisplayGrandTotal;
        }
        set
        {
            this._DisplayGrandTotal = value;
        }
    }
    #endregion
}

​​​​​​​2- Used PXFilter to set and get its Current values and use them in my Select Delegate

public PXFilter<UDCTPMCostProjectionFilter> CostProjectionFilter;

3- Added those fields to my screen

4- Write a Select Delegate to manupulate the output as follows

public IEnumerable details()
{
    var selectDetails = new PXSelect<PMCostProjectionLine,
        Where<PMCostProjectionLine.projectID, Equal<Current<PMCostProjection.projectID>>,
            And<PMCostProjectionLine.revisionID, Equal<Current<PMCostProjection.revisionID>>,
                And<Where<Current<UDCTPMCostProjectionFilter.taskID>, IsNull, Or<Current<UDCTPMCostProjectionFilter.taskID>, Equal<PMCostProjectionLine.taskID>>>>>>,
        OrderBy<Asc<PMCostProjectionLine.projectID,
            Asc<PMCostProjectionLine.taskID,
                Asc<PMCostProjectionLine_Extension.sortOrder,
                    Asc<PMCostProjectionLine.costCodeID,
                        Asc<PMCostProjectionLine.accountGroupID,
                            Asc<PMCostProjectionLine.inventoryID>>>>>>>>(Base);

    PXDelegateResult delegateResult = new PXDelegateResult();
    delegateResult.Capacity = 202;
    delegateResult.IsResultFiltered = false;
    delegateResult.IsResultSorted = true;
    delegateResult.IsResultTruncated = false;

    var view = new PXView(Base, false, selectDetails.View.BqlSelect);
    var startRow = PXView.StartRow;
    int totalRows = 0;

    var resultSet = view.Select(PXView.Currents, PXView.Parameters, PXView.Searches, PXView.SortColumns, PXView.Descendings, PXView.Filters, ref startRow, PXView.MaximumRows, ref totalRows);
    PXView.StartRow = 0;

    delegateResult.AddRange(resultSet.RowCast<PMCostProjectionLine>());

    if (IsCopyPaste == false)
    {
        if (IsDisplayTaskTotal() == true)
        {
            delegateResult.Clear();

            CostProjectionFilter.Current.TaskID = null;

            var tasksID = PXSelectJoinGroupBy<PMTask,
                InnerJoin<PMCostProjectionLine, On<PMTask.taskID, Equal<PMCostProjectionLine.taskID>>>,
                    Where<PMCostProjectionLine.projectID, Equal<Current<PMCostProjection.projectID>>,
                        And<PMCostProjectionLine.revisionID, Equal<Current<PMCostProjection.revisionID>>,
                            And<Where<Current<UDCTPMCostProjectionFilter.taskID>, IsNull, Or<Current<UDCTPMCostProjectionFilter.taskID>, Equal<PMCostProjectionLine.taskID>>>>>>,
                Aggregate<GroupBy<PMTask.projectID,
                    GroupBy<PMTask.taskID,
                        GroupBy<PMTask.taskCD,
                            GroupBy<PMTask.description>>>>>>
                .Select(Base).RowCast<PMTask>().ToList().Select(record => record.TaskID).Distinct();

            foreach (int taskID in tasksID)
            {
                selectDetails = new PXSelect<PMCostProjectionLine,
                    Where<PMCostProjectionLine.projectID, Equal<Current<PMCostProjection.projectID>>,
                        And<PMCostProjectionLine.revisionID, Equal<Current<PMCostProjection.revisionID>>,
                            And<Where<Current<UDCTPMCostProjectionFilter.taskID>, IsNull, Or<Current<UDCTPMCostProjectionFilter.taskID>, Equal<PMCostProjectionLine.taskID>>>>>>,
                    OrderBy<Asc<PMCostProjectionLine.projectID,
                        Asc<PMCostProjectionLine.taskID,
                            Asc<PMCostProjectionLine_Extension.sortOrder,
                                Asc<PMCostProjectionLine.costCodeID,
                                    Asc<PMCostProjectionLine.accountGroupID,
                                        Asc<PMCostProjectionLine.inventoryID>>>>>>>>(Base);

                PMTask task = PXSelect<PMTask, Where<PMTask.projectID, Equal<Required<PMTask.projectID>>, And<PMTask.taskID, Equal<Required<PMTask.taskID>>>>>.Select(Base, Base.Document.Current.ProjectID, taskID);

                CostProjectionFilter.Current.TaskID = taskID;

                view = new PXView(Base, false, selectDetails.View.BqlSelect);

                view.WhereAnd<Where<PMCostProjectionLine.taskID, Equal<Current<UDCTPMCostProjectionFilter.taskID>>>>();
                selectDetails.WhereAnd<Where<PMCostProjectionLine.taskID, Equal<Current<UDCTPMCostProjectionFilter.taskID>>>>();

                startRow = PXView.StartRow;
                totalRows = 0;
                resultSet = view.Select(PXView.Currents, PXView.Parameters, PXView.Searches, PXView.SortColumns, PXView.Descendings, PXView.Filters, ref startRow, PXView.MaximumRows, ref totalRows);
                PXView.StartRow = 0;

                delegateResult.AddRange(resultSet.RowCast<PMCostProjectionLine>());

                List<PMCostProjectionLine> viewResult = new List<PMCostProjectionLine>(selectDetails.Select().RowCast<PMCostProjectionLine>());

                PMCostProjectionLine viewTotal = viewResult.CalculateSumTotal(selectDetails.Cache);
                PMCostProjectionLine_Extension viewTotalExt = viewTotal.GetExtension<PMCostProjectionLine_Extension>();

                viewTotal.ProjectID = Base.Document.Current.ProjectID;
                viewTotal.RevisionID = Base.Document.Current.RevisionID;
                viewTotal.LineNbr = null;
                viewTotal.TaskID = taskID;
                viewTotal.CostCodeID = null;
                viewTotal.AccountGroupID = null;
                viewTotal.InventoryID = null;
                viewTotal.Description = PMMessages.TaskTotal + " - " + task.Description;
                viewTotal.UOM = null;
                viewTotal.Mode = null;
                viewTotal.BudgetedQuantity = decimal.Zero;
                viewTotal.ActualQuantity = decimal.Zero;
                viewTotal.UnbilledQuantity = decimal.Zero;
                viewTotal.Quantity = decimal.Zero;
                viewTotal.ProjectedQuantity = decimal.Zero;
                viewTotal.CompletedPct = decimal.Zero;

                viewTotalExt.SortOrder = 1;
                viewTotalExt.UsrCFComment = null;
                viewTotalExt.UsrCFLogCntr = 0;
                viewTotalExt.UsrCFBudgetCompletedPct = decimal.Zero;
                viewTotalExt.UsrCFProjectionCompletedPct = decimal.Zero;
                viewTotalExt.UsrCFForecastCompletedPct = decimal.Zero;

                delegateResult.Add(viewTotal);
            }

            CostProjectionFilter.Current.TaskID = null;
        }
    }

    return delegateResult;
}

 

5- and this is the final result. For styling the rows I have added some more codes to the Page .cs file that is irrelevent to this functionality so I didn’t include in this post.

 

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
D

4 replies

Yuriy Zaletskyy
Jr Varsity I
Forum|alt.badge.img+3

Can you please mention which Acumatica build you use? Sometimes answer may be version dependent. For example if Acumatica has second level extension, or your VAR/ISV has second/third level extension, which may hide/modify totals calculation.

 

https://acupowererp.com
Chris Hackett
aaghaei
Captain II
Forum|alt.badge.img+10
  • aaghaei
  • Author
  • Captain II
  • June 5, 2022

Hi @Yuriy Zaletskyy the version I’m on is 21R2 (21.205)

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
aaghaei
Captain II
Forum|alt.badge.img+10
  • aaghaei
  • Author
  • Captain II
  • June 7, 2022

@Yuriy Zaletskyy @Chris Hackett 
This is the output I was trying to get and after quite a bit of plying finally made it work. Of course in reality this is going to be very lengthy but this gives the feeling.

 

 

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Chris Hackett
aaghaei
Captain II
Forum|alt.badge.img+10
  • aaghaei
  • Author
  • Captain II
  • Answer
  • June 9, 2022

@Yuriy Zaletskyy @Chris Hackett 

Here is what I did to get this output.

1- I added a virtual DAC

//**************************************** PMCostProjectionFilter ****************************************//
[PXCacheName("Cost Projection Filter")]
[Serializable]
[System.Diagnostics.CodeAnalysis.ExcludeFromCodeCoverage]
public class UDCTPMCostProjectionFilter : IBqlTable
{
    #region TaskID
    public abstract class taskID : PX.Data.BQL.BqlInt.Field<taskID> { }
    protected Int32? _TaskID;
    [ProjectTask(typeof(PMCostProjection.projectID), DisplayName = "Task", AlwaysEnabled = true, DirtyRead = true, Visibility = PXUIVisibility.SelectorVisible, Required = false, Enabled = true, Visible = true)]
    public virtual Int32? TaskID
    {
        get
        {
            return this._TaskID;
        }
        set
        {
            this._TaskID = value;
        }
    }
    #endregion

    #region DisplayTaskTotal
    public abstract class displayTaskTotal : PX.Data.BQL.BqlBool.Field<displayTaskTotal> { }
    protected Boolean? _DisplayTaskTotal;
    [PXDBBool()]
    [PXDefault(false)]
    [PXUIField(DisplayName = "Display Task Total", Visibility = PXUIVisibility.SelectorVisible, Required = false, Enabled = true, Visible = true)]
    public virtual Boolean? DisplayTaskTotal
    {
        get
        {
            return this._DisplayTaskTotal;
        }
        set
        {
            this._DisplayTaskTotal = value;
        }
    }
    #endregion

    #region DisplayGrandTotal
    public abstract class displayGrandTotal : PX.Data.BQL.BqlBool.Field<displayGrandTotal> { }
    protected Boolean? _DisplayGrandTotal;
    [PXDBBool()]
    [PXDefault(false)]
    [PXUIField(DisplayName = "Display Grand Total", Visibility = PXUIVisibility.SelectorVisible, Required = false, Enabled = true, Visible = true)]
    public virtual Boolean? DisplayGrandTotal
    {
        get
        {
            return this._DisplayGrandTotal;
        }
        set
        {
            this._DisplayGrandTotal = value;
        }
    }
    #endregion
}

​​​​​​​2- Used PXFilter to set and get its Current values and use them in my Select Delegate

public PXFilter<UDCTPMCostProjectionFilter> CostProjectionFilter;

3- Added those fields to my screen

4- Write a Select Delegate to manupulate the output as follows

public IEnumerable details()
{
    var selectDetails = new PXSelect<PMCostProjectionLine,
        Where<PMCostProjectionLine.projectID, Equal<Current<PMCostProjection.projectID>>,
            And<PMCostProjectionLine.revisionID, Equal<Current<PMCostProjection.revisionID>>,
                And<Where<Current<UDCTPMCostProjectionFilter.taskID>, IsNull, Or<Current<UDCTPMCostProjectionFilter.taskID>, Equal<PMCostProjectionLine.taskID>>>>>>,
        OrderBy<Asc<PMCostProjectionLine.projectID,
            Asc<PMCostProjectionLine.taskID,
                Asc<PMCostProjectionLine_Extension.sortOrder,
                    Asc<PMCostProjectionLine.costCodeID,
                        Asc<PMCostProjectionLine.accountGroupID,
                            Asc<PMCostProjectionLine.inventoryID>>>>>>>>(Base);

    PXDelegateResult delegateResult = new PXDelegateResult();
    delegateResult.Capacity = 202;
    delegateResult.IsResultFiltered = false;
    delegateResult.IsResultSorted = true;
    delegateResult.IsResultTruncated = false;

    var view = new PXView(Base, false, selectDetails.View.BqlSelect);
    var startRow = PXView.StartRow;
    int totalRows = 0;

    var resultSet = view.Select(PXView.Currents, PXView.Parameters, PXView.Searches, PXView.SortColumns, PXView.Descendings, PXView.Filters, ref startRow, PXView.MaximumRows, ref totalRows);
    PXView.StartRow = 0;

    delegateResult.AddRange(resultSet.RowCast<PMCostProjectionLine>());

    if (IsCopyPaste == false)
    {
        if (IsDisplayTaskTotal() == true)
        {
            delegateResult.Clear();

            CostProjectionFilter.Current.TaskID = null;

            var tasksID = PXSelectJoinGroupBy<PMTask,
                InnerJoin<PMCostProjectionLine, On<PMTask.taskID, Equal<PMCostProjectionLine.taskID>>>,
                    Where<PMCostProjectionLine.projectID, Equal<Current<PMCostProjection.projectID>>,
                        And<PMCostProjectionLine.revisionID, Equal<Current<PMCostProjection.revisionID>>,
                            And<Where<Current<UDCTPMCostProjectionFilter.taskID>, IsNull, Or<Current<UDCTPMCostProjectionFilter.taskID>, Equal<PMCostProjectionLine.taskID>>>>>>,
                Aggregate<GroupBy<PMTask.projectID,
                    GroupBy<PMTask.taskID,
                        GroupBy<PMTask.taskCD,
                            GroupBy<PMTask.description>>>>>>
                .Select(Base).RowCast<PMTask>().ToList().Select(record => record.TaskID).Distinct();

            foreach (int taskID in tasksID)
            {
                selectDetails = new PXSelect<PMCostProjectionLine,
                    Where<PMCostProjectionLine.projectID, Equal<Current<PMCostProjection.projectID>>,
                        And<PMCostProjectionLine.revisionID, Equal<Current<PMCostProjection.revisionID>>,
                            And<Where<Current<UDCTPMCostProjectionFilter.taskID>, IsNull, Or<Current<UDCTPMCostProjectionFilter.taskID>, Equal<PMCostProjectionLine.taskID>>>>>>,
                    OrderBy<Asc<PMCostProjectionLine.projectID,
                        Asc<PMCostProjectionLine.taskID,
                            Asc<PMCostProjectionLine_Extension.sortOrder,
                                Asc<PMCostProjectionLine.costCodeID,
                                    Asc<PMCostProjectionLine.accountGroupID,
                                        Asc<PMCostProjectionLine.inventoryID>>>>>>>>(Base);

                PMTask task = PXSelect<PMTask, Where<PMTask.projectID, Equal<Required<PMTask.projectID>>, And<PMTask.taskID, Equal<Required<PMTask.taskID>>>>>.Select(Base, Base.Document.Current.ProjectID, taskID);

                CostProjectionFilter.Current.TaskID = taskID;

                view = new PXView(Base, false, selectDetails.View.BqlSelect);

                view.WhereAnd<Where<PMCostProjectionLine.taskID, Equal<Current<UDCTPMCostProjectionFilter.taskID>>>>();
                selectDetails.WhereAnd<Where<PMCostProjectionLine.taskID, Equal<Current<UDCTPMCostProjectionFilter.taskID>>>>();

                startRow = PXView.StartRow;
                totalRows = 0;
                resultSet = view.Select(PXView.Currents, PXView.Parameters, PXView.Searches, PXView.SortColumns, PXView.Descendings, PXView.Filters, ref startRow, PXView.MaximumRows, ref totalRows);
                PXView.StartRow = 0;

                delegateResult.AddRange(resultSet.RowCast<PMCostProjectionLine>());

                List<PMCostProjectionLine> viewResult = new List<PMCostProjectionLine>(selectDetails.Select().RowCast<PMCostProjectionLine>());

                PMCostProjectionLine viewTotal = viewResult.CalculateSumTotal(selectDetails.Cache);
                PMCostProjectionLine_Extension viewTotalExt = viewTotal.GetExtension<PMCostProjectionLine_Extension>();

                viewTotal.ProjectID = Base.Document.Current.ProjectID;
                viewTotal.RevisionID = Base.Document.Current.RevisionID;
                viewTotal.LineNbr = null;
                viewTotal.TaskID = taskID;
                viewTotal.CostCodeID = null;
                viewTotal.AccountGroupID = null;
                viewTotal.InventoryID = null;
                viewTotal.Description = PMMessages.TaskTotal + " - " + task.Description;
                viewTotal.UOM = null;
                viewTotal.Mode = null;
                viewTotal.BudgetedQuantity = decimal.Zero;
                viewTotal.ActualQuantity = decimal.Zero;
                viewTotal.UnbilledQuantity = decimal.Zero;
                viewTotal.Quantity = decimal.Zero;
                viewTotal.ProjectedQuantity = decimal.Zero;
                viewTotal.CompletedPct = decimal.Zero;

                viewTotalExt.SortOrder = 1;
                viewTotalExt.UsrCFComment = null;
                viewTotalExt.UsrCFLogCntr = 0;
                viewTotalExt.UsrCFBudgetCompletedPct = decimal.Zero;
                viewTotalExt.UsrCFProjectionCompletedPct = decimal.Zero;
                viewTotalExt.UsrCFForecastCompletedPct = decimal.Zero;

                delegateResult.Add(viewTotal);
            }

            CostProjectionFilter.Current.TaskID = null;
        }
    }

    return delegateResult;
}

 

5- and this is the final result. For styling the rows I have added some more codes to the Page .cs file that is irrelevent to this functionality so I didn’t include in this post.

 

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
D
Chris Hackett
Yuriy Zaletskyy
Terms & ConditionsCookie settingsAccessibility statement
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved