web

You’re offline. This is a read only version of the page.

News and Announcements icon

Power Platform CommunityForums Power Apps Grouped Collection wit...

Power Apps

Unanswered

Grouped Collection with Sum from another table

(0) Share

Report

Report

Posted on by meridian59

9

Hi guys,

I've been stuck on this issue for a while now and was hoping someone might be able to help!

I've got two tables, one for Production hours with Employee names etc and another with Production Quantities.

Once users submit all the data, I was then trying to join the two, Grouped by Date and attempting to sum all the quantities from the other table against each Date row so someone can review it at a summarised level.

The offending collection!:

But actually on the other table there's only QTY 200 on the 23/01/2024, not 200 on both dates:

Here is the code used:

ClearCollect(

QuantitiesScreenCollection,

AddColumns(

GroupBy(

Filter(ProductionHours, WorksOrder = DataCardValue15_1.Selected.WorksOrder),

"cr557_productiondate",

"GroupedDate"

),

"SumOfPackingHours",

Sum(

GroupedDate,

PackingHours

),

"SumOfCleaningHours",

Sum(

GroupedDate,

CleaningHours

),

"SumOfSetUpHours",

Sum(

GroupedDate,

SetUpHours

),

"SumOfQuantity",

Sum(

Filter(

ProductionQTIES,

ProductionDate = cr557_productiondate,

WorksOrder = DataCardValue15_1.Selected.WorksOrder

),

QTYProduced

)

)

)

If someone could help, that would be brilliant! Even ChatGPT hasn't worked..

Categories:

Building Power Apps

All responses Img

All responses (2)

Answers Img

Answers (0)

Sort by

v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @meridian59 ,

I cannot find the reason from your codes. If you create another collection with less added columns, would it be different?

ClearCollect( colTest, AddColumns( GroupBy( Filter(ProductionHours, WorksOrder = DataCardValue15_1.Selected.WorksOrder), "cr557_productiondate", "GroupedDate" ), "SumOfQuantity", Sum( Filter( ProductionQTIES, ProductionDate = cr557_productiondate, WorksOrder = DataCardValue15_1.Selected.WorksOrder ), QTYProduced ) ) )

I suspect it's due to ambiguity or mismatch in date time values. Please try below codes:

ClearCollect( QuantitiesScreenCollection, AddColumns( AddColumns( GroupBy( Filter(ProductionHours, WorksOrder = DataCardValue15_1.Selected.WorksOrder), "cr557_productiondate", "GroupedDate" ), "SumOfPackingHours", Sum( GroupedDate, PackingHours ), "SumOfCleaningHours", Sum( GroupedDate, CleaningHours ), "SumOfSetUpHours", Sum( GroupedDate, SetUpHours ) ) As New, "SumOfQuantity", Sum( Filter( ProductionQTIES, ProductionDate = DateValue(New.cr557_productiondate,"en-US"), WorksOrder = DataCardValue15_1.Selected.WorksOrder ), QTYProduced ) ) )

Best regards,
WarrenBelz 156,532 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @meridian59 ,

Firstly @v-jefferni is correct that there is nothing technically wrong with your code. This may be worth a try to get all the major calculations out of the main filter

With( { _Hours: GroupBy( Filter( ProductionHours, WorksOrder = DataCardValue15_1.Selected.WorksOrder ), "cr557_productiondate", "GroupedDate" ), _Qty: Filter( ProductionQTIES, WorksOrder = DataCardValue15_1.Selected.WorksOrder ) }, ClearCollect( QuantitiesScreenCollection, AddColumns( _Hours, "SumOfPackingHours", Sum( GroupedDate, PackingHours ), "SumOfCleaningHours", Sum( GroupedDate, CleaningHours ), "SumOfSetUpHours", Sum( GroupedDate, SetUpHours ), "SumOfQuantity", Sum( Filter( _Qty, ProductionDate = cr557_productiondate ), QTYProduced ) ) ) )

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.

MVP (Business Applications) Visit my blog Practical Power Apps

Join the conversation