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
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.
Found the answer thanks to @WarrenBelz
This works perfectly! My app can now filter multi choice without delegation issues.
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
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?
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.
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.