How To Create Multiple Filters Working At The Same Time

(0) Share

Report

Posted on by s_tanswell84 Microsoft Employee

Hi!

I have a SharePoint List ("Reactive Support Escalation") with the following columns:

- Title

- Entity (Single Line Text Field)

- Status (Single Line Text Field)

I have set up a Gallery for this SharePoint List in PowerApps.

I have 3 PowerApps Field for filtering:

- StatusSearch (Dropdown box with 3 options: Select, Ongoing, Completed. This field is to filter on the "Status" SharePoint column)

- Entity (Text Input. This field is to filter on the "Entity" SharePoint column)

- SearchTicket (Text Input. This field is to filter on the "Title" SharePoint column)

I want to be able to filter the gallery using one or more (or all) of the filters.

For example - Below is my full list in the Gallery:

Ticket (Title)	Entity	Status
123	UK	Ongoing
124	France	Ongoing
125	UK	Completed
130	UK	Ongoing

If I was to filter on just one Filter (SearchTicket) then the Gallery would show:

Filter: Entity = UK

Ticket (Title)	Entity	Status
123	UK	Ongoing
125	UK	Completed
130	UK	Ongoing

If I then filter further by Status, I should see the below

Filter: StatusSearch Select Value = Ongoing

Ticket (Title)	Entity	Status
123	UK	Ongoing
130	UK	Ongoing

If I then filter even further by Ticket (Title), I should see the below

Filter: SearchTicket = 123

Ticket (Title)	Entity	Status
123	UK	Ongoing

I have tried a few times to get this working but have failed. What should my Gallery Items Property be?

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

Akser 1,546 Moderator on at

Like (0)

Report
Hi @s_tanswell84,

Let me know if this is what you are looking for:

Filter( DataSource, IsBlank(StatusSearch.Selected.Value) Or StatusSearch.Selected.Value = Status, IsBlank(Entity.Text) Or StartsWith(Entity, Entity.Text), IsBlank(SearchTicket.Text) Or StartsWith(Title, SearchTicket.Text) )

Was this reply helpful? Yes No
s_tanswell84 Microsoft Employee on at

Like (0)

Report
Thanks @Akser - This is great! But is it possible to make a couple of tweaks?

When all the filters are blank, the gallery is blank. How can we do the above but show all Gallery Items when the filters are blank?
The text input filters work when I put the exact ticket number in. How can we use the above code but allow the filters to search on a more "contains" level. For example, if put just "12" in to the Ticket Search input field, it shows all the tickets containing "12"
Ticket (Title) Entity Status
123 UK Ongoing
124 France Ongoing
125 UK Completed

Was this reply helpful? Yes No
Akser 1,546 Moderator on at

Like (0)

Report
Hi @s_tanswell84,

The gallery should show all the records when the filters are blank (which is why I have these 'IsBlank' conditions there). Let's try another approach with the use of 'Len'.

Be aware that 'Contains' will give you a delegation warning since your data source is SharePoint.

Filter( DataSource, Len(StatusSearch.Selected.Value) = 0 Or StatusSearch.Selected.Value = Status, IsBlank(Entity.Text) Or Entity.Text in Entity, IsBlank(SearchTicket.Text) Or SearchTicket.Text in Title )

Was this reply helpful? Yes No
s_tanswell84 Microsoft Employee on at

Like (0)

Report

Thanks @Akser ! Unfortunately that comes up with an error of "The function Filter has some invalid arguments" and on the OR and the SharePoint Column Names it says "Invalid Argument Type"

Was this reply helpful? Yes No
Akser 1,546 Moderator on at

Like (0)

Report

Hi @s_tanswell84,

Could you do me a favor and rename your Text Input field Entity to SearchEntity (and update the previously shared formula accordingly)?

Confirmation that it works without an error:

Was this reply helpful? Yes No
s_tanswell84 Microsoft Employee on at

Like (0)

Report

Hi @Akser - this is what it looks like. There is still an error:

Was this reply helpful? Yes No