Power Apps Exchange
- Community Home
- Discussion 3.9K
- Library 113
- Events 0
- Members 23.3K
View My Drafts
Filter multi selection choices column with selectmultiple combobox
J JJul 13, 2021 08:31 AM
Warren BelzJul 15, 2021 07:46 AM
J JJul 15, 2021 08:12 AM
Warren BelzJul 15, 2021 05:16 PM
J JJul 16, 2021 02:39 AM
Warren BelzJul 16, 2021 03:44 AM
1. Filter multi selection choices column with selectmultiple combobox
0 RecommendJ JPosted Jul 13, 2021 08:31 AMReply Reply Privately Options DropdownHi,
I try to create a tool to filter gallery with choices column.
Here is my dataverse table:
Here are my settings:
table name:'Tasks Infos'
combobox name:subtasksFiltersubtasksFilteritems: Choices['Tasks Infos'.Subtasks]
gallery items:
Filter['Tasks Infos'; Concat[subtasksFilter.SelectedItems; Value & ","] in Concat[Subtasks; Value & ","]]
When I used this formula records are hidden when filter is not enabled and doesn't work properly when combobox selection has wrong order. Here is what it looks like:
I would assume that by selecting 1, 3 gallery displays:
What is wrong with this formula? I tried before with Sharepoint list with choice that allow multiple selections but it works the same and also I read that sharepoint list has more limitation and dataverse is the database that I should use.
------------------------------
J J
------------------------------2. RE: Filter multi selection choices column with selectmultiple combobox
0 RecommendTop ContributorWarren BelzPosted Jul 15, 2021 07:46 AMReply Reply Privately Options DropdownHi @ JJ,
You have a many-to-many filter there, which cannot be resolved with a single filter. You have to loop through one lot of Selected items and do a collection on the result of comparing each item with the second table. I have a blog on the process that may assist you.
------------------------------
Warren Belz
Queensland Australia
------------------------------Original MessageOriginal Message:
Sent: Jul 13, 2021 08:30 AM
From: J J
Subject: Filter multi selection choices column with selectmultiple comboboxHi,
I try to create a tool to filter gallery with choices column.
Here is my dataverse table:
Here are my settings:
table name:'Tasks Infos'
combobox name:subtasksFiltersubtasksFilteritems: Choices['Tasks Infos'.Subtasks]
gallery items:
Filter['Tasks Infos'; Concat[subtasksFilter.SelectedItems; Value & ","] in Concat[Subtasks; Value & ","]]
When I used this formula records are hidden when filter is not enabled and doesn't work properly when combobox selection has wrong order. Here is what it looks like:
I would assume that by selecting 1, 3 gallery displays:
What is wrong with this formula? I tried before with Sharepoint list with choice that allow multiple selections but it works the same and also I read that sharepoint list has more limitation and dataverse is the database that I should use.
------------------------------
J J
------------------------------3. RE: Filter multi selection choices column with selectmultiple combobox
0 RecommendJ JPosted Jul 15, 2021 08:12 AM
Edited by J J Jul 15, 2021 09:10 AMReply Reply Privately Options DropdownI have tried this solution and it works but it is not ideal solution. It forces to use single line text as a source and not choices. Adding additional combobox makes the function looks like spaghetti.
ClearCollect[ colItems, TestList2 ]; ClearCollect[ colOptions, RenameColumns[ AddColumns[ ["dog", "chicken", "pet", "boswell", "young"], "IsChoosen", true ], "Value", "OptionName" ] ];
I put here solution from a video.
App OnStart:
ComboBox1_2 Items:
ClearCollect[colYourItems, colItems]; Reset[ComboBox1_2];
Reset icon OnSelect:
// diselect all options UpdateIf[colOptions, true, {IsChoosen: false}]; // change IsChoosen to true for selected items from combobox ForAll[ ComboBox1_2.SelectedItems As _item, If[_item in colOptions, Patch[colOptions, _item, {IsChoosen: true}]] ]; // create empty collection that match exact Clear[colItemsToShowExact]; // add items to exact collection based on IsChoosen ForAll[ Filter[ colOptions, IsChoosen ] As FilterChoices, Collect[ colItemsToShowExact, Filter[ colItems, FilterChoices.OptionName in ItemType ] ] ]; // remove duplicated items from exact collection ClearCollect[ colYourItems, Filter[ GroupBy[ colItemsToShowExact, "ItemName", "ItemType", "Color", "DATA" ], CountRows[DATA] = CountRows[ Filter[ colOptions, IsChoosen ] ] ] ];
Filter button OnSelect:
colYourItems
Gallery Items:
I will check your blog and try your solution. Thanks.
-- edit
Your solution works for ANY filter, I need EXACT filter or at least add another multi select combo box. Do you know how to modify it for this needs?
------------------------------
J J
------------------------------Original MessageOriginal Message:
Sent: Jul 15, 2021 07:46 AM
From: Warren Belz
Subject: Filter multi selection choices column with selectmultiple combobox
Hi @ JJ,
You have a many-to-many filter there, which cannot be resolved with a single filter. You have to loop through one lot of Selected items and do a collection on the result of comparing each item with the second table. I have a blog on the process that may assist you.
------------------------------
Warren Belz
Queensland Australia
Original Message:
Sent: Jul 13, 2021 08:30 AM
From: J J
Subject: Filter multi selection choices column with selectmultiple comboboxHi,
I try to create a tool to filter gallery with choices column.
Here is my dataverse table:
Here are my settings:
table name:'Tasks Infos'
combobox name:subtasksFiltersubtasksFilteritems: Choices['Tasks Infos'.Subtasks]
gallery items:
Filter['Tasks Infos'; Concat[subtasksFilter.SelectedItems; Value & ","] in Concat[Subtasks; Value & ","]]
When I used this formula records are hidden when filter is not enabled and doesn't work properly when combobox selection has wrong order. Here is what it looks like:
I would assume that by selecting 1, 3 gallery displays:
What is wrong with this formula? I tried before with Sharepoint list with choice that allow multiple selections but it works the same and also I read that sharepoint list has more limitation and dataverse is the database that I should use.
------------------------------
J J
------------------------------4. RE: Filter multi selection choices column with selectmultiple combobox
0 RecommendTop ContributorWarren BelzPosted Jul 15, 2021 05:16 PMReply Reply Privately Options DropdownMy post is really a broad guide [and I see you have a similar structure] of many-to-many comparisons. I have used a combo box selected items, [which are a one-field table, so ForAll will get you individual values to compare] to allow an in Filter with a field in another Table.
Bear in bind that the target could be a text string and you are simply looking for the content of each item somewhere in the field. You could also use Split[] on a delimited list to produce a table on the first item. Does this assist?
------------------------------
Warren Belz
Queensland Australia
------------------------------Original MessageOriginal Message:
Sent: Jul 15, 2021 08:12 AM
From: J J
Subject: Filter multi selection choices column with selectmultiple comboboxI have tried this solution and it works but it is not ideal solution. It forces to use single line text as a source and not choices. Adding additional combobox makes the function looks like spaghetti.
ClearCollect[ colItems, TestList2 ]; ClearCollect[ colOptions, RenameColumns[ AddColumns[ ["dog", "chicken", "pet", "boswell", "young"], "IsChoosen", true ], "Value", "OptionName" ] ];
I put here solution from a video.
App OnStart:
colOptions
ComboBox1_2 Items:
ClearCollect[colYourItems, colItems]; Reset[ComboBox1_2];
Reset icon OnSelect:
// diselect all options UpdateIf[colOptions, true, {IsChoosen: false}]; // change IsChoosen to true for selected items from combobox ForAll[ ComboBox1_2.SelectedItems As _item, If[_item in colOptions, Patch[colOptions, _item, {IsChoosen: true}]] ]; // create empty collection that match exact Clear[colItemsToShowExact]; // add items to exact collection based on IsChoosen ForAll[ Filter[ colOptions, IsChoosen ] As FilterChoices, Collect[ colItemsToShowExact, Filter[ colItems, FilterChoices.OptionName in ItemType ] ] ]; // remove duplicated items from exact collection ClearCollect[ colYourItems, Filter[ GroupBy[ colItemsToShowExact, "ItemName", "ItemType", "Color", "DATA" ], CountRows[DATA] = CountRows[ Filter[ colOptions, IsChoosen ] ] ] ];
Filter button OnSelect:
colYourItems
Gallery Items:
I will check your blog and try your solution. Thanks.
-- edit
Your solution works for ANY filter, I need EXACT filter or at least add another multi select combo box. Do you know how to modify it for this needs?
------------------------------
J J
Original Message:
Sent: Jul 15, 2021 07:46 AM
From: Warren Belz
Subject: Filter multi selection choices column with selectmultiple combobox
Hi @ JJ,
You have a many-to-many filter there, which cannot be resolved with a single filter. You have to loop through one lot of Selected items and do a collection on the result of comparing each item with the second table. I have a blog on the process that may assist you.
------------------------------
Warren Belz
Queensland Australia
Original Message:
Sent: Jul 13, 2021 08:30 AM
From: J J
Subject: Filter multi selection choices column with selectmultiple comboboxHi,
I try to create a tool to filter gallery with choices column.
Here is my dataverse table:
Here are my settings:
table name:'Tasks Infos'
combobox name:subtasksFiltersubtasksFilteritems: Choices['Tasks Infos'.Subtasks]
gallery items:
Filter['Tasks Infos'; Concat[subtasksFilter.SelectedItems; Value & ","] in Concat[Subtasks; Value & ","]]
When I used this formula records are hidden when filter is not enabled and doesn't work properly when combobox selection has wrong order. Here is what it looks like:
I would assume that by selecting 1, 3 gallery displays:
What is wrong with this formula? I tried before with Sharepoint list with choice that allow multiple selections but it works the same and also I read that sharepoint list has more limitation and dataverse is the database that I should use.
------------------------------
J J
------------------------------5. RE: Filter multi selection choices column with selectmultiple combobox
0 RecommendJ JPosted Jul 16, 2021 02:39 AMReply Reply Privately Options DropdownYes, it is true. But then to add additional combobox I would need to compare each collection with the previous one. What a nightmare. Anyway thanks for your advice :]
------------------------------
J J
------------------------------Original MessageOriginal Message:
Sent: Jul 15, 2021 05:16 PM
From: Warren Belz
Subject: Filter multi selection choices column with selectmultiple combobox
My post is really a broad guide [and I see you have a similar structure] of many-to-many comparisons. I have used a combo box selected items, [which are a one-field table, so ForAll will get you individual values to compare] to allow an in Filter with a field in another Table.
Bear in bind that the target could be a text string and you are simply looking for the content of each item somewhere in the field. You could also use Split[] on a delimited list to produce a table on the first item. Does this assist?
------------------------------
Warren Belz
Queensland Australia
Original Message:
Sent: Jul 15, 2021 08:12 AM
From: J J
Subject: Filter multi selection choices column with selectmultiple comboboxI have tried this solution and it works but it is not ideal solution. It forces to use single line text as a source and not choices. Adding additional combobox makes the function looks like spaghetti.
ClearCollect[ colItems, TestList2 ]; ClearCollect[ colOptions, RenameColumns[ AddColumns[ ["dog", "chicken", "pet", "boswell", "young"], "IsChoosen", true ], "Value", "OptionName" ] ];
I put here solution from a video.
App OnStart:
colOptions
ComboBox1_2 Items:
ClearCollect[colYourItems, colItems]; Reset[ComboBox1_2];
Reset icon OnSelect:
// diselect all options UpdateIf[colOptions, true, {IsChoosen: false}]; // change IsChoosen to true for selected items from combobox ForAll[ ComboBox1_2.SelectedItems As _item, If[_item in colOptions, Patch[colOptions, _item, {IsChoosen: true}]] ]; // create empty collection that match exact Clear[colItemsToShowExact]; // add items to exact collection based on IsChoosen ForAll[ Filter[ colOptions, IsChoosen ] As FilterChoices, Collect[ colItemsToShowExact, Filter[ colItems, FilterChoices.OptionName in ItemType ] ] ]; // remove duplicated items from exact collection ClearCollect[ colYourItems, Filter[ GroupBy[ colItemsToShowExact, "ItemName", "ItemType", "Color", "DATA" ], CountRows[DATA] = CountRows[ Filter[ colOptions, IsChoosen ] ] ] ];
Filter button OnSelect:
colYourItems
Gallery Items:
I will check your blog and try your solution. Thanks.
-- edit
Your solution works for ANY filter, I need EXACT filter or at least add another multi select combo box. Do you know how to modify it for this needs?
------------------------------
J J
Original Message:
Sent: Jul 15, 2021 07:46 AM
From: Warren Belz
Subject: Filter multi selection choices column with selectmultiple combobox
Hi @ JJ,
You have a many-to-many filter there, which cannot be resolved with a single filter. You have to loop through one lot of Selected items and do a collection on the result of comparing each item with the second table. I have a blog on the process that may assist you.
------------------------------
Warren Belz
Queensland Australia
Original Message:
Sent: Jul 13, 2021 08:30 AM
From: J J
Subject: Filter multi selection choices column with selectmultiple comboboxHi,
I try to create a tool to filter gallery with choices column.
Here is my dataverse table:
Here are my settings:
table name:'Tasks Infos'
combobox name:subtasksFiltersubtasksFilteritems: Choices['Tasks Infos'.Subtasks]
gallery items:
Filter['Tasks Infos'; Concat[subtasksFilter.SelectedItems; Value & ","] in Concat[Subtasks; Value & ","]]
When I used this formula records are hidden when filter is not enabled and doesn't work properly when combobox selection has wrong order. Here is what it looks like:
I would assume that by selecting 1, 3 gallery displays:
What is wrong with this formula? I tried before with Sharepoint list with choice that allow multiple selections but it works the same and also I read that sharepoint list has more limitation and dataverse is the database that I should use.
------------------------------
J J
------------------------------6. RE: Filter multi selection choices column with selectmultiple combobox
0 RecommendTop ContributorWarren BelzPosted Jul 16, 2021 03:44 AMReply Reply Privately Options DropdownIt was not clear from your original description what you were trying to do, however on seeing your end code, it is far more complex than a "normal" many-to-many exercise.
I am glad you got it sorted.
------------------------------
Warren Belz
Queensland Australia
------------------------------Original MessageOriginal Message:
Sent: Jul 16, 2021 02:39 AM
From: J J
Subject: Filter multi selection choices column with selectmultiple combobox
Yes, it is true. But then to add additional combobox I would need to compare each collection with the previous one. What a nightmare. Anyway thanks for your advice :]
------------------------------
J J
Original Message:
Sent: Jul 15, 2021 05:16 PM
From: Warren Belz
Subject: Filter multi selection choices column with selectmultiple combobox
My post is really a broad guide [and I see you have a similar structure] of many-to-many comparisons. I have used a combo box selected items, [which are a one-field table, so ForAll will get you individual values to compare] to allow an in Filter with a field in another Table.
Bear in bind that the target could be a text string and you are simply looking for the content of each item somewhere in the field. You could also use Split[] on a delimited list to produce a table on the first item. Does this assist?
------------------------------
Warren Belz
Queensland Australia
Original Message:
Sent: Jul 15, 2021 08:12 AM
From: J J
Subject: Filter multi selection choices column with selectmultiple comboboxI have tried this solution and it works but it is not ideal solution. It forces to use single line text as a source and not choices. Adding additional combobox makes the function looks like spaghetti.
ClearCollect[ colItems, TestList2 ]; ClearCollect[ colOptions, RenameColumns[ AddColumns[ ["dog", "chicken", "pet", "boswell", "young"], "IsChoosen", true ], "Value", "OptionName" ] ];
I put here solution from a video.
App OnStart:
colOptions
ComboBox1_2 Items:
ClearCollect[colYourItems, colItems]; Reset[ComboBox1_2];
Reset icon OnSelect:
// diselect all options UpdateIf[colOptions, true, {IsChoosen: false}]; // change IsChoosen to true for selected items from combobox ForAll[ ComboBox1_2.SelectedItems As _item, If[_item in colOptions, Patch[colOptions, _item, {IsChoosen: true}]] ]; // create empty collection that match exact Clear[colItemsToShowExact]; // add items to exact collection based on IsChoosen ForAll[ Filter[ colOptions, IsChoosen ] As FilterChoices, Collect[ colItemsToShowExact, Filter[ colItems, FilterChoices.OptionName in ItemType ] ] ]; // remove duplicated items from exact collection ClearCollect[ colYourItems, Filter[ GroupBy[ colItemsToShowExact, "ItemName", "ItemType", "Color", "DATA" ], CountRows[DATA] = CountRows[ Filter[ colOptions, IsChoosen ] ] ] ];
Filter button OnSelect:
colYourItems
Gallery Items:
I will check your blog and try your solution. Thanks.
-- edit
Your solution works for ANY filter, I need EXACT filter or at least add another multi select combo box. Do you know how to modify it for this needs?
------------------------------
J J
Original Message:
Sent: Jul 15, 2021 07:46 AM
From: Warren Belz
Subject: Filter multi selection choices column with selectmultiple combobox
Hi @ JJ,
You have a many-to-many filter there, which cannot be resolved with a single filter. You have to loop through one lot of Selected items and do a collection on the result of comparing each item with the second table. I have a blog on the process that may assist you.
------------------------------
Warren Belz
Queensland Australia
Original Message:
Sent: Jul 13, 2021 08:30 AM
From: J J
Subject: Filter multi selection choices column with selectmultiple comboboxHi,
I try to create a tool to filter gallery with choices column.
Here is my dataverse table:
Here are my settings:
table name:'Tasks Infos'
combobox name:subtasksFiltersubtasksFilteritems: Choices['Tasks Infos'.Subtasks]
gallery items:
Filter['Tasks Infos'; Concat[subtasksFilter.SelectedItems; Value & ","] in Concat[Subtasks; Value & ","]]
When I used this formula records are hidden when filter is not enabled and doesn't work properly when combobox selection has wrong order. Here is what it looks like:
I would assume that by selecting 1, 3 gallery displays:
What is wrong with this formula? I tried before with Sharepoint list with choice that allow multiple selections but it works the same and also I read that sharepoint list has more limitation and dataverse is the database that I should use.
------------------------------
J J
------------------------------