StartsWith Function on SharePoint List Choice Type

[IncursioML1]

I have a SharePoint List that I'm trying to filter into a table in PowerApps.

The filter is giving me a delegation warning on the StartsWith function.

Filter(SPList,SomeNumber = Value(TextInput2.Text), StartsWith(Status.Value,"Re"))

The 'Status' column in SharePoint is a Choice Type, in which I would think is a complex datatype in whichshould work with the delegation logic.  Does anybody know how PowerApps treats a Choice Type field from a SharePoint List?  I suspect it's not Text or Complex.

[Pstork1]

It seems to be something specific to StartsWith().  If you do a Filter with "=" on a specific value from the dropdown it works without delegation as long as the value is Text.  I'm not sure why StartsWith() doesn't honor the complex type's final type like "=" does.  

[GarethPrisk]

I believe SharePoint does define the Choice column type as complex.

However, this grid is either misleading or SharePoint doesn't handle the nested operations with delegation. There's a note there about the decision being offloaded to "subfield involved" - hard to tell.

If you add a StartsWith operation to your gallery controls, for example - it will not show a delegation issue. I have a temporary Toggle with its Default set as

StartsWith(
    ThisItem.Choice.Value,
    "Re"
)

This does not show an issue.

It's definitely a delegation issue, as testing with setting app's record limit to 1 does in fact only return 1 record when multiple exist.

How long is your list of choices? Could this be checked a different way?

[IncursioML1]

The SharePoint List is around 2200 records but applying that filter first gets me to literally 5 records, but due to delegation it's missing the 6th record.  I ended up making a collection filter from filter of the 'SomeNumber' to get it to 6 records, then just did the starts with in there.  If you have any other suggestions to avoid the delegation and do it in one swoop I'm all ears.

Kind of confusing but I had used the documentation and grid to build the app as best I could knowing delegation exists.

[v-jefferni · Accepted solution]

Hi @IncursioML1 ,

The workaround is to hard code all the choices you want to filter by in the formula:

Filter(SPList,
       SomeNumber = Value(TextInput2.Text), 
       Status.Value = "Re1" || Status.Value = "Re2" || Status.Value = "Re3"
)

I don't think there would be too many options in a Choice column, which makes this operational.

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[Pstork1]

I'm not sure if this is what you mean by a collection filter, but this will work if the number filter gets you below the data row limit.

Filter(Filter(SPList,SomeNumber = Value(TextInput2.Text)) StartsWith(Status.Value,"Re"))

  Since the inner filter gets you below the data row limit you can ignore the delegation warning.