Skip to main content
Solved

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


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.

 

View original
Did this topic help you find an answer to your question?

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.

 


aaghaei
Captain II
Forum|alt.badge.img+10
  • Author
  • Captain II
  • 1205 replies
  • June 5, 2022

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


aaghaei
Captain II
Forum|alt.badge.img+10
  • Author
  • Captain II
  • 1205 replies
  • 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.

 

 


aaghaei
Captain II
Forum|alt.badge.img+10
  • Author
  • Captain II
  • 1205 replies
  • 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.

 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings