Solved

BQL: Group by Month

  • 5 December 2023
  • 4 replies
  • 97 views
M
Rank
Userlevel 1
Badge 
var openSalesOrders = SelectFrom<SOLine>
    .InnerJoin<SOOrder>.On<SOLine.orderNbr.IsEqual<SOOrder.orderNbr>
        .And<SOOrder.orderType.IsEqual<SOLine.orderType>>>
    .InnerJoin<InventoryItem>.On<SOLine.inventoryID.IsEqual<InventoryItem.inventoryID>>
    .InnerJoin<INItemClass>.On<InventoryItem.itemClassID.IsEqual<INItemClass.itemClassID>>
    .Where<INItemClass.itemClassCD.IsEqual<@P.AsString>
        .And<SOLine.openQty.IsGreater<Zero>>
        .And<SOLine.operation.IsEqual<@P.AsString>>>
    .View.Select(this, "811", "I");

Is there a way to add a GroupBy to the above query to group and sum all sales order lines by Month & Year?

Coming from Python and Django in particular, something similar to Trunc is what I am looking to do.

Does BQL offer anything similar?

icon

Best answer by vardan22 22 December 2023, 09:56

View original

4 replies

Vignesh Ponnusamy
Rank
Userlevel 7
Badge +4

Hi @manley75,

You need use AggregateTo along with the GroupBy clause.

Following documentation has the explanation and some examples that you can refer to,

Good Luck,

#VP
M
Rank
Userlevel 1
Badge

Hi @manley75,

You need use AggregateTo along with the GroupBy clause.

Following documentation has the explanation and some examples that you can refer to,

Good Luck,

 

I know about and have used the .AggregateTo<GroupBy<>> in my queries before but I have not found a way to have it group by month & year. If I .AggregateTo<GroupBy<SOLine.shipDate» it will group by the day, not the month the day is in.

Basically if the shipDate was 10/18/23 I would want it to group it along with every other SOLine in October and give it a new consolidated date of 10/01/23.

Chris Hackett
Rank
Userlevel 7
Badge

Hi @manley75 were you able to find a solution? Thank you!

Chris ~ Let us know how we're doing: https://forms.office.com/r/xmJUdZ1QE5 - A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.
vardan22
Rank
Userlevel 4
Badge +1

You can try this in you group by  

Aggregate<GroupBy<DatePart<DatePart.month, DAC_DATE_FIELD>>>

 

Best Regards, Vardan Vardanyan.

Reply


Terms & ConditionsCookie settings
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