Hello All,
To avoid 'in' delegation issue, I have created a Flexible height gallery which is getting filtered based on 2 dropdown values created in app as filter.
1st dropdown value is Status of request, Second dropdown is Group. The backend data is SharePoint list, and both "Status" and "Group" are single selection choice fields.
Now after filtering and getting this data, I am hiding and showing rows based on Text input field and a Combobox field. Text input field searches the "description" columns in SharePoint which are "TitleDetails" of the request and "DetailedDescription" of the request. I also have a combobox column in SharePoint called "Country" which I want to use to filter along with the textbox field. The code for gallery works fine
If(
drpdwnStatus.Selected.Value = "All",
If(
drpGroupSelection.Selected.Name = "All",
TotalList,
Filter(
TotalList,
GroupName = drpGroupSelection.Selected.Name
)
),
If(
drpGroupSelection.Selected.Name = "All",
Filter(
TotalList,
Status.Value = drpdwnStatus.Selected.Value
),
Filter(
TotalList,
GroupName = drpGroupSelection.Selected.Name,
Status.Value = drpdwnStatus.Selected.Value
)
)
)
There are multiple labels created in this gallery, which I have grouped together and then for the entire group, visible property is added as
If(
IsBlank(txtSearchBox),
true,
txtSearchBox.Text in ThisItem.'TitleDetails' || TextSearchBox_1.Text in ThisItem.DetailedDescription
)
Till this the code works fine. The moment I add the code as below, the gallery goes in error.
If(
IsBlank(txtSearchBox),
true,
txtSearchBox.Text in ThisItem.'TitleDetails' || txtSearchBox.Text in ThisItem.DetailedDescription && Concat(comboBoxCountry.SelectedItems,Value&",") in Concat(ThisItem.Country,Value&",")
)
Can someone please help me how to add the combobox piece to the code for hide/show of gallery rows?
Thanks,
AK
Solved! Go to Solution.
Updated based on your field names
With({galData: Filter(TotalList, IsBlank(comboBoxCountry.SelectedItems) || IsEmpty(comboBoxCountry.SelectedItems) || CountryDetails.Value = comboBoxCountry.SelectedItems.Value, drpGroupSelection.Selected.Value = "All" || ApplicationName = drpGroupSelection.SelectedText.Value)},If(Not(IsBlank(txtSearchBox.Text)),Search(galData,txtSearchBox.Text,"TitleDetails","DetailedDescription "),galData))
Please verify drpGroupSelection.SelectedText.Value & drpGroupSelection.Selected.Value which one has "All"
Example
With(
{
sData: Filter(
'Issue tracker',
IsBlank(ComboBox3.SelectedItems.Value) || ComboBox3.Selected.Value ="All" || IsEmpty(ComboBox3.SelectedItems) || Status.Value = ComboBox3.Selected.Value,
IsBlank(Dropdown3.Selected.Value) || Dropdown3.SelectedText.Value ="All" || Dropdown3.SelectedText.Value="Critical"
)
},
If(
Not(IsBlank(TextInput2.Text)),
Search(
sData,
TextInput2.Text,
"Title","Description"
),
sData
)
)
So I solved it by converting the multi select column values in a Text column in SharePoint. Then I just used a search command with a single value dropdown in powerapps instead of multi select combobox and everything worked.
With(
{
galData: Filter(
TotalList,
IsBlank(comboBoxCountry.SelectedItems) || IsEmpty(comboBoxCountry.SelectedItems),
drpGroupSelection.Selected.Value = "All" || ApplicationName = drpGroupSelection.Selected.Value
)
},
If(
Not(IsBlank(txtSearchBox.Text)),
Search(
galData,
txtSearchBox.Text,
"TitleDetails",
"DetailedDescription "
),
Not(drpCountry.Selected.Value = "All"),
Search(
galData,
drpCountry.Selected.Value,
"countryTextValues"
),
galData
)
)
In the below example filters using 1 dropdown, 1 date and 1 text search. All are optional
With(
{
sData: Filter(
'Issue tracker',
IsBlank(Dropdown3.Selected.Value) || IsEmpty(drpdwnStatus.Selected.Value) || Status.Value = drpdwnStatus.Selected.Value,
IsBlank(drpGroupSelection.SelectedItems.Value) || IsEmpty(drpGroupSelection.Selected.Value) || Status.Value = drpGroupSelection.Selected.Name
)
},
If(
Not(IsBlank(txtSearchBox.Text)),
Search(
sData,
txtSearchBox.Text,
"TitleDetails","DetailedDescription"
),
sData
)
)
Edit: Updated with your field(s)
Sample:
not sure you are using dropdown or combo. Just to show the different usage for dropdown and combo
{
sData: Filter(
'Issue tracker',
IsBlank(ComboBox3.SelectedItems.Value) || IsEmpty(ComboBox3.SelectedItems) || Priority.Value = ComboBox3.Selected.Value,
IsBlank(Dropdown3.Selected.Value) || IsEmpty(Dropdown3.Selected.Value) || Priority.Value = Dropdown3.Selected.Value
)
},
It is combo box as well dropdown. Currently in my setup it is 2 dropdowns, "Status
and "Group" along with 1 combobox "Country" and 1 search box "txtSearchBox". The solution which you have mentioned is more on lines about changing the entire gallery items formula. Let me try it out if this works.
This solution gives me error as "Incompatible types for comparison. These types can't be compared. Table, Table" for this piece CountryDetails.Value = comboBoxCountry.SelectedItems.Name
With({galData: Filter(TotalList, IsBlank(comboBoxCountry.SelectedItems.Name) || IsEmpty(comboBoxCountry.SelectedItems) || CountryDetails.Value = comboBoxCountry.SelectedItems.Name, drpGroupSelection.Selected.Name = "All" || ApplicationName = drpGroupSelection.Selected.Name)},If(Not(IsBlank(txtSearchBox.Text)),Search(galData,txtSearchBox.Text,"TitleDetails","DetailedDescription "),galData))
Will have to check if this later gives me delegation error or not as my dataset will go over 500 records and reach over 2000 by end of this year.
Please update to
comboBoxCountry.Selected.Value
drpGroupSelection.Selected.Value
Still
drpGroupSelection.Selected.Value = "All"
Updated based on your field names
With({galData: Filter(TotalList, IsBlank(comboBoxCountry.SelectedItems) || IsEmpty(comboBoxCountry.SelectedItems) || CountryDetails.Value = comboBoxCountry.SelectedItems.Value, drpGroupSelection.Selected.Value = "All" || ApplicationName = drpGroupSelection.SelectedText.Value)},If(Not(IsBlank(txtSearchBox.Text)),Search(galData,txtSearchBox.Text,"TitleDetails","DetailedDescription "),galData))
Please verify drpGroupSelection.SelectedText.Value & drpGroupSelection.Selected.Value which one has "All"
Example
With(
{
sData: Filter(
'Issue tracker',
IsBlank(ComboBox3.SelectedItems.Value) || ComboBox3.Selected.Value ="All" || IsEmpty(ComboBox3.SelectedItems) || Status.Value = ComboBox3.Selected.Value,
IsBlank(Dropdown3.Selected.Value) || Dropdown3.SelectedText.Value ="All" || Dropdown3.SelectedText.Value="Critical"
)
},
If(
Not(IsBlank(TextInput2.Text)),
Search(
sData,
TextInput2.Text,
"Title","Description"
),
sData
)
)
Thanks for lot for responding so quickly. Really appreciate you spending your time on this! I am still getting the error for Table, Table comparision. If I remove that section, code works great which means combo box filter is not available for the gallery. Here is the updated code I am using which works great.
With(
{
galData: Filter(
TotalList,
IsBlank(comboBoxCountry.SelectedItems) || IsEmpty(comboBoxCountry.SelectedItems),
drpGroupSelection.Selected.Value = "All" || ApplicationName = drpGroupSelection.Selected.Value
)
},
If(
Not(IsBlank(txtSearchBox.Text)),
Search(
galData,
txtSearchBox.Text,
"TitleDetails",
"DetailedDescription "
),
galData
)
)
The moment I add this, the code breaks
|| CountryDetails.Value = comboBoxCountry.SelectedItems.Value
Delegation warning. "Incompatible types for comparison. These types can't be compared. Table, Table"
Here CountryDetails is Multiselect checkbox column in SharePoint
and comboBoxCountry is Combobox in PowerApps with multi selection On.
So I solved it by converting the multi select column values in a Text column in SharePoint. Then I just used a search command with a single value dropdown in powerapps instead of multi select combobox and everything worked.
With(
{
galData: Filter(
TotalList,
IsBlank(comboBoxCountry.SelectedItems) || IsEmpty(comboBoxCountry.SelectedItems),
drpGroupSelection.Selected.Value = "All" || ApplicationName = drpGroupSelection.Selected.Value
)
},
If(
Not(IsBlank(txtSearchBox.Text)),
Search(
galData,
txtSearchBox.Text,
"TitleDetails",
"DetailedDescription "
),
Not(drpCountry.Selected.Value = "All"),
Search(
galData,
drpCountry.Selected.Value,
"countryTextValues"
),
galData
)
)
User | Count |
---|---|
256 | |
114 | |
95 | |
48 | |
38 |