cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SMurphy
Helper II
Helper II

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

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
SMurphy
Helper II
Helper II

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#'
    )
)

View solution in original post

5 REPLIES 5
cha_cha
Resident Rockstar
Resident Rockstar

Hello @SMurphy 

 

This is how I understand your issue:

1.jpg

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.

 

2.jpg

 

 

 

 

 

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.

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

 
bistek.space   @cha_bistek    @BisTekSpace 

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

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

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

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. 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,079)