cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
akg1421
Helper III
Helper III

No Data in Gallery after Filter by TextBox and DatePicker

Using With and Filter in formula to get data from collection "mycollection1"  on basis of a Textbox: "SearchBox" and a DatePicker: "DatePicker1" to show in my gallery control

 

 

With({mycollection1:SortByColumns(mycollection1,"Due date time", Descending)
}, 
      If(!IsBlank(DatePicker1.SelectedDate) && !IsBlank(SearchBox.Text),
          Filter(
                mycollection1,
				SearchBox.Text in "FullNameOriginator" ||
				SearchBox.Text in "User (Originator)" ||
				SearchBox.Text in "Subject" ||
				SearchBox.Text in "Work item instructions" ||
				SearchBox.Text in "MenuItemName" &&
                'Due date time' = DatePicker1.SelectedDate
            ),
			!IsBlank(DatePicker1.SelectedDate) && IsBlank(SearchBox.Text), 
          Filter(
                mycollection1,
                'Due date time' = DatePicker1.SelectedDate
            ),
           IsBlank(DatePicker1.SelectedDate) && !IsBlank(SearchBox.Text),
          Filter(
                mycollection1,
				SearchBox.Text in "FullNameOriginator" ||
				SearchBox.Text in "User (Originator)" ||
				SearchBox.Text in "Subject" ||
				SearchBox.Text in "Work item instructions" ||
				SearchBox.Text in "MenuItemName"
            ),
           mycollection1
       )
)

 

 

Collection is filtered through four cases:

  1.  DatePicker is selected and SearchBox has text

 

If(!IsBlank(DatePicker1.SelectedDate) && !IsBlank(SearchBox.Text)

 

  • DatePicker is selected and SearchBox has no text 

 

!IsBlank(DatePicker1.SelectedDate) && IsBlank(SearchBox.Text)​

 

  • DatePicker is not selected and SearchBox has text 

 

IsBlank(DatePicker1.SelectedDate) && !IsBlank(SearchBox.Text)​

 

  • DatePicker is not selected and SearchBox has no text then "mycollection1" is returned

 

Gallery control however, is not showing any data be default i.e. 4th when no filter is applied. Rest of the conditions also not working. Any better way to Filter based on Text Input and a Date Picker?

 

@RandyHayes @WarrenBelz @poweractivate @BCBuizer @timl @Drrickryp @iAm_ManCat @Manan-Malhotra @victorcp @VJR @NandiniBhagya20 @eka24 @EricBLott @theapurva @StinkyD01 @Feiteira @lg270492 @BCBuizer @jed76 @Drrickryp 

10 REPLIES 10

@akg1421 

 

Try like this. I'll also suggest some simpler steps to try first:

With
(
      {mycollection1:SortByColumns(mycollection1,"Due date time", Descending)}
	  ,If
	  (
	        !IsBlank(DatePicker1.SelectedDate) && !IsBlank(SearchBox.Text)
           ,Filter
		    (
                 mycollection1
				,
				(
				    SearchBox.Text in "FullNameOriginator" ||
				    SearchBox.Text in "User (Originator)" ||
				    SearchBox.Text in "Subject" ||
				    SearchBox.Text in "Work item instructions" ||
				    SearchBox.Text in "MenuItemName"
                 )	
                 &&
                 (				 
                    'Due date time' = DatePicker1.SelectedDate
				 )
            )
		   ,!IsBlank(DatePicker1.SelectedDate) && IsBlank(SearchBox.Text)
		   ,Filter
			 (
                  mycollection1
                ,'Due date time' = DatePicker1.SelectedDate
             )
		   ,IsBlank(DatePicker1.SelectedDate) && !IsBlank(SearchBox.Text)
		   ,Filter
			(
                 mycollection1
				,SearchBox.Text in "FullNameOriginator" ||
			 	 SearchBox.Text in "User (Originator)" ||
				 SearchBox.Text in "Subject" ||
				 SearchBox.Text in "Work item instructions" ||
				 SearchBox.Text in "MenuItemName"
            )
           ,mycollection1
       )
)

@akg1421 

 

If the above did not work, try to break it down like this:

 

1.Does this filter work by itself?

With
(
        {mycollection1:SortByColumns(mycollection1,"Due date time", Descending)}
	   ,Filter
		(
			 mycollection1
			,
			(
				SearchBox.Text in "FullNameOriginator" ||
				SearchBox.Text in "User (Originator)" ||
				SearchBox.Text in "Subject" ||
				SearchBox.Text in "Work item instructions" ||
				SearchBox.Text in "MenuItemName"
			 )	
			 &&
			 (				 
				'Due date time' = DatePicker1.SelectedDate
			 )
		)
)

 2. Repeat #1 for the other filter.

RandyHayes
Super User
Super User

@akg1421 

You are referencing a With scoped table and a collection table with the same name.

Either way, you don't need any of that.

 

Please consider changing your Formula to the following:

SortByColumns(
    Filter(mycollection1,
        IsBlank(SearchBox.Text) || SearchBox.Text in $"{FullNameOriginator}|{{'User (Originator)'}|{Subject}|{'Work item instructions'}|{MenuItemName}",
        IsBlank(DatePicker1.SelectedDate) ||'Due date time' = DatePicker1.SelectedDate
    ),
    "Due date time", 
    Descending
)

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@akg1421 

 

And if the above does not work, first check this line in your With

 

{mycollection1:SortByColumns(mycollection1,"Due date time", Descending)}

 

I do not recommend the mycollection1 refers to itself on both the left and right side.

Instead change the name of the one on the left

 

{myTEMPcollection1:SortByColumns(mycollection1,"Due date time", Descending)}

 

  and then adjust the Filter formula

 

Filter(myTEMPcollection1,...)

 

 

Also as another troubleshooting step, try it with mycollection1 without the SortByColumns  and without the With - does it help?

 

Filter(mycollection1,...)

 

@akg1421 I would recommend you to refer to @RandyHayes response, it may be a better approach.

@poweractivate Nothing shows in gallery. Tried both the approaches you recommended.

 

Although it works seamlessly without the DatePicker (which is required) through this formula:

 

 

If(IsBlank(SearchBox.Text),SortByColumns(mycollection1,"Due date time",Descending),Search(mycollection1,SearchBox.Text,"FullNameOriginator","User (Originator)","Subject","Work item instructions","MenuItemName"))

 

 

 

@RandyHayes 

Tried your formula but still nothing showed in gallery. Although it works seamlessly without the DatePicker (which is required) through this formula:

 

 

If(IsBlank(SearchBox.Text),SortByColumns(mycollection1,"Due date time",Descending),Search(mycollection1,SearchBoxDocType.Text,"FullNameOriginator","User (Originator)","Subject","Work item instructions","MenuItemName"))

 

 

 

@akg1421 You can try with @RandyHayes formula.

For this thread, if and when @RandyHayes is available next (or any one else) I would like them to assist you with the remainder of your issues, as my availability to check on this thread might be limited for the moment.

@poweractivate 
Yes, I tried @RandyHayes formula but it didn't work. Even though, there was no error in the formula itself, nothing showed in gallery. Any other work around for Datepicker used alongside a Textbox for filtering data? 

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,696)