Filter gallery based on 2nd SP list

[gmbeee]

Hi, I can’t seem to get my head around this. 

I have a SP with around 5000 items which will grow. One of the fields is a multi choice column. Let’s call this list nLISTdb

I need to filter this column, amongst others E and display filtered results in a gallery. 

I can pull all records into a large merged collection but this is having a hit on performance. So I’d like to delegate the filter to SP.

@RandyHayes suggested one route to be a 2nd SP list. 

I have the following field:

FLAG | recordID

Lets call this list nLISTflag

So when an item is created in nLISTdb, the choices I made for FLAGS are added individually into nLISTflag with the item ID from nLISTdb

So if an item is created like this in nLISTdb:

Choices             |           ID

ABC,XYZ,123     |         5001

This is patched ti nLISTflag as:

FLAG      |      recordID

ABC                  5001

XYZ                   5001

123                   5001

So now I’m trying to filter my gallery to display all the records in nLISTdb which have the ABC flag for example.

The closest I get give me a delegation warning because of ‘in’ which is not delegator. But I can’t figure out another way:

Snippet from my gallery items field.

ID in Filter(nLISTflag, FLAG=varFLAGfilter).recordID

Where ID is the ID of the record in nLISTdb and varFLAGFilter is a combobox selection for example “ABC”

Any help appreciated.

[gmbeee]

Anyone any ideas?

[gmbeee]

Been looking at this today. I’m not sure this is the right approach now. Anyone point me in the right direction? Thanks

[gmbeee]

Found the answer thanks to @WarrenBelz 

https://powerusers.microsoft.com/t5/Building-Power-Apps/Filter-one-Gallery-based-on-two-SharePoint-L...

This works perfectly! My app can now filter multi choice without delegation issues.

[gmbeee]

Ok, so this doesn’t work as expected.

The code works fine. However because I’ve used LookUp() the filter only returns the first item. I need all the matching items returned.

When I substitute A second filter() for LookUp() I get an error. Below…

Incompatible types for comparison…: Number/record

ID=Filter(nTIR_flags,Title=varFlagfilter).recordID

[gmbeee]

@RandyHayes 

Any ideas? Thanks

[gmbeee]

Still looking at this.

I’ve found that Delegation issues can be avoided by using a LookUp column with Search(Choices())

This actually works, but I can’t find a way to combine it with my other filters.

There must be a way to delegate filtering of a multi choice / lookup column to SP. 

I’m just missing it!!! All help appreciated.

@WarrenBelz  Any ideas?

[WarrenBelz]

Hi @gmbeee ,

You are not missing anything - the Choices workaround you mentioned has some limitations (as you have discovered), but the in filter is simply not Delegable. Filter() will not help here as that simply returns all records with a field matching the first item in the multi-select column. I had a look at a Collection looping through the items, but you still need AddColumns and Split, where both are subject to Delegation limits also.

The best you could do here would be to "pre-filter" both lists to numbers under your delegation limit (maybe the newest 2,000 records) before adding your filter. 

[gmbeee]

Hi @WarrenBelz 

Thank you for the reply.

The gallery is subject to several combobox filters. The user filters it accordingly.

I’ve gone with a toggle called ‘Large dataset filter’. When checked the toggle sets a variable to true.

I have an if statement in the gallery items field. If the toggle is true, I collect all list records into a collection using an ID mirror column to count the records and collect them in batches. 

This give performance a hit. But only when the toggle is checked.

By default it’s fault and the gallery is filtered with delegation warnings but prefiltered by date. So no performance issues.

[WarrenBelz]

Hi @gmbeee ,

Yes - @RezaDorrani has a good video on this process and actually refers in part to the pre-filter process I mentioned - I assume you are following this logic. Is this not resolved?