cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
voglejm
Helper I
Helper I

Filtering with Date Picker, Text Search, and IsBlank

Hello Everyone,

My organization has asked me to create a basic app that will allow employees to volunteer to help with activities throughout the company during the COVID situation. I have a list of all available assignments and I would like the browse gallery to only show items with no support resource assigned.

 

COVID Support AppCOVID Support App

 

With help from the power apps community yesterday I was able to make real progress. Now I’m being asked to add a date picker as well. I'm able to get each of these to function separately; however, I haven't been able to successfully join the two statements into one. 

 

Date Filter

Filter(Support_Needs_List,DateValue(Text(StartDateOnly),"en")=DateValue(Text(DatePicker1.SelectedDate),"en"))

 

Text Filter

SortByColumns(Filter(Support_Needs_List,IsBlank(Support_Resource),IsBlank(FilterTextBox.Text) || FilterTextBox.Text in FacilityText),"StartDate", Ascending)

 

Any thoughts you could share would be much appreciated. The SortByColumns feature is a requirement as I only want the app to show opportunities that haven't been filled.

 

Sincerely, 

Justin

21 REPLIES 21

@WarrenBelz - Here is a screenshot. The highlighted true is the label confirming the IsBlank(DatePicker1). 

 

4-16-2020 9-00-00 PM.png

Hi @voglejm ,

I never give up any anything like this - I have actually never needed to test a blank date picker for a filter, but this another bizarre PowerApps issue on dates where a true value does not work as it should.
The solution I believe is this - I have tested it on similar data and it works.

SortByColumns(
   Filter(
      Support_Needs_List,
      If(
         Value(DatePicker1.SelectedDate) = 0,
         IsBlank(Support_Resource)  &&
         (
            IsBlank(FilterTextBox.Text) || 
            FilterTextBox.Text in FacilityText
         ),
         IsBlank(Support_Resource)  &&
         (
            IsBlank(FilterTextBox.Text) || 
            FilterTextBox.Text in FacilityText
         ) &&
         DateDiff(
            StartDateOnly,
            DatePicker1.SelectedDate,
            Days
         )=0
      )
   ),
   "StartDate", 
   Ascending
)

 

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 - It must be a condition in my list because I continue to get the same results.

4-17-2020 8-14-51 AM.png

Any thoughts on maybe what I could look at in my data since it's working for you?

Hi @voglejm ,

You must have copied that very quickly - I made an error in the transposition to your values, but fixed it a couple of minutes later. (I had the logic backwards)

SortByColumns(
   Filter(
      Support_Needs_List, 
      If(
         Value(DotePicfeerJ.SelectedDate) = 0, 
         IsBlank(Support_Resource) && 
         (
            IsBlank(FilterTextBox.Text) || 
            Filter TextBox.Text in FacilityText 
         ),
         IsBlank(Support_Resource) && 
         (
            IsBlank(FilterTextBox.Text) || 
            FilterTextBox.Text in FacilityText
         ) && 
         DateDiff(
            StartDateOnly, 
            DatePickerl.SelectedDate, 
            Days
         )=0, 
      )
   ),
   "StartDate", 
   Ascending
)

This really should work, however I can also see a Delegation error on your screenshot - this will be the date filter. There are workarouds for this if you need to.

 

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 - With this updated code I seem to have some errors. I updated a few pieces including the DatePicker1 spelling and FilterTextBox. Do you believe the delegation issue would be causing these problems since it's working for you?

 

SortByColumns(Filter(Support_Needs_List, If(Value(DatePicker1.SelectedDate)=0, IsBlank(Support_Resource) && (IsBlank(FilterTextBox.Text) || FilterTextBox.Text in FacilityText), IsBlank(Support_Resource) && ( IsBlank(FilterTextBox.Text) || FilterTextBox.Text in FacilityText ) && DateDiff(StartDateOnly, DatePicker1.SelectedDate, Days)=0,)),"StartDate", Ascending )

 

4-17-2020 4-32-54 PM.png

Hi @voglejm ,

Bracket in wrong place - I am free-typing here and have to rely on you a bit to understand the logic and fix these if required.

SortByColumns(
   Filter(
      Support_Needs_List,
      If(
         Value(DatePicker1.SelectedDate)=0, 
         IsBlank(Support_Resource) && 
         (
            IsBlank(FilterTextBox.Text) || 
            FilterTextBox.Text in FacilityText
         ), 
         IsBlank(Support_Resource) && 
         (
            IsBlank(FilterTextBox.Text) || 
            FilterTextBox.Text in FacilityText
         ) && 
         DateDiff(
            StartDateOnly, 
            DatePicker1.SelectedDate, 
            Days
         )=0
      )
   ),
   "StartDate", 
   Ascending
)

Try this first, but you have actually two non-Delegable filters in there - the Date and In filters.

If you will always have less than 2000 records, you can do a straight collect

ClearCollect(colSupport,Support_Needs_List)

and all the filters will work on the collection.

 

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.

 

Hi @voglejm ,

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.

@WarrenBelz - Thanks for following up. I definitely don't want to be a burden; however, I'm still receiving the same results. The gallery is blank and when I manipulate either the text search or the date picker, no records are presented in the gallery. At this point I think I'd be happy to switch the text search to the date picker while sorting by columns any fields that don't have a resource assigned in the support resource column. It just seems my original ask may not be possible. 

 

4-20-2020 8-57-43 AM.png

Hi @voglejm ,

I have tested this again here with the exact syntax and a date picker and it works. I can see a warning on your gallery - what is this?

Also can you please put a label on the screen while all this is happening with the Text

Value(DatePickerl.SelectedDate)=0

and see if it is true when there is no date in the control.

@WarrenBelz - I think I figured it out. I added the list to a collection and things seem to be working. 

 

4-21-2020 11-23-52 AM.png

 

I do have one more question. How would I reset the Date Picker back to blank after someone submits an entry? Should I utilize the signup button for this or would you suggest another way? 

 

4-21-2020 11-26-27 AM.png

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (3,581)