cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
McG
Level: Powered On

Search/Filter Gallery Based On Text Input

I have an issue with an app that is not searching based on the formula I have provided, despite using this formula many times in different apps. My only logic I can draw from this is that it has something to do with the SharePoint list column being of a Person/Group type. 

 

Here is my formula:
SortByColumns(Search('Improvements',txtSearch.Text,"Raised By"), "ID Number", If(SortDescending1, Descending, Ascending))

 

I get three errors from the formula:

The specified column "Raised By" does not exist

The function "search" has some invalid arguments

The function "SortByColumns" has some invalid arguments

 

I can confirm "Raised By" does exist (it does not allow multiple selections) on the SharePoint site with no extra spaces or anything. 

 

Am I overlooking something simple with the gallery or the sorting? I do have a "sort" button with the code "UpdateContext({SortDescending1: !SortDescending1})" on it. 

 

I just think I am stuck again on the SharePoint issue and Person/Group type. As always, any help would be greatly appreciated

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Search/Filter Gallery Based On Text Input

Person or Group columns return an object that has multiple fields.  To use it in a Filter statement you need to add the specific field you want to filter on like this

Filter('Conditional Flow',SearchBox.Text in 'Modified By'.DisplayName)

Since search operates at the column level you can't do that, so you'll need to use AddColumn to pull the sepecific field out of the person column as a seperate column like this.

Search(AddColumns('Conditional Flow',"ModifiedPerson",'Modified By'.DisplayName),SearchBox.Text,"ModifiedPerson")

You can also use the added column to sort the results by the DisplayName of the person. 



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

Re: Search/Filter Gallery Based On Text Input

So I have this, and it produces a result initally (gallery is properly populated) but when I start typing in the search box nothing is returned. Any ideas?

 

SortByColumns(Search(AddColumns('Improvements',"ModifiedPerson",'Modified By'.DisplayName),txtSearch.Text,"ModifiedPerson"),"ID",If(SortDescending1, Ascending, Descending))

 

On a secondary note, my column to sort on SharePoint is called "ID Number" but in the formula above I have to use just "ID". Is there something up with the word "Number" in regards to a SharePoint list?

 

Thanks! 

9 REPLIES 9
Super User
Super User

Re: Search/Filter Gallery Based On Text Input

Person or Group columns return an object that has multiple fields.  To use it in a Filter statement you need to add the specific field you want to filter on like this

Filter('Conditional Flow',SearchBox.Text in 'Modified By'.DisplayName)

Since search operates at the column level you can't do that, so you'll need to use AddColumn to pull the sepecific field out of the person column as a seperate column like this.

Search(AddColumns('Conditional Flow',"ModifiedPerson",'Modified By'.DisplayName),SearchBox.Text,"ModifiedPerson")

You can also use the added column to sort the results by the DisplayName of the person. 



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

Re: Search/Filter Gallery Based On Text Input

So I have this, and it produces a result initally (gallery is properly populated) but when I start typing in the search box nothing is returned. Any ideas?

 

SortByColumns(Search(AddColumns('Improvements',"ModifiedPerson",'Modified By'.DisplayName),txtSearch.Text,"ModifiedPerson"),"ID",If(SortDescending1, Ascending, Descending))

 

On a secondary note, my column to sort on SharePoint is called "ID Number" but in the formula above I have to use just "ID". Is there something up with the word "Number" in regards to a SharePoint list?

 

Thanks! 

Super User
Super User

Re: Search/Filter Gallery Based On Text Input

Did you verify that what you are typing actually maps to the Display name of the modified By column?  I know for mine the Display names aren't what I actually expected them to be.  That would explain why you get no results when you start typing.

 

Is ID number a column you created?  If so then you aren't sorting on the right column.  ID is a built-in column for SharePoint lists that is auto generated.



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

Re: Search/Filter Gallery Based On Text Input

I had mistakenly copied your 'Modified By'.DisplayName into my formula instead of changing it to my 'Raised By'.displayname - now it works ha

 

Thank you very much for your help!

McG
Level: Powered On

Re: Search/Filter Gallery Based On Text Input

An unexpected error has shown up.  When I click on the item in the gallery to go to another screen where the expanded details are, nothing shows up ("No item to display" is shown in the form (in display mode). The error (on the form) says "invalid formula. Expected a value compatible with 'DataSource' ", with DataSource simply being the SharePoint list name, and the items just being NameOfGallery.selected.  

Super User
Super User

Re: Search/Filter Gallery Based On Text Input

What is the Items property on the second screen's Form set to?  It sounds like you have it set to the data source.  If you want to show details it should be set to the selected item in the first gallery.



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

Re: Search/Filter Gallery Based On Text Input

It is set to the SharePoint list.  The Item is set to the selected item from the first screen.  There's something odd happening with the formula on the gallery that prevents passing of the selected item to the second screen (the detail screen). 

What you suggested does in fact work, but, as is the trend with my posts here, another issue was encountered.  While the detail screen does show all of the info, if I need to edit an item, the form is no longer connected to the SharePoint list (datasource) and it will not update.  

Super User
Super User

Re: Search/Filter Gallery Based On Text Input

Since the gallery adds a column I'm wondering if you need to point the detail screen at the gallery as a data source instead of the sharepoint list?  Or add the column and store it in a collection and then use that on both the gallery and the detail screen.  I suspect the mismatch in the schemas after you add a column is causing the issue on the detail screen.



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

Re: Search/Filter Gallery Based On Text Input

I was really hoping that I could use DropColumns and align the columns agin with the SharePoint list.  I think I will repost this as a new question to open it up to the community. Maybe someone has encountered this before.  

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Tomorrow, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 106 members 3,830 guests
Please welcome our newest community members: