cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IncursioML1
Helper I
Helper I

StartsWith Function on SharePoint List Choice Type

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.

IncursioML1_0-1634749967439.png

 

1 ACCEPTED SOLUTION

Accepted Solutions

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.

View solution in original post

5 REPLIES 5
Pstork1
Dual Super User III
Dual Super User III

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.  



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
GarethPrisk
Resident Rockstar
Resident Rockstar

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?

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.

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.

View solution in original post

Pstork1
Dual Super User III
Dual Super User III

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. 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,371)