Filter/Group By from a different list and collection

(0) Share

Report

Posted on by syhrh

997 Super User 2024 Season 1

Hi, I have this collection, NewSubordinates that is filtered according to the Manager's Name.

I need to check who's the approver(s) for each of the subordinates inside the collection. To check the subordinates' approver(s), it is in another list, the Employee list.

Name, Manager's Name, Approver Level 1 and Approver Level 1 are people columns.

I also need to check whether the subordinates have 1 or more approvers to create the approval for the specific subordinate.

//App.OnStart
With(
 {
 ApproverName:
 Filter(
 Employee,
 'Approver Level 1' And 'Approver Level 2'
 ),
 EmpName:
 Filter(
 NewSubordinates, Requester.DisplayName)
 },
 
 First(ForAll(
 ApproverName As aCol,
 Collect(
 Newcollect,
 Filter(
 EmpName,
 Requester.DisplayName = aCol.'Approver Level 1' && aCol.'Approver Level 2'
 )
 )
 )
));

I tried this formula but it doesn't work. How to get the who's the approver(s) for the subordinates?

Thank you.

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

BCBuizer 22,510 Super User 2025 Season 2 on at

Like (0)

Report

HI @syhrh ,

Can you please share the code you use to create the NewSubordinates collection? I'm sure it will be easier to add a column / columns that have the approver data in there than it is to create two different collections.

Was this reply helpful? Yes No
syhrh 997 Super User 2024 Season 1 on at

Like (0)

Report
@BCBuizer
//App.OnStart With( { wSub: Filter( Employee, 'Manager''s Name'.Email = User().Email ), wLeave: Sort( Test, ID, Descending ) }, First(ForAll( wSub As aCol, Collect( NewSubordinates, Filter( wLeave, Requester.DisplayName = aCol.Name.DisplayName && Status.Value="Pending" ) ) ) ));

Was this reply helpful? Yes No
BCBuizer 22,510 Super User 2025 Season 2 on at

Like (1)

Report
Hi @syhrh ,

The below modified code will add two columns to the NewSubordinates collection: "Approver Level 1" and "Approver Level 2" for which the value is pulled from aCol (= wSub) = Employee)).

//App.OnStart With( { wSub: Filter( Employee, 'Manager''s Name'.Email = User().Email ), wLeave: Sort( Test, ID, Descending ) }, First(ForAll( wSub As aCol, Collect( NewSubordinates, AddColumns( Filter( wLeave, Requester.DisplayName = aCol.Name.DisplayName && Status.Value="Pending" ), "Approver Level 1", LookUp(aCol, Requester.DisplayName = aCol.Name.DisplayName, 'Approver Level 1') "Approver Level 2", LookUp(aCol, Requester.DisplayName = aCol.Name.DisplayName, 'Approver Level 2') ) ) ));

Was this reply helpful? Yes No
syhrh 997 Super User 2024 Season 1 on at

Like (0)

Report

@BCBuizer
I got this error

Was this reply helpful? Yes No
Verified answer

BCBuizer 22,510 Super User 2025 Season 2 on at

Like (1)

Report
Hi @syhrh ,

//App.OnStart With( { wSub: AddColumns( Filter( Employee, 'Manager''s Name'.Email = User().Email ), "EmployeeDisplayName", Name.DisplayName ), wLeave: AddColumns( Sort( Test, ID, Descending ), "RequesterDisplayName", Requester.DisplayName }, ForAll( wSub, Collect( NewSubordinates, AddColumns( Filter( wLeave, RequesterDisplayName = EmployeeDisplayName && Status.Value="Pending" ), "Approver Level 1", LookUp(wSub, EmployeeDisplayName = RequesterDisplayName, 'Approver Level 1') "Approver Level 2", LookUp(wSub, EmployeeDisplayName = RequesterDisplayName, 'Approver Level 2') ) ) );

Was this reply helpful? Yes No
BCBuizer 22,510 Super User 2025 Season 2 on at

Like (0)

Report

Hi @syhrh ,

Did my post resolve your issue? If yes, please mark it as the solution for the benefit of the community. If not, please let me know what you are running into.

Was this reply helpful? Yes No
syhrh 997 Super User 2024 Season 1 on at

Like (1)

Report

@BCBuizer Sorry for the late reply. Yes, it does what I need. Thank you!

Was this reply helpful? Yes No