GroupBy aggregate with record count in projection

2019-09-16 05:47发布

I'm currently working on a processing screen that should be showing a listing of custom orders from my DAC summarized by orderid.

The table itself stores a full listing of the order line items.

For example: OrderNbr, OrderDate, LineNbr, Desc, InvtId, Qty, ExtPrice, isValidated

The grid of the processing screen should show the following information OrderNbr, OrderDate, Desc, Count(LineNbr), Sum(ExtPrice)

I've created a projection DAC to group the lines by OrderNbr/OrderDate

[PXProjection(typeof(Select4<CWOrderTable,
    Where<CWOrderTable.isValidated,Equal<boolTrue>>,
    Aggregate<
         GroupBy<CWOrderTable.orderNbr,
         GroupBy<CWOrderTable.isValidated,
         Sum<CWOrderTable.ExtPrice>>>>>
public partial class CWOrderSummary : IBqlTable

What I'm not able to get is a count of the line items that composes this order. I attempted to add a Count<> aggregate to the above however that doesn't return an actual count, simply the MAX() of the field. I couldn't find a great example of using Count() in a projection DAC.

I also found the article on the asiablog regarding the ScalarCount function and attempted that.

article here: http://asiablog.acumatica.com/2016/05/scalarcount-bql-operator.html

There are no errors using this however the subquery never is executed/passed to SQL and the value is always null.

Anyone have any recommendations or examples on how to get the count of records this way?

This is using Acumatica 6.10.0010. I'm partially wondering if it's a build issue and we need to force the customer to upgrade.

Thanks

标签: acumatica
0条回答
登录 后发表回答