Dinamically add groupby in view Delegate

Hi @anahizentella94 

You can certainly use group by statements within a view delegate. Here's how you can do it:

1. BQL Fluent API (FBQL): The newer FBQL allows you to construct a query in a more procedural and flexible manner, which is very useful when building dynamic queries in view delegates.

2. PXSelectGroupBy and PXGroupBy: If you're using the traditional BQL, you can leverage these attributes and classes for grouping your results.

Here's a quick demonstration using traditional BQL:

public PXSelectGroupBy<ARInvoice, 
    Where<ARInvoice.released, Equal<True>>,
    Aggregate<
        GroupBy<ARInvoice.customerID, 
        Sum<ARInvoice.curyOrigDocAmt>>> ARInvoicesGrouped;

If you're building the query dynamically within the delegate, you'd use something like:

public PXSelect<ARInvoice> ARInvoices;

protected virtual IEnumerable aRInvoices()
{
    var currentDocument = SomeFilter.Current; //Assuming you're using a filter DAC

    PXSelectBase<ARInvoice> query = new PXSelectJoinGroupBy<ARInvoice, 
        LeftJoin<Customer, On<ARInvoice.customerID, Equal<Customer.bAccountID>>>,
        Where<ARInvoice.released, Equal<True>>,
        Aggregate<
            GroupBy<ARInvoice.customerID, 
            Sum<ARInvoice.curyOrigDocAmt>>>>(this);

    // You can add conditions based on your filter/view delegate logic
    if(currentDocument.SomeField == someCondition)
    {
        query.WhereAnd<Where<ARInvoice.docType, Equal<Current<FilterDAC.docType>>>>();
    }

    foreach(PXResult<ARInvoice, Customer> record in query.Select())
    {
        ARInvoice invoice = record;
        // Do any additional processing if necessary

        yield return invoice;
    }
}

• After the GroupBy statement in your BQL, any aggregate function like Sum, Avg, Min, Max, etc. will perform the operation on each group separately.

• Ensure that any fields you want to use in a GroupBy are not marked with the PXDBCalced attribute in your DAC, as calculated fields may cause issues when used in grouping.

BQL Fluent API (FBQL)

Firstly, ensure that you have the necessary namespaces included:

using PX.Data.BQL;
using PX.Data.BQL.Fluent;

public SelectFrom<ARInvoice>
    .Where<ARInvoice.released.IsEqual<True>>
    .GroupBy<ARInvoice.customerID>
    .Aggregate<Sum<ARInvoice.curyOrigDocAmt>>.View ARInvoicesGrouped;

For the view delegate with conditions:

protected virtual IEnumerable aRInvoicesGrouped()
{
    var currentDocument = SomeFilter.Current; // Assuming you have a filter DAC named SomeFilter

    var query = SelectFrom<ARInvoice>
        .InnerJoin<Customer>.On<ARInvoice.customerID.IsEqual<Customer.bAccountID>>
        .Where<ARInvoice.released.IsEqual<True>>;

    if (currentDocument.SomeField == someCondition)
    {
        query.WhereAnd<ARInvoice.docType.IsEqual<SomeFilter.docType.FromCurrent>>();
    }

    query.GroupBy<ARInvoice.customerID>
         .Aggregate<Sum<ARInvoice.curyOrigDocAmt>>();

    foreach (ARInvoice invoice in query.View.SelectMultiBound(new object[] { currentDocument }))
    {
        // Do any additional processing if necessary

        yield return invoice;
    }
}

The FBQL allows you to build and modify your query step by step. You can use .WhereAnd<> or .WhereOr<> methods to add more conditions to the query dynamically based on the runtime data or business logic.

This should help you achieve grouping within a view delegate in Acumatica.

Thanks @davidnavasardyan09 @zfebert56 ,

It worked, after adding a few Where<> conditions I was able to add the Group by before executing the query. 

     var bqlCommand = SelectCommand.View.BqlSelect;
            var newCommand = bqlCommand.AggregateNew
                    <Aggregate
                        <GroupBy<ARSiteStatusSelected.inventoryID,
                            GroupBy<ARSiteStatusSelected.subItemID,
                                GroupBy<ARSiteStatusSelected.siteID>>>>>();

            PXView resultList = new PXView(Base, true, newCommand);

  List<object> result = resultList.Select(PXView.Currents, PXView.Parameters,
                       PXView.Searches, PXView.SortColumns, PXView.Descendings,
                       filterRows.ToArray(), ref startrow, int.MaxValue, ref totalrow);