You’re offline. This is a read only version of the page.
Hello experts,
I am trying to filter my gallery with four dropdowns, two combo boxes, two date pickers (for selecting a range of dates) & a textinput for contains search. My data source is SQL server and I am using created views for showing data and I have more than three hundred thousand records in it. Below is the formula that I am using but its not working. Also, the formula for my three dropdowns is as follows
Daypart_Dropdown_1 - (Items): ForAll(Split(("All," & Concat( ForAll(Distinct(vw_Job_List,DayPart ), {Result: ThisRecord.Value}), Result&",")), ","), {Result: ThisRecord.Value})
Season_Dropdown_1 - (Items): ForAll(Split(("All," & Concat( ForAll(Distinct(vw_Job_List,Season ), {Result: ThisRecord.Value}), Result&",")), ","), {Result: ThisRecord.Value})
Brand_Dropdown_1 - ForAll(Split(("All," & Concat( ForAll(Distinct(vw_Job_List,Brand ), {Result: ThisRecord.Value}), Result&",")), ","), {Result: ThisRecord.Value})
Category_Dropdown_1 - ForAll(Split(("All," & Concat( ForAll(Distinct(vw_Job_List,Category ), {Result: ThisRecord.Value}), Result&",")), ","), {Result: ThisRecord.Value})
Photogrpaher_Combo_1 - (combobox) items - Another view was created just to get the names of the photographers in it.
and same goes for talent combo box.
the code for gallery is below
Share
ReportSearch(Filter(vw_Job_List,(Daypart_Dropdown_1.Selected.Result="All"||DayPart=Daypart_Dropdown_1.Selected.Result)&&(Season_Dropdown_1.Selected.Result="All"||Season=Season_Dropdown_1.Selected.Result)&&(Brand_Dropdown_1.Selected.Result="All"||Brand=Brand_Dropdown_1.Selected.Result)&&(Category_Dropdown_1.Selected.Result="All"||Category=Category_Dropdown_1.Selected.Result)&&(JobPhotographer=Photogrpaher_Combo_1.Selected.FullName)&&(TalentName=Talent_Combo_box_1.Selected.'Full Name')&& DateValue(ShootDate,"EN-us")>=DatePicker1_3.SelectedDate,DateValue(ShootDate,"EN-us")<=DatePicker1_4.SelectedDate),TextInput7_1.Text,"EpisodeTitle","JobPhotographer")But its not showing any results please do help me in fixing this code and how to avoid delegation warning so that it could work along all the records. Also I have added a screenshot for your reference
@BCBuizer @Drrickryp @CNT @TheRobRush @Rusk Hope you guys will help me out with this issue
Try my Filter code in your Gallery Items property by first only defining your search TextBox and the Column it will be searching on. Do this one by one adding in your combo boxes with additional and statements: ''&&". I always start as simple as I can get that working then slowly add complexity.
Using the Find function with the Filter Function returns the starting position so including the "<=1" at the end enables this method to function with multiple search items. You can reference The Filter and Find functions here: https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-filter-lookup
and here: https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-find
If you want to use a combo instead of a Textbox just add another line to the filter formula I referenced before.
like this :
All responses (
Answers ( (Find(ComboBox.Selected.Value, ColumnName) >=1)
Hey @coreypac , Thanks for replying, But I didn't understand what you share please could you elaborate more ?
Thanks
I built my screen with multiple Text boxes one per column to give the user the ability to filter the gallery by typing in each textbox. You may be able to adapt my code to meet your needs:
//This Code Goes into Gallery Items:
Filter(
CollectionName,
(Find(Lower(txtboxFilter1.Text), Lower(ColumnName1)) >=1) &&
(Find(Lower(txtboxFilter2.Text), Lower(ColumnName2)) >=1)
)
Hi @muzamil-baxture ,
Then you have a Delegation issue as the DateValue() function is not Delegable, however @BCBuizer has a good point that you may be able to leverage - it will be a bit more complex than this splitting out the text elements. I am not an SQL user either and am quite surprised that Dates have this issue, even if not in Text form.
@WarrenBelz , Thanks for replying I really appreciate your response. The date values are stored in form of text that's why I had to use the 'DateValue()' function, and this is giving me delegation warning. Is there a workaround for this as I have a very large dataset in SQL server ?
Hi @muzamil-baxture ,
Responding to the PM you sent - if your date values are stored as Dates, you should not need DateValue()
Search(
Filter(
vw_Job_List,
(
Daypart_Dropdown_1.Selected.Result = "All" ||
DayPart = Daypart_Dropdown_1.Selected.Result
) &&
(
Season_Dropdown_1.Selected.Result = "All" ||
Season = Season_Dropdown_1.Selected.Result
) &&
(
Brand_Dropdown_1.Selected.Result = "All" ||
Brand = Brand_Dropdown_1.Selected.Result
) &&
(
Category_Dropdown_1.Selected.Result = "All" ||
Category = Category_Dropdown_1.Selected.Result
) &&
JobPhotographer = Photogrpaher_Combo_1.Selected.FullName &&
TalentName = Talent_Combo_box_1.Selected.'Full Name' &&
ShootDate >= DatePicker1_3.SelectedDate,
ShootDate <= DatePicker1_4.SelectedDate
),
TextInput7_1.Text,
"EpisodeTitle",
"JobPhotographer"
)
Hi @muzamil-baxture ,
No expert on using SQL as a datasource here.
However, according to the documentation a workaround is to use calculated columns: SQL Server - Connectors | Microsoft Learn
@BCBuizer thanks for replying, My database is SQL server so Search function is delegable I don't have delegation warning for that, also its my requirement to have 'contains search' and as we know that can only be implemented by using search function. I have delegation warning at the date picker control, I think date functions are not delegable in it. So what could be the possible workaround for it ? as I have to deal with more than three hundred thousand records. Also its the requirement of the project to have that much data in it.
Hi @muzamil-baxture ,
As a first step, the Search function needs to be removed and replaced with StartsWith:
Filter(
vw_Job_List,
Daypart_Dropdown_1.Selected.Result="All"||DayPart=Daypart_Dropdown_1.Selected.Result,
Season_Dropdown_1.Selected.Result="All"||Season=Season_Dropdown_1.Selected.Result,
Brand_Dropdown_1.Selected.Result="All"||Brand=Brand_Dropdown_1.Selected.Result,
Category_Dropdown_1.Selected.Result="All"||Category=Category_Dropdown_1.Selected.Result,
JobPhotographer=Photogrpaher_Combo_1.Selected.FullName,
TalentName=Talent_Combo_box_1.Selected.'Full Name',
DateValue(ShootDate,"EN-us")>=DatePicker1_3.SelectedDate,
DateValue(ShootDate,"EN-us")<=DatePicker1_4.SelectedDate,
StartsWith("EpisodeTitle", TextInput7_1.Text) || StartsWith("JobPhotographer",TextInput7_1.Text)
)
If there are still delegation warnings, please share a screenshot with the blue squiggly lines to indicate which of the conditions are causing the issue.
#1
WarrenBelz
146,771
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
66,091
Most Valuable Professional
