Large dataset filtering choice options

[gmbeee]

Hi,

So I’ve done a lot of reading around delegation and large datasets.

I have a SP list as my datasource.

This list currently holds just under 2000 items. 

It will soon go above that. However I can’t see it going beyond 10,000 in the next 5 years.

Have a column which would ideally be a multi select choice column. However I avoided this because I know that I can’t filter this without running into delegation issues.

So I use a single line text column which holds a string of options separated with “,”.

This approach works well. However my issue now is that I cannot Filter the data source to search for terms within this string.

If I Split the string and lookup I run into delegation warnings again.

At the moment the only way I’m thinking it’s possible is to merge blocks of 2000 item collections. But this has performance issues.

Just an example:

Title            Column A

1                 ONE,TWO,THREE

2                 TWO,THREE

3                 ONE,FOUR,FIVE,SIX

….                ….

3000           SEVEN,TEN,ONE,FIVE

How can I filter Column A so that only items with ‘ONE’ are returned…

As always, thanks very much.

[RandyHayes]

@gmbeee 

You kind of traded one problem for another.  Being able to filter on multiple choice columns was not going to be delegable, but now, filtering on strings that contain a value is not going to be delegable either.

I would first consider if there is anything about your data that you can "pre-filter" on.  Meaning, when you search this string, or multiple choice options (neither delegable) do you HAVE to do it on ALL records?  Or is there a pre-filtering that can be done - like a date range of records, or based on a status column value or...anything else that is delegable?

If so, then you can use the pre-filter to limit the number of records that you will perform non-delegable criteria on.  Essentially, in your formula, you would establish a pre-filter that is delegable and then perform the non-delegable on the results of that - at that point delegation is not a need.

Ex.

With({_preFilter: 
    Filter(dataSource, Created>=DateAdd(Now(), -30, Days))
    },
    Filter(_preFilter,
        "ONE" in ColumnA
    )
)

In the above, the prefilter returns that last 30 days of records and then it performs an in operator on the prefiltered list.  In is not delegable to a datasource, but to a local table it is no problem.

I hope this is helpful for you.

[gmbeee]

Hi Randy,

Thanks for your suggestion. I do pre filter to some extent.

However, I’d still like the ability to be able to Filter the entire list.

I just wanted to check I hadn’t missed anything.

I think I will settle on the Concurrent method to merge collections when I filter the complete list.

It’s not ideal but it is at least a viable workaround.

[RandyHayes · Accepted solution]

@gmbeee 

Yes, but keep in mind you are then loading ALL the records into your app memory.  This can be a huge impact on the performance of your app.  

It is best to consider alternatives.  

Those type would be based on the worst-case scenario.  I would consider loading all records into your app memory to be the worst-case.  So that would be the baseline.  Let's just say that process would consume 10 seconds and would then mean your app is also consuming a large amount of memory.

Then consider the alternatives...

1) Perform a special filter when there is a need to actually search what could be 10k records.  You can perform the search you want, but it is not just a simple filter statement, it's sort of a hybrid of collecting everything and filtering.  That might take 10 seconds as well, but now, you are doing this ONLY when you perform this specific search.  So, if users never initiate that type of search, then the app is not impacted as it would be if you collected everything all the time.

2) Consider your data.  While multi-select choices are "nice" in SharePoint, it is not "database normal".  In the database world you would have a separate table that would contain all the choices and all the records that are linked to that choice.  This is all possible in SharePoint as well (and preferred to avoid delegation).  So using your example data, you would have a separate list with a Title and recordID column.  It would have the following to represent your example:

Title        recordID

ONE       1

TWO      1

THREE    1

TWO      2

THREE    2

ONE       3

FOUR     3

FIVE       3

....etc...

Now that table is delegable.  You can find all records with ONE and so forth with a delegable filter.

In this scenario, your time would be milliseconds to do, but the tradeoff comes with then looking up the corresponding records.  But again, this would be where the trade off goes.

SO...the above are just some ideas for you to consider.  I hate to see an app suffer from a performance issue because so much data is always being collected and then people complain about performance.  There are options to consider.

You are the designer...so the choice is yours.

Hope that extra is somewhat helpful.

[gmbeee]

You make some excellent points. Option 2 is very clever.

What would be the best way to populate the additional list?

If I’m using a form to submit data to my primary database list. I guess I could just Patch each choice with the with the record ID?

Or is there a way to auto fill the additional list?

Thanks. Just trying to get my head around it. Oh, and Happy New Year!

[RandyHayes]

@gmbeee 

Sorry for the delay in response...

So populating the additional list really is best handled by PowerAutomate.  You could try to do it within the app, and that is fine too.  Essentially, the PowerAutomate flow would get triggered by new or changed records in your list.  It would then take the value of the Title and record ID and look it up in the alternate list.  If changes are needed, it would make them or add it.  It's just one option.  I would say, I'd probably do it within the app at first and if performance or other factors become a condition, then relocate the logic into a flow.

Happy New Year to you too!!

[gmbeee]

Hi Randy,

Do you have an example of how I can integrate option 2 into my app? I can’t seem to get my head around it.

😭