Filtering out records with certain values.

(0) Share

Report

Posted on by Chrisguff12

233

Hello,

With the help of @Drrickryp and the following formula i was able to filter my Data table by a search box and drop down.

Link to past post: https://powerusers.microsoft.com/t5/Building-Power-Apps/Data-Table-Filtering-with-search-field-and-Dropdown/m-p/869312#M276267https://powerusers.microsoft.com/t5/Building-Power-Apps/Data-Table-Filtering-with-search-field-and-Dropdown/m-p/869312#M276267

Sort(
 Filter(
 'Expense App List New',IsBlank(Tech_Name_Search_TextInput1.Text)||
 StartsWith(RequestedBy,Tech_Name_Search_TextInput1.Text),
 IsBlank(Filter_Item_Status_Dropdown1.Selected.Value)||
 ItemStatus = Filter_Item_Status_Dropdown1.Selected.Value
 ),
 RequestedBy,Ascending
)

My question now is can I add a filter to exclude and not show any records where the value in ItemStatus is "Complete".

Then I want to duplicate the data table for another screen and show only records where the ItemStatus is "Complete".

Categories:

Building Power Apps

All responses (13)

Answers (1)

Sort by

WarrenBelz 156,714 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Chrisguff12 ,

This one is Delegable (when they are complete)

Sort( Filter( 'Expense App List New', ( IsBlank(Tech_Name_Search_TextInput1.Text)|| StartsWith(RequestedBy,Tech_Name_Search_TextInput1.Text ) && IsBlank(Filter_Item_Status_Dropdown1.Selected.Value)|| ItemStatus = Filter_Item_Status_Dropdown1.Selected.Value ) && ItemStatus = "Complete" ), RequestedBy )

but this is not (when they are not complete)

Sort( Filter( 'Expense App List New', ( IsBlank(Tech_Name_Search_TextInput1.Text)|| StartsWith(RequestedBy,Tech_Name_Search_TextInput1.Text ) && IsBlank(Filter_Item_Status_Dropdown1.Selected.Value)|| ItemStatus = Filter_Item_Status_Dropdown1.Selected.Value ) && ItemStatus <> "Complete" ), RequestedBy )

If you have all the other Status values (some examples), you can do this to be Delegable.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
Chrisguff12 233 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz

Warren, these formulas are working to filter out the Complete status, but I have lost the ability to filter the records only using the dropdown. After I use the search box to filter "RequestedBy" then the dropdown will filter by status. Can we change is so that the table will show all user requests that match the selected dropdown value?

Edit: also when I filter the records with the dropdown I pulls up every record matching with the matching status from the dropdown regardless of what I type in the search box.

the search box filters correctly for the table that shows only Complete records but not for the other.
WarrenBelz 156,714 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@Chrisguff12 ,

So you want to have records that are either complete or matching the drop-down and Search box?

You have to decide which one or have a trigger (checkbox or the like) to "turn on" the Completed filter.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
Chrisguff12 233 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz

There are two data tables "Current Requests" and "Complete requests".

The Current requests table will show all records that are not complete. These will need to be able to be filtered by the search box and/or StatusDropdown.

The Complete table will only have the complete records. This one will only need to be filtered by the search box cause all records will have the Complete status.
WarrenBelz 156,714 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@Chrisguff12 ,

If they are all complete, you do not need the last filter (ItemStatus="Complete").

As I asked previously, when do you / do you not want to filter on Item Status - you will need some sort of a switch to change the Filter accordingly.
Chrisguff12 233 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz
For the "Current Requests" Table I don't want them to show Complete at all. but the status dropdown will still have to filter the other options like Approved, pending, ordered, etc.
WarrenBelz 156,714 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@Chrisguff12 ,

We are going in circles here a bit - the problem is you know what you want, but I cannot see your data) or read your mind). You have two filters, one for RequestedBy (a Text box) and the other for ItemStatus (a drop-down).

Are you trying to restrict the items shown in the drop-down (which you are already filtering on)?
Chrisguff12 233 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz ,

I’m Sorry for the confusion and Thanks for your patience. I’m not trying to restrict items shown in the dropdown.

Ill show you what is happening.

This data table will display all records and their status as a standard. With NO filters applied. The users wont even have an option to set a filer to show the Completed. They will select the Complete Request button to take them to a different screen and view the completed requests.
Your Formula works to take out the records with the “Complete” status. (Below).

But in doing so the search and dropdown filters no longer work right. Here are the examples. Green is the results from my current Formula, the Red is the results with your formula.

Searching by just the dropdown. The Red is still showing all the records.

Filtering by name only works just fine.

Filtering by both. The Red filters by Status just fine, but it is showing all the users regardless of what I type in.
WarrenBelz 156,714 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Thanks @Chrisguff12 ,

The key here is that you want to filter by all the "other" values other than approved - try this (the "long way" is to make it Delegable)

With( { wActive: Filter( 'Expense App List New', ItemStatus = "Delivered" || ItemStatus = "Missing" || ItemStatus = "Ordered" || ItemStatus = "Pending" || ItemStatus = "Received" || ItemStatus = "Returned" || ItemStatus = "Reviewing" ) }, Sort( Filter( wActive, ( IsBlank(Tech_Name_Search_TextInput1.Text)|| StartsWith(RequestedBy,Tech_Name_Search_TextInput1.Text ) && IsBlank(Filter_Item_Status_Dropdown1.Selected.Value)|| ItemStatus = Filter_Item_Status_Dropdown1.Selected.Value ) ), RequestedBy ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
WarrenBelz 156,714 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Chrisguff12 ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.