Deal with Delegation warning for more than 2000 records

[Zgolbarg]

Hi,

I have a SharePoint list with near 4000 records. i have this formula in the item property of my drop down:

ForAll(
Sort(
Filter(Projects,
drpClients.Selected.Value ='Client (Client0)'.Value &&
Not("closed" in Status.Value)
),
'Project Number'),

{Value: 'Project Title',
Id:ID
}
)

I set the delegation limit to 2000 but I received that warning in "In" function in the formula and it just show parts of  the result.

can you help me with that?

Thanks

[RandyHayes]

@Zgolbarg 

To be clear - you set the record limit in the app.  Your formula is not a delegable formula, so it will be limited by the first 2000 records in your datasource.

The In operator is not a delegable operator.  Also, the Not is not a delegable operator.

Please consider changing your Formula to the following:

With({_preFilter: 
    Filter(Projects, drpClients.Selected.Value ='Client (Client0)'.Value)
   },

    ForAll(
        Sort(
            Filter(_preFilter
                 Not("closed" in Status.Value)
            ),
           'Project Number'
        ),
        {Value: 'Project Title',
         Id:ID
        }
    )
)

The attempt here is to prefilter your datasource with a Delegable filter criteria and then use that as the source for the non-delegable filter.

Also, I assume based on your formula that the Status column is a multi-select choice column.  If it is not multi-select, then you don't need the in operator.

I hope this is helpful for you.

[Zgolbarg]

Hi @RandyHayes 

Thanks for your reply.

Yes, the status is a multi-select choice column.

I tried your formula but I've got these errors:

in the second filter : Invalid number of arguments , received 1 expected 2 or more

in the sort : Name isn't valid(project number, ID, project Title)

[RandyHayes · Accepted solution]

@Zgolbarg 

Missed a comma in the formula.

Should be:

With({_preFilter: 
    Filter(Projects, drpClients.Selected.Value ='Client (Client0)'.Value)
   },

    ForAll(
        Sort(
            Filter(_preFilter,
                 Not("closed" in Status.Value)
            ),
           'Project Number'
        ),
        {Value: 'Project Title',
         Id:ID
        }
    )
)

[Zgolbarg]

@RandyHayes 

Thanks a lot for your help.