Counting Related records

(0) Share

Report

Posted on by Mattw112b

1,088

I have a PowerApp with three galleries.

The first gallery (Collection1) I want to display the count of items in the third gallery (Collection3) with a certain status.

They are related through Collection2.

I've tried to represent this below:

I've tried a number of things, but I can figure out the formula to get the relations and then count them?

Can anyone advise?

Thanks,

Terry

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Sort by

WarrenBelz 154,930 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @Mattw112IG ,
Firstly the syntax for counting matching rows of a gallery is
CountRows( Filter( Gallery3Name.AllItems, Status=WhateverTest ) )
You are going to need some sort of nested lookup - happy to help if you get stuck.

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.

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Mattw112IG ,

Could you please share a bit more about your data source (Collection1, Collection2, Collection3)? Are they all SP Lists?

Could you please show more details about the "Status" column in your Collection3? Is it a Choice type column?

I have made a test on my side, please consider take a try with the following workaround:

Set the Items property of the first Gallery to following:

Collection1

Add two Labels within this Gallery, set the Text property of Label1 to following:

ThisItem.Name.DisplayName

set the Text property of Label2 to following:

"#Open Number: " & CountRows( Filter( Collection3, COL2ID in Filter(Collection2,NAME.DisplayName = ThisItem.NAME.DisplayName).COL2ID, Status.Value = "Open" ) )

Note: I assume that the Status column in your Collection3 (SP List) is a Choice type column.

If the Status column in your Collection3 is a Text type column, please modify above formula as below:

"#Open Number: " & CountRows( Filter( Collection3, COL2ID in Filter(Collection2, NAME.DisplayName = ThisItem.NAME.DisplayName).COL2ID, Status = "Open" ) )

Please consider take a try with above solution, check if the issue is solved.

Best regards,

Was this reply helpful? Yes No
Mattw112b 1,088 on at

Like (0)

Report
Copy link

Link copied!

Thanks guys I will try these tomorrow. But to answer a couple of your questions I updated this:

Thanks,
Terry

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Mattw112IG ,

Have you take a try with the solution I provided above?

Based on the needs that you mentioned, I think the solution I provided above could achieve your needs. Please take a try with it, check if the issue is solved.

Best regards,

Was this reply helpful? Yes No
Mattw112b 1,088 on at

Like (0)

Report
Copy link

Link copied!

I tried it along with a few iterations... keep getting circular reference errors?
Terry

Was this reply helpful? Yes No