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:
DatePicker is selected and SearchBox has text
If(!IsBlank(DatePicker1.SelectedDate) && !IsBlank(SearchBox.Text)
!IsBlank(DatePicker1.SelectedDate) && IsBlank(SearchBox.Text)
IsBlank(DatePicker1.SelectedDate) && !IsBlank(SearchBox.Text)
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
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
)
)
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.
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.
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"))
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?