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

Filter sharepoint list

I have sharePoint List that has multiple columns. 

Columns: Facility, Facility Status, Address, Phone number, etc.

I want to be able to search and show all items by "Facility" column but I would like to see Items where Facility Status = "Active".

 

This is what I have:

SortByColumns(Search([@Facility], TextSearchBox1.Text, "Facility"), "Facility", If(SortDescending1, Descending, Ascending))

 

My fromula shows all the items that are "Active"and "Inactive".

 

Please help on configuring my formula to show only Facilities where Facility Status = "Active"

 

Thank you

2 ACCEPTED SOLUTIONS

Accepted Solutions
PowerApps Staff rgruian
PowerApps Staff

Re: Filter sharepoint list

See my answer above.

 

If the field is declared as a choice field in Sharepoint, then try this:

 

SortByColumns(Filter([@Facility], TextSearchBox1.Text in Facility && Facility_x0020_status.Value = "Active"), "Facility", If(SortDescending1, Descending, Ascending))

 

There is no need to use single quotes for identifiers unless they contain non-alphanumeric characters. For example identifiers with blanks and characters such as !, @, #, $, %, etc -- would have to be quoted. Nothing else has to be. In your case there is no need to use single quotes.

 

I hope this helps.

 

Radu Gruian [MSFT] ** PowerApps Staff

View solution in original post

daleritf
Level: Powered On

Re: Filter sharepoint list

You are awesome!!!. Worked perfect.. I really appreciate that.. Before I close this post as Solved, I have couple of questions:

 

- Where can I find more information about configuring Search functions and how did you identify what column setting should be used for a specific column. For example in case of choice column you used TextSearchBox1.Text (how did you know that?)

 

Is it true that PowerApps has limitation to pull only 500 list Items from SharePoint? Is this 500 Filtered records or 500 total records that PowerApps can render?

 

 

Thank you again

 

View solution in original post

17 REPLIES 17
Perumal
Level 10

Re: Filter sharepoint list

I assume that FacilityStatus is of text type. You need to use And operator. Replace the Search with the below Filter and check.

 

Filter(SPDataSource,And(Facility=TextInput1.Text,FacilityStatus="Active"))

Community Support Team
Community Support Team

Re: Filter sharepoint list

Hi Daleritf,

 

I created an App based on a SharePoint Online list, the SharePoint list was similar with yours. In the App, I configured the Items property of the gallery to:
Sort(If(IsBlank(TextSearchBox1.Text),Facility,Filter(Facility,And(Title=TextSearchBox1.Text,Status="Active"))),Title,If(SortDescending1,Descending,Ascending))

 

Hope this can also be a reference for you.

 

Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PowerApps Staff rgruian
PowerApps Staff

Re: Filter sharepoint list

To complement the other answers you have received, here's my take, which will allow for partial matches as well:

 

SortByColumns(Filter([@Facility], TextSearchBox1.Text in Facility && 'Facility Status' = "Active"), "Facility", If(SortDescending1, Descending, Ascending))

 

I hope this helps.

 

Radu Gruian [MSFT] ** PowerApps Staff
daleritf
Level: Powered On

Re: Filter sharepoint list

Thank you for reply but I get this error:

Error

PowerApps Staff rgruian
PowerApps Staff

Re: Filter sharepoint list

 

What is the type of Facility_x0020_status in SharePoint?

Also, if you hover over the red squiggle in your formula, what error message do you see?

 

If the field is declared as a choice field in Sharepoint, then try this:

 

SortByColumns(Filter([@Facility], TextSearchBox1.Text in Facility && Facility_x0020_status.Value = "Active"), "Facility", If(SortDescending1, Descending, Ascending))

 

 

 

 

Radu Gruian [MSFT] ** PowerApps Staff
daleritf
Level: Powered On

Re: Filter sharepoint list

Thank you for reply:

 

- Facility_x0020_status is a choice column in sharepoint list (dropdopwn)

- The error message says "Invalid argument type".

 

Also should I set the column in the formula with single quotation mark or without?

 

Facility_x0020_status   or 'Facility_x0020_status'

 

I appreciate your help

PowerApps Staff rgruian
PowerApps Staff

Re: Filter sharepoint list

See my answer above.

 

If the field is declared as a choice field in Sharepoint, then try this:

 

SortByColumns(Filter([@Facility], TextSearchBox1.Text in Facility && Facility_x0020_status.Value = "Active"), "Facility", If(SortDescending1, Descending, Ascending))

 

There is no need to use single quotes for identifiers unless they contain non-alphanumeric characters. For example identifiers with blanks and characters such as !, @, #, $, %, etc -- would have to be quoted. Nothing else has to be. In your case there is no need to use single quotes.

 

I hope this helps.

 

Radu Gruian [MSFT] ** PowerApps Staff

View solution in original post

daleritf
Level: Powered On

Re: Filter sharepoint list

You are awesome!!!. Worked perfect.. I really appreciate that.. Before I close this post as Solved, I have couple of questions:

 

- Where can I find more information about configuring Search functions and how did you identify what column setting should be used for a specific column. For example in case of choice column you used TextSearchBox1.Text (how did you know that?)

 

Is it true that PowerApps has limitation to pull only 500 list Items from SharePoint? Is this 500 Filtered records or 500 total records that PowerApps can render?

 

 

Thank you again

 

View solution in original post

PowerApps Staff rgruian
PowerApps Staff

Re: Filter sharepoint list

 

Glad to hear things are working!

 

For Search() information, please refer to the formula reference:

https://powerapps.microsoft.com/en-us/tutorials/function-filter-lookup/

 

I am not fully understanding the first question, specifically what you meant by "identifying what column setting [...]".

Columns in PowerApps are typed, as they are in Sharepoint for example. Certain operators make sense only for certain types. The "in" operator that I used for your first column applies to text (string) values or whole tables. Based on the formula you mentioned in your original post I guessed that you wanted to use the output of TextSearchBox1 to filter down your list -- hence the first test: (TextSearchBox1.Text in Facility).

 

You also stated you wanted to further filter down the list based on the value of a choice field. Choice fields in PowerApps are complex values (records). Hence the second test: (Facility_x0020_status.Value = "Active").

 

Re. your last question: yes, PowerApps delegates certain operations to the back end when it's possible to do so (when the query is compatible with what the back end (in this case Sharepoint) and the corresponding Connection supports). This is a work in progress, and more queries will be seamlessly delegated in upcoming releases. Whatever query cannot be delegated will operate locally on a cache, which is limited to 500 rows per data source.

 

Radu Gruian [MSFT] ** PowerApps Staff

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,096)