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
Solved! Go to Solution.
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))
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))
Thank you! I just clued in that I was missing the .value after my location.
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?
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))
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
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.
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?
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.
Stay up tp date on the latest blogs and activities in the community News & Announcements.
Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST
Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.
User | Count |
---|---|
200 | |
179 | |
67 | |
36 | |
33 |
User | Count |
---|---|
341 | |
271 | |
113 | |
73 | |
58 |