Unanswered

Getting a CountRows within 1 table for each item in a collection

(1) Share

Report

Posted on by mhomol

122

I have Dataverse tables that are separated by a few degrees relationship-wise, but need to be able to get a count of rows in one table for each item in a collection. Can I do a CountRows(Filter) call within a ForAll() function? I'm assuming this would not be the recommended approach for performance reasons, so what kind of pattern should I try to follow?

Categories:

Building Power Apps

I have the same question (1)

All responses (5)

Answers (1)

ivan_apps 2,187 Moderator on at

Like (2)

Report

Are you trying to do anything else with the count on the related data or just display it in a gallery?

you could put a text label on a gallery and do a Counts(Filter(your_table, this item.id=your relationship))
this should display it on the gallery.

Was this reply helpful? Yes No
mhomol 122 on at

Like (2)

Report

So I was trying to use the DetailsList component from CreatorKit, since it gives me more tabular functionality OOTB, but the gallery is an interesting call, so I may just have to try this out and see if that gets me where I want to be then might have to bite the bullet and build out some tabular functionality for a gallery instead.

Was this reply helpful? Yes No
Verified answer

Static 118 on at

Like (2)

Report

You can use CountRows(Filter()) within a ForAll() function in Power Apps, but you're right that this might not be optimal for performance, especially if you're dealing with large datasets. Each call to CountRows(Filter()) within ForAll() would result in multiple data retrieval operations, which can slow down your app.

Load your data into collections to reduce multiple calls to Dataverse.
ClearCollect(MyTable1Collection, MyTable1);
ClearCollect(MyTable2Collection, MyTable2);
Group and Count:
Use GroupBy and AddColumns to create a summarized collection with counts.
ClearCollect(
SummaryCollection,
AddColumns(
GroupBy(MyTable1Collection, "GroupColumn", "GroupedItems"),
"ItemCount",
CountRows(Filter(MyTable2Collection, RelatedColumn in GroupedItems))
)
);
Display the Summary:
Now you can use SummaryCollection to display the counts or further process them.
Gallery.Items = SummaryCollection

Was this reply helpful? Yes No
ivan_apps 2,187 Moderator on at

Like (2)

Report

Yeah the creator kit is very nice looking but if I recall correctly it’s all JSON configuration so you may not be able to implement PowerFX within the details list config.

another alternative is creating a rollup field in Dataverse. You can have it count all the sub-items and it should work with the caveat that it’s not an instant roll-up. It’s scheduled a couple of times a day so if you wanted and instant roll-up you’d have to implement it custom.

Was this reply helpful? Yes No
mhomol 122 on at

Like (1)

Report

Thank you to everyone who responded. I ended up following @statc 's guidance and loaded information into separate collections, where one of them had the grouping all setup and the other collection was able to simply read in the CountRows from the grouped collection. So far, so good!

Was this reply helpful? Yes No