Filter List based on values from another List

(0) Share

Report

Posted on by Sam70

271

Hello,

I have 2 SharePoint lists (List A and List B)

I need output in a gallery as

the code in gallery Items is Filter(ListA, Product.Value = "A"). Works perfect but the catch is List B has multiple Products

Note* - Product in ListA is of column Type Choice and that in ListB is Text column. Hence need help.

regards

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

WarrenBelz 153,079 Most Valuable Professional on at

Like (1)

Report
Hi @Sam70 ,
Based on what you posted, to would be something like this - note that this is not Delegable

Filter( ListA, Product.Value in ListB.Product )

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.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Sam70 271 on at

Like (0)

Report

Hi @WarrenBelz ,

Yes! The Filter query is not delegable. Can you suggest something which can be delegable?

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,079 Most Valuable Professional on at

Like (0)

Report

Hi @Sam70 ,

If there was a Delegable solution, I would have posted that instead. The in Filter simply is not Delegable. The only chance you have is to "pre-filter" (see this blog of mine) to get your data set numbers under your Delegation limit - you would need another relevant criteria for this to be possible.

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.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
AmyWaggoner 27 on at

Like (0)

Report

Hey there - Thanks for this clarification. I've been looking around and can't see an example for how to take the formula the OP was using and add the With() or ClearCollect(). Do you have the syntax for just adding in this example of comparing two column values from two data sources?

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

Like (0)

Report

@AmyWaggoner ,

I am not clear on your question - the blog I referred to below shows the With() statement use.

Was this reply helpful? Yes No
AmyWaggoner 27 on at

Like (0)

Report
@WarrenBelz I read through your blog post and am trying to follow this piece:

Pre-filtering
As an example, if my list was 10,000 records, but if after Filtering to show only the Status values Planned and In Progress, the result was always less than 2,000 records, I could then use Search (a non-Delegable operator) to look for the content of Text input SearchBox.Text in both the fields FirstName andLastName. This would work perfectly, and no Delegation warning would be received.

With( { _Status: Filter( MyList, Status = "Planned" || Status = "In Progress" ) }, Search( _Status, SearchBox.Text, "FirstName", "LastName" ) )
I've amended the code (using your previous generic example) to this:

With( { Filter( ListA, Product.Value in ListB.Product ) } )

...Which is still throwing the delegable error.

I'm assuming I need to add in an additional element to overcome the "in" issue, but I don't know what it would be.

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

Like (0)

Report

HI @AmyWaggoner ,

That is because neither the in Filter nor the Relational query to the second list are Delegable. There is no "magic bullet" here unless you can get your record numbers down with a completely Delegable query.

Was this reply helpful? Yes No
AmyWaggoner 27 on at

Like (0)

Report

@WarrenBelz Okay, I'm understanding now. In your blog, the Filter was delegable, but in this example it's not because of the "in." So anything else that would be a delegable Filter for ListA should be added.

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

Like (0)

Report

@AmyWaggoner ,

Yes - you need to get your dataset processed and returned server-side (a Delegable query) under your Data Row Limit and then you can process anything you want client-side (done by Power Apps) from this.

Was this reply helpful? Yes No