cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate IV
Advocate IV

SharePoint Lists - Powerapps won't search "Choice" Field?

I have a very simple powerapps application which enables managers to enter some information into a SharePoint list.

 

In order to make it easy for them (and myself) I set up many of the columns as "Choice" fields which present a Dropdown box to the manager with the limited choices they have to populate the field (Example: Under the "Locations" column they can choose North, South, East or West). They cannot add other choices.

 

Now, in the Powerapp itself, when I go to set some search functionality in the "Search" field at the top, Powerapps will not accept any of the Choice (dropdown) fields to search on.

 

This is the default forumula in the "Search" box, in the forumula bar, with the gallery selected and "Items" as the property choice:

 

SortByColumns(Filter('Safety Concentration Time Tasks', StartsWith(ComplianceAssetId, TextSearchBox1.Text)), "ComplianceAssetId", If(SortDescending1, Descending, Ascending))

 

My question is am I thinking about this the right way OR is there a forumla which will search those "choice" columns?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

One thing to note is that complex field types (choice, lookup, date, metadata, person/group) aren't delegable in SharePoint, so if you have a larger list (>2000) you will have issues. If you have a smaller list then you might not have to worry about the blue dot warning you wil get when filtering or searching using a complex field if the performance isn't an issue because all the items from the list need to be pulled into PowerApps before the query can be executed.

 

Example using the search box to search on locations

SortByColumns(Filter('Safety Concentration Time Tasks', StartsWith(Locations.Value, TextSearchBox1.Text)), "ComplianceAssetId", If(SortDescending1, Descending, Ascending))

 

Instead of a search box you could use drop downs to help filter the gallery.

Example:

ddLocations: drop down control will all the location values

Gallery Items: SortByColumns(Filter('Safety Concentration Time Tasks', ddLocations.Selected.Value = Locations.Value), "ComplianceAssetId", If(SortDescending1, Descending, Ascending))



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

10 REPLIES 10
Highlighted
Super User II
Super User II

One thing to note is that complex field types (choice, lookup, date, metadata, person/group) aren't delegable in SharePoint, so if you have a larger list (>2000) you will have issues. If you have a smaller list then you might not have to worry about the blue dot warning you wil get when filtering or searching using a complex field if the performance isn't an issue because all the items from the list need to be pulled into PowerApps before the query can be executed.

 

Example using the search box to search on locations

SortByColumns(Filter('Safety Concentration Time Tasks', StartsWith(Locations.Value, TextSearchBox1.Text)), "ComplianceAssetId", If(SortDescending1, Descending, Ascending))

 

Instead of a search box you could use drop downs to help filter the gallery.

Example:

ddLocations: drop down control will all the location values

Gallery Items: SortByColumns(Filter('Safety Concentration Time Tasks', ddLocations.Selected.Value = Locations.Value), "ComplianceAssetId", If(SortDescending1, Descending, Ascending))



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

Highlighted

Thank you! I just clued in that I was missing the .value after my location.

 

 

Highlighted

Seems like this solution works for the OP however what if one really need to perform a search in a text input for a choice field in sharepoint list, is this still possible?

 

If yes, able to point me in to the direction on how to achieve this?

 

- I have a choice field say "Departments" with various deparments as an option

- in my powerapp, i have a text search input that allows users to be able to search by Name(string field), Department(Choice field)

- At present I have to change the the "Department" field as string instead of choice field in order for the search function to work.

 

Error: Wrong column type: Expects text type.

Formula:

 

Search('Sharepoint List', SearchBoxText.Text, "Name", "Department")

 

I'm assuming some conversion from choice to string need to be done however I have no idea where to start.

Hi Asri, rather old thread, but did you even find a solution?

Highlighted

Hey Stuart,

 

The way that I got around this was by using the AddColumns function to run a calculation on the non-text field and turning it into a new field that was a text item. Then I could use Search on the newly created field.

 

AddColumns(myTable, "newColumn", PlainText(oldColumn.value))

Highlighted

Hey Matt,

Where do you put this AddColumns function in regards to text function? I currently am trying to search a text and choice. I currently have the function:

Search('Room 110 list',txtSearchbox.Text,"Title")

which works, but I also want to add a choice column, so in theory, I'd want:

Search('Room 110 list',txtSearchbox.Text,"Title"," Shop")

Where Shop is a choice in my SharePoint. I think I need to use the function you mentioned "AddColumns" to this to make it work, if I understand you correctly

Highlighted

Hi RK1, 

 

Just wondering if you managed to get the AddColumn to work?

I am also experiencing the same error of trying to search values from a Choice column in SharePoint.

I would be very grateful if you could shed some light.

 

Thank you.

Highlighted

Hi,

 

No, I didn't get it to work, but I ended up changing to a text type column, but I used a drop down style input in powerapps to turn the text into a "Choice" kind of . Does that make sense?

Highlighted

Hi RK1,

Thanks for replying.

You may wish to visit this thread I just posted: https://powerusers.microsoft.com/t5/Building-Power-Apps/SharePoint-Lists-Powerapps-won-t-search-quot...


Hardit explained how to get AddColumns to work. 

 

Hope it helps.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,959)