Using ForAll to count qualifying records in a table

(0) Share

Report

Posted on by Martin_W

373

Hi,

In our system which manages equipment, there is a Dataverse table of equipment called Assets, and each item of equipment is assigned an EquipmentCategory, which is a lookup value from another table called Categories. The Categories table is a simple table which contains the category title and the annual maintenance contract value e.g

Column headings: Title, AnnualCost

record values:

"cat 1", 50

"cat 2", 75

"cat 3", 100

etc.

I would like to be able to iterate through the Categories table, and for each category, determine how many records in Assets there are for each category, resulting in a collection e.g.

"cat 1", 2

"cat 2", 4

However, I see that CountIf can't be used within a ForAll function and so I'm stuck now as to how to achieve this neatly. I don't want to have a line of code for each category, as this means (a) it's inefficient and inelegant and (b) if any categories are added in the future I'll need to change the code.

Any help greatly appreciated!

thanks

Martin

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (2)

Sort by

Verified answer

iAm_ManCat 18,256 Most Valuable Professional on at

Like
a
(2)

Report
Copy link

Link copied!
Based on what you've said, you want to show how many Assets are in each category type, the below pseudocode will need some tweaking to work with your Assets table and the Category lookup, but its a good starting point

Clear(Categorytotals); ForAll( Distinct(Categories, Title) As CVal, Collect(Categorytotals, { Category: CVal.Value, NumberOfAssets: CountRows(Filter(Assets, Category.Title=Cval.Value)) } ) )

Was this reply helpful? Yes No
Gochix 1,937 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
The only thing I would change here is how the ForAll function is performed.

Collect( CategoryTotals, ForAll( Distinct( Categories, Title) As CVal, { Category: CVal.Value, NumberOfAssets: CountRows(Filter(Assets, Category.Title = CVal.Value)) } ) )

_____________________________________________________________________________________
Please give a thumbs up if I resolved your issue! Please click Accept as Solution to close the topic!

Was this reply helpful? Yes No
Martin_W 373 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

Unfortunately I get a "The specified column is not accessible in this context" error, but this line works outside of the ForAll function.

thanks,
Martin

Was this reply helpful? Yes No
timl 37,283 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Martin_W

Whilst this doesn't answer your question, you could consider implementing this calculation at the Dataverse level using a rollup column on the Categories table.

It'll be a bit more efficient as you won't need to retrieve the Assets within the ForAll just to count them. It would also provide some additional filtering capabilites should you need it at a later time (eg, to call Filter on the categories table to return category records with 0 assets).

Was this reply helpful? Yes No
Verified answer

Martin_W 373 on at

Like
a
(1)

Report
Copy link

Link copied!

The fix for the "specified column is not accessible in this context" is to enable the option for "Record scope one-to-many and many-to-many relationships"

Was this reply helpful? Yes No
Martin_W 373 on at

Like
a
(1)

Report
Copy link

Link copied!

Thanks @iAm_ManCat

The CountRows method has been the solution, although I had to enable the option that I mentioned in another reply.

Many thanks
Martin

Was this reply helpful? Yes No