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)

WarrenBelz 153,079 Most Valuable Professional on at

Like (0)

Report
@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 (0)

Report

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

Was this reply helpful? Yes No
WarrenBelz 153,079 Most Valuable Professional on at

Like (0)

Report
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 (0)

Report

@WarrenBelz
it works but it has delegation issues

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,079 Most Valuable Professional on at

Like (0)

Report
@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 (0)

Report

@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 153,079 Most Valuable Professional on at

Like (0)

Report

@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 (0)

Report

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

Was this reply helpful? Yes No
WarrenBelz 153,079 Most Valuable Professional on at

Like (0)

Report

@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 (0)

Report

@WarrenBelz Oh, okay then. Thank you.

Was this reply helpful? Yes No