Filter sharepoint list

[daleritf]

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

[Perumal]

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

[v-yamao-msft]

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

[rgruian]

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.

[daleritf]

Thank you for reply but I get this error:

[rgruian]

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

[daleritf]

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

[rgruian · Accepted solution]

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.

[daleritf · Accepted solution]

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

[rgruian]

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.