Filtering out records with certain values.

[Chrisguff12]

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-D...

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

[WarrenBelz]

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]

@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]

@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]

@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]

@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]

@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]

@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]

@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]

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.