No Data in Gallery after Filter by TextBox and DatePicker

[akg1421]

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 @Anonymous @Feiteira @lg270492 @BCBuizer @jed76 @Drrickryp 

[poweractivate]

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

[poweractivate]

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

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

[poweractivate]

@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,...)

[poweractivate]

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

[akg1421]

@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"))

[akg1421]

@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"))

[poweractivate]

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

[akg1421]

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