Get sum of records from one collection to an additional column in another collection

[stefansc]

Hi all,

I have a problem to add a column to a collection containing a CountRows from another collection.

There are two collections:

colOrders

ClearCollect(
    colOrders,
    Sort(
        Distinct(
            Orders,
            OrderNumber
        ),
        ThisRecord.Result,
        Ascending
    )
);

This results in a collection like:

Result:

2022-123

2022-124

2022-125

colOrderDetails

ClearCollect(
    colOrderDetails,
    OrderDetails
);

This results in a collection like:

OrderNumber	OrderDetail
2022-123	Order detail 1
2022-123	Order detail 2
2022-124	Order detail 3
2020-125	Order detail 4

I want to add a new column "Counter" to colOrders which counts the number of order details, so in this example it should look like:

Result	Counter
2022-123	2
2022-124	1
2022-125	1

I already tried the following code:

ClearCollect(
    tempOrders,
    colOrders,
        AddColumns(
        colOrderss,
        "Counter",
        0)

);
ForAll(
    tempEnvironments,
    Counter = CountRows(
        Filter(
            colOrderDetails,
            ThisRecord.OrderNumber = Result
        )
    )
)

But even in the first ClearCollect, the column is added but without any value.

Also in the ForAll statement, it doesn't work.

Any idea woul be appreciated.

Best

Stefan

[WarrenBelz]

Hi @stefansc ,

Try this

ClearCollect(
   tempOrders,
   AddColumns(
      GroupBy(
         "OrderNumber",
         "Data"
      ),
      "Counter",
      CountRows(Data)
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[stefansc · Accepted solution]

HI,

this works:

ClearCollect(
    tempOrders,
    AddColumns(
        GroupBy(
            OrderDetails,
            "Ordernumber",
            "Orderdetails"
        ),
        "Counter",
        CountRows(ThisRecord.Orderdetails)
    )
)

[WarrenBelz]

Hi @stefansc ,

Yes - another version of the same structure.