Answered

How to filter a list for items referenced in another list

(0) Share

Report

Posted on by bouillons

302

Hi, I have a somewhat complex data structure in sharepoint where I have a main list, for example Points of Sales with basic data such as address, etc... I have another list where the Item ID of the first list is a field and that contains data about the sales management, one field being the Area Manager. I want to query the first list for all items where the Area Manager is X.

For example main list contains:

ID	POSName	POSAddress	POSCity	POSZip
1	POS A	POS Address A	POS City A	POS Zip A
2	POS B	POS Address B	POS City B	POS Zip B
3	POS C	POS Address C	POS City C	POS Zip C
4	POS D	POS Address D	POS City D	POS Zip D
5	POS E	POS Address E	POS City E	POS Zip E
...

And the Sales Management list:

Title	POSID	AreaManager	POSManager
North	1 (points to POS A)	John	Julie
North	4 (points to POS D)	John	Patrick
East	2 (points to POS B)	Adam	James
North	5 (points to POS E)	John	Mark
...

So I want to query the main list where the area manager is John, and it should return records 1, 2 and 5 ... In other words, query the main list for all items for which an item in the sales list (Sales.POSID = Main.ID) exists where the area manager is John.

Stephane

Categories:

Building Power Apps

All responses (5)

Answers (1)

Sort by

WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @bouillons ,

Firstly the With() statement is only to avoid the Delegation Warning you will get on the Relational Lookup and is also assuming your Sales Management list item numbers are under your (500-2000) Delegation limit.

With( {wSales:SalesManagementList}, Filter( MainList, ID=LookUp( wSales, AreaManager=YourManagerValue ).POSID ) )

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.
bouillons 302 on at

Like
a
(0)

Report
Copy link

Link copied!

You rock ! Thanks for your time and effort Warren. This relational lookup is new to me. The only thing that is not yet as expected, is that it only returns the first matching record. In other words and to continue on the example, when looking for John, my result gallery only shows the first Point of Sales (1). I was expecting the 1, 2 and 5 records to show up.
Stephane
Verified answer

WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Thanks @bouillons,

You are correct - the LookUp will return 1 item - I cannot test this presently, but try

With( {wSales:SalesManagementList}, Filter( MainList, ID in Filter( wSales, AreaManager=YourManagerValue ).POSID ) )

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.
bouillons 302 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks again for your time and effort Darren, that worked and solved my problem in this scenario.
For my education, do you know if there is a delegated way of achieving the same on sharepoint ?
Stephane
WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @bouillons ,

I have a blog on Delegation that sets out some of the workarounds (and explains what Delegation is) that may be suitable, although relational lookups are simply not Delegable.

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.