Filter sharepoint list

(3) Share

Report

Posted on by 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

All responses (18)

Answers (2)

PeterLeibling 4 on at

Like (0)

Report

Re: Filter sharepoint list

Maybe somebody can help me. I try to filter my Sharepoint List - cause i want only to see Entrys with Status "Offen" (means Open - this comes from an Dropdownlist in Sharepoint.

The original Source is:
SortByColumns(Filter([@Ticketliste], StartsWith(Titel, TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending))

When i try this, i get an error:
SortByColumns(Filter([@Ticketliste], StartsWith(Titel, TextSearchBox1.Text), Status = "Offen"), "Title", If(SortDescending1, Descending, Ascending))

When i edit the Sharepoint list this field it shows me: ...../_layouts/15/FldEdit.aspx?List=%xxxxxxxxxxxx-D14A-4E7F-BF89-67E11C230DAF%7D&Field=jexo

How is the Syntax? Did i have to use as Name jexo or Status? How is the correct Syntax?

Thanks a lot for your help.
jkeckard007 108 on at

Like (0)

Report

Re: Filter sharepoint list

This did not work see
It just says starting string the function returns true if the text starts with a string and then it does not return anything.
Thank you
Meneghino 6,949 on at

Like (0)

Report

Re: Filter sharepoint list

PS the = operator may be case-sensitive for SP lists but I am not sure
Meneghino 6,949 on at

Like (0)

Report
Re: Filter sharepoint list

Hi @jkeckard007, since both StartsWith and = are delegated then it shouldn't matter how many rows the list has.
I think you got a bracket in the wrong place, try this:
SortByColumns(Filter(HelpDeskTicket, StartsWith(Title, TextSearchBox1.Text), Status="complete")), "Status", If(SortDescending1, Descending, Ascending))
Please let me know.
jkeckard007 108 on at

Like (0)

Report

Re: Filter sharepoint list

Can sme one help me I am trying to do close to the same thing. I have a Title Column and a Status Column both are single line of text field.
I want to filter on Status= COMPLETE and text box search on Tilte column...the Only thing I get to work is the below the list is over 5000 item This doesn't filter on Status at all.
SortByColumns(Filter(HelpDeskTicket, StartsWith(Title, TextSearchBox1.Text&& Status="complete")), "Status", If(SortDescending1, Descending, Ascending))
Thanks
Jennifer
MarkLandon 5 on at

Like (0)

Report

Re: Filter sharepoint list

Hi Meneghino

Sorry to take so long to get back to you - I was away the second half of last week.

The good news is that it works! You're a star, many thanks
Meneghino 6,949 on at

Like (2)

Report
Re: Filter sharepoint list

SortByColumns(Filter(Valet, StartsWith(RegNo, TextSearchBox1.Text), Status = "Live"), "RegNo", If(SortDescending1, Descending, Ascending))
Does this work?
MarkLandon 5 on at

Like (0)

Report

Re: Filter sharepoint list

Hi Radu, I'm still really struggling with this syntax and your solution appears to be the only one out there!

I have a Sharepoint list called Valet which is designed to book vehicle valet appointments. From this I have created a powerapp also called Valet. My Items syntax currently reads:
SortByColumns(Filter(Valet, StartsWith(RegNo, TextSearchBox1.Text)), "RegNo", If(SortDescending1, Descending, Ascending))

This searches for the RegNo just fine but my problem is that I only want to display items where Status = "Live". I have tried using your logic based on the Facility example but clearly I'm doing something wrong as all I end up with is lots of little yellow warning triangles. All of my display fields are text - no choice or drop-downs so in theory it should be very simple. Could you tell me what syntax I need? I have been tearing my hair out on this all afternoon!

Many thanks
daleritf 99 on at

Like (0)

Report

Re: Filter sharepoint list

Thanks alot!!!!!
rgruian on at

Like (0)

Report

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.