Power Apps Exchange

Please login or click SIGN UP FOR FREE to create your PowerAppsUG account to join this user group.
 View Only
  • 1.  Filter multi selection choices column with selectmultiple combobox

    Posted Jul 13, 2021 08:31 AM

    Hi,

     

    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: subtasksFilter

    subtasksFilter items: 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

    Top Contributor
    Posted Jul 15, 2021 07:46 AM
    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
    ------------------------------



  • 3.  RE: Filter multi selection choices column with selectmultiple combobox

    Posted Jul 15, 2021 08:12 AM
    Edited by J J Jul 15, 2021 09:10 AM

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

    I put here solution from a video.

    App OnStart:

    ClearCollect(
        colItems,
        TestList2
    );
    ClearCollect(
        colOptions,
        RenameColumns(
            AddColumns(
                ["dog", "chicken", "pet", "boswell", "young"],
                "IsChoosen",
                true
            ),
            "Value",
            "OptionName"
        )
    );


    ComboBox1_2 Items:

    colOptions


    Reset icon OnSelect:

    ClearCollect(colYourItems, colItems);
    Reset(ComboBox1_2);



    Filter button 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
                )
            )
        )
    );



    Gallery Items:

    colYourItems




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



  • 4.  RE: Filter multi selection choices column with selectmultiple combobox

    Top Contributor
    Posted Jul 15, 2021 05:16 PM
    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
    ------------------------------



  • 5.  RE: Filter multi selection choices column with selectmultiple combobox

    Posted Jul 16, 2021 02:39 AM
    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
    ------------------------------



  • 6.  RE: Filter multi selection choices column with selectmultiple combobox

    Top Contributor
    Posted Jul 16, 2021 03:44 AM
    It 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
    ------------------------------