Filter a collection while looping another collection

(0) Share

Report

Posted on by syhrh

997 Super User 2024 Season 1

Hi,

I have created a collection that is filtered by the Manager's Name in the Sharepoint list. Then I would like to create a new collection that filters the Requester column which is a people column in the Leave Request list and match it with the Name in the subordinates collection.

I tried using this formula but has errors.

ClearCollect(subordinates,Filter(Employee,'Manager''s Name'.Email=CurrentUser));
ForAll(subordinates,
Collect(NewSubordinates,Filter('Leave-Request',Requester.DisplayName=LookUp(subordinates,Name.DisplayName=Requester.DisplayName))));

Can someone please help me?

Thank you.

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

Sort by

WarrenBelz 156,409 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@syhrh ,

Try this

With( { wSub: Filter( Employee, 'Manager''s Name'.Email = CurrentUser ) }, ForAll( wSub As aCol, Collect( NewSubordinates, Filter( 'Leave-Request', Requester.DisplayName = aCol.Name.DisplayName ) ) ) );

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.

Visit my blog Practical Power Apps

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

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz It works but it also creates many duplicates and add up into the collection

Was this reply helpful? Yes No
WarrenBelz 156,409 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @syhrh ,

That is probably more an issue with your data . You only really have two choices - multiple (as posted previously) or single (the first) matches in your List - single as below

With( { wSub: Filter( Employee, 'Manager''s Name'.Email = CurrentUser ) }, ForAll( wSub As aCol, Collect( NewSubordinates, LookUp( 'Leave-Request', Requester.DisplayName = aCol.Name.DisplayName ) ) ) );

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.

Visit my blog Practical Power Apps

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

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz
it works but it has delegation issues

Was this reply helpful? Yes No
Verified answer

WarrenBelz 156,409 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@syhrh

Assuming that you will never need to look in more than the newest 500 (or whatever your Delegation limit is) records in 'Leave Request'

With( { wSub: Filter( Employee, 'Manager''s Name'.Email = CurrentUser ), wLeave: Sort( 'Leave-Request', ID, Descending ) }, ForAll( wSub As aCol, Collect( NewSubordinates, LookUp( wLeave, Requester.DisplayName = aCol.Name.DisplayName ) ) ) )

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.

Visit my blog Practical Power Apps

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

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz Can I use Filter instead? If I use LookUp, there are some rows from SharePoint that are not retrieved.

Was this reply helpful? Yes No
WarrenBelz 156,409 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@syhrh ,

Yes, you can use Filter, but as you said earlier, there may be duplicates.

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.

Visit my blog Practical Power Apps

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

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz If I put First, will there be no duplicates?

Was this reply helpful? Yes No
WarrenBelz 156,409 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@syhrh ,

No - First is exactly the same result as LookUp.

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

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz Oh, okay then. Thank you.

Was this reply helpful? Yes No