Filtering a Gallery based on a multi select lookup column (Sharepoint)

[SMurphy]

Hi, I am trying to filter a gallery which is on Table1(tblProjects) with values from table 2 (tbl_Project_Team_Alignment) with values from a multi select lookup field. I have successfully done this with a normal single select lookup column but i am struggling with the multi select field.   Below is how i am filtering the gallery based on the lookup column in table 2, this is working. 

Filter(
        tblProjects,
        'Project#' in Filter(tblProject_Team_Alignment, IsBlank(cmbWorkingGroup.Selected) || IsEmpty(cmbWorkingGroup.Selected) || Primary_Working_Group.Value = cmbWorkingGroup.Selected.Result).'Project#'
    )

In order to have the multiselect look up field display in the gallery i am using the code: 

With(
    {
        ProductString: Concat(
            LookUp(
                tblProject_Team_Alignment,
                'Project#' = ThisItem.'Project#'
            ).Associated_Product.Value,
            Value,
            " ,"
        )
    },
    Mid(
        ProductString,
        1,
        Len(ProductString)
    )
)

Now i am trying to be able to filter the gallery based on those values, i would likely use a text input as a search bar. I am struggling to figure out how to get there. I think i may need to do a ForAll Collection but i am struggling creating that as well. Any help on possible solutions would be great. Thanks. 

[cha_cha]

Hello @SMurphy 

This is how I understand your issue:

And this is how I would I approach this given that you might need a quicker solution. I normally do this when I don't want to make another DataSource call. I would be your second gallery or make another gallery with the same Item code as the 2nd table then use it as a source for the actual second gallery.

Search(TEMP.AllItems,SearchBox.Text, "name")

//Replace name with the appropriate column name

---

✔️	Just in case you think my answer helped you solve your problem, please mark this as a SOLUTION.   This helps community members if they experience a similar issue in the future.

🔗	🕸  bistek.space  🐦 @cha_bistek  📺  @BisTekSpace

👤

Hello, I'm still new to Power Platform (since May 2022 👶) but I have 10+ years of working in the IT field and I'm really glad whenever I can help. The more I help, the more I learn.  I sometimes record my answers via my channel. I have limited spare time so will only do for ones that can be done in a short period. If my answers/interpretations are wrong, please correct me.

[SMurphy]

Thank you for the response. I may have confused you in the way i explained it. I only have 1 gallery that is referencing two tables. The main table is tblProjects and the other is tblProject_Team_Alignment. The 2nd piece of code is a label within the gallery to pull data from the second table. 

[SMurphy]

I think if I were able to create a collection of all the values within the multi select lookup field I would be able to figure out how to filter from there. I am struggling with this as well, any recommendations? Thanks. 

[SMurphy · Accepted solution]

I was able to create a collection and filter the gallery by referencing that collection. 

Clear(colAssociatedTeams);ForAll(tblProject_Team_Alignment,Collect(colAssociatedTeams,{'Project#':'Project#',Workstream:Concat(Associated_Workstream.Value, Value, ", "),Product:Concat(Associated_Product.Value, Value, ", "),Process:Concat(Associated_POWG.Value, Value, ", ")}))

My gallery is filtered as such. 

If(
    Toggle1.Value = false,
    Filter(
        tblProjects,
        Portfolio_Status.Value = "Active" || IsBlank(Portfolio_Status.Value),
        IsBlank(cmbProjectOwner_1.SelectedItems) || IsEmpty(cmbProjectOwner_1.SelectedItems) || (Project_Owner.DisplayName in cmbProjectOwner_1.SelectedItems.Result),
        IsBlank('cmbProject#'.SelectedItems) || IsEmpty('cmbProject#'.SelectedItems) || ('Project#' = 'cmbProject#'.Selected.Result),
        'Project#' in Filter(
            tblProject_Team_Alignment,
            IsBlank(cmbWorkingGroup.Selected) || IsEmpty(cmbWorkingGroup.Selected) || Primary_Working_Group.Value = cmbWorkingGroup.Selected.Result
        ).'Project#',
        'Project#' in Filter(
            colAssociatedTeams,
            TeamsSearch.Text in Product || TeamsSearch.Text in Workstream || TeamsSearch.Text in Process
        ).'Project#'
    ),
    Filter(
        tblProjects,
        IsBlank(cmbProjectOwner_1.SelectedItems) || IsEmpty(cmbProjectOwner_1.SelectedItems) || (Project_Owner.DisplayName in cmbProjectOwner_1.SelectedItems.Result),
        IsBlank('cmbProject#'.SelectedItems) || IsEmpty('cmbProject#'.SelectedItems) || ('Project#' = 'cmbProject#'.Selected.Result),
        'Project#' in Filter(
            tblProject_Team_Alignment,
            IsBlank(cmbWorkingGroup.Selected) || IsEmpty(cmbWorkingGroup.Selected) || Primary_Working_Group.Value = cmbWorkingGroup.Selected.Result
        ).'Project#',
        'Project#' in Filter(
            colAssociatedTeams,
            TeamsSearch.Text in Product || TeamsSearch.Text in Workstream || TeamsSearch.Text in Process
        ).'Project#'
    )
)

[SMurphy]

The above code has been working has been working as intended though i have recently noticed some slowness within the app and suspect it's the new code. The collection is on visible for the screen with the gallery. Any recommendations on how to make this more efficient? Thanks.