Answered

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

(0) Share

Report

Posted on by CP153319

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

Categories:

Building Power Apps

I have the same question (0)

All responses (13)

Answers (1)

Sort by

Verified answer

Jeff_Thorpe 6,085 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

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))

Was this reply helpful? Yes No
CP153319 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
asri_zainal 54 on at

Like
a
(4)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
StuartSmith 225 on at

Like
a
(1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
mattbGetz 25 on at

Like
a
(2)

Report
Copy link

Link copied!

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))

Was this reply helpful? Yes No
RK1 14 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Australia 387 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
RK1 14 on at

Like
a
(0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Australia 387 on at

Like
a
(0)

Report
Copy link

Link copied!

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-Choice-quot-Field/td-p/104473

Hardit explained how to get AddColumns to work.

Hope it helps.

Was this reply helpful? Yes No
RK1 14 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks, I'll give it a look.

Was this reply helpful? Yes No