Filter Gallery by Group

[Bcleaver]

I have an App that uses SharePoint as a data source and has a people column ('Assigned To') that can hold people and groups. While I can successfully filter the gallery in my app by the logged-in user, in some cases I would like to do this by groups too. I would like to achieve the following:

- The logged-in user sees any items in the gallery assigned to themselves or the group. For example, if User A is part of an Office 365 group called 'Test Group', then I would like any items assigned to the 'Test Group' to appear in User A's gallery along with other assigned items to User A.

Any suggestions would be much appreciated 🙂. 

[StalinPonnusamy]

Hi @Bcleaver 

Set the Items property of Gallery to

With(
    {_Item: [@SpSource]},
    Filter(
        _Item,
        'Assigned to'.Email = UserInfo.Email || UserInfo.Email in (Office365Groups.ListGroupMembers("SecurityGroupId").value.mail)
    )
)

Set App OnStart property to 

Set(UserInfo,User())

Example

With(
    {_Item: [@IssueTracker]},
    Filter(
        _Item,
        'Assigned to'.Email = UserInfo.Email || UserInfo.Email in (Office365Groups.ListGroupMembers("1b23d6d0-2374-4c16-9290-6c9eb9e11184").value.mail)
    )
)

Thanks,
Stalin - Learn To Illuminate

[Bcleaver]

Hi Stalin,

Thanks very much for the response. 

I've tried your suggested solution and unfortunately, it doesn't seem to work. I set a variable named 'VarUser' to get the logged-in user -Set(VarUser, User())- but I received the following error, 'Incompatible types for comparison. These types can't be compared: Table, Text'. 

I had this issue originally, so tried changing the formula to the following: Set(VarUser, Office365Users.MyProfileV2().mail). When using this formula to get the logged-in user, the formula in the items property of the gallery is accepted but none of the actions assigned to a group seem appear. I also amended your suggested formula slightly by using 'VarUser in Assigned.Email' (I appreciate this may be incorrect). Formula listed below for reference:

With(
        {_Item: [@'SP List']},
           Filter(
                   _Item,
                     VarUser in Assigned.Email  ||   VarUser in Office365Groups.ListGroupMembers("GroupID").value.mail
           )
)

Other useful info that I didn't include earlier may be that my SharePoint list people column can contain multiple users and groups.

Any further suggestions? Thanks again for the original response.

[StalinPonnusamy]

Hi @Bcleaver 

We can filter the gallery If the person belongs to only one security group like below 

With(
    {_Item: [@'SP List']},
    Filter(
        _Item,
        VarUser in ( Concat(
            Assigned.Email,
            Email,
            "&"
        )) || ("development@ltiblogs.onmicrosoft.com" in ( Concat(
            Assigned.Email,
            Email,
            "&"
        )) && VarUser in (Office365Groups.ListGroupMembers("1a23d6d0-6574-4c16-9290-6c9eb9e10984").value).mail)
    )
)

Security group
Name: development@ltiblogs.onmicrosoft.com
GUID: 1a23d6d0-6574-4c16-9290-6c9eb9e10984

Please refer to the below example, User may belong to multiple groups. We may not filter the gallery directly since N:N like below

In this case, we may need to use the collection to loop through the multi-select person list with the groups that users belong to.

[Bcleaver]

Hi Stalin,

Thanks once again for your response. 

I tried the above formula and while the formula is accepted, it again only seems to return SharePoint list items that are assigned to the logged in user and no items assigned to the group that the logged-in user is part of. It would just be the one group that is necessary (at least for now).

You mention security groups in your solution, could it be that the group in question is a standard Office 365 group rather than a security group/mail-enabled security group? Could there be anything in the make-up of the SharePoint site or Group set up that is blocking this from happening as desired do you think?

Any further suggestions would be much appreciated. Thanks again for all your help.

[StalinPonnusamy]

Hi @Bcleaver 

Try this. This should list only the items that user belongs to the Security group

With(
    {_Item: [@IssueTracker]},
    Filter(
        _Item,
        ("development@ltiblogs.onmicrosoft.com" in ( Concat(
            MultiplePeople.Email,
            Email,
            "&"
        )) && UserInfo.Email in (Concat(
            Office365Groups.ListGroupMembers("1a23d6d0-6574-4c16-9290-6c9eb9e10984").value,
            mail,
            ","
        )))
    )
)

Note: Change the Name and the guid

[Bcleaver]

Thanks once again for the response.

Unfortunately, it still doesn't seem to be working as desired. The suggested formula does seem to target any list items that belong to that group (as shown by the results in the formula bar itself - see screenshot. T27 is an action assigned to a group), but nothing appears in the gallery, either as an item assigned to the logged-in user or assigned to a group that the logged-in user is part of.

No errors appear in the formula but it still seems to return a blank gallery. Any other suggestions as to where I might be going wrong? Thanks again for your help.

[StalinPonnusamy]

Hi @Bcleaver 

To troubleshoot further

Set the items property of Gallery to SPList (No filter for now)

Group exist in SP

1. Insert the label inside the gallery and set the Text property to

"development@ltiblogs.onmicrosoft.com" in (Concat(ThisItem.Assignedto.Email,Email,","))

User exists on that Group

2. Insert another label and set the text property to

UserInfo.Email in (Concat(Office365Groups.ListGroupMembers("1a23d6d0-6574-4c16-9290-6c9eb9e10984").value,mail,","))

List all members on that SP security group

3. Insert another Label and set the text property to

Concat(Office365Groups.ListGroupMembers("1a23d6d0-6574-4c16-9290-6c9eb9e10984").value.mail,mail & ",")

Reference 

Now you should be able to see which link (connection/data) is missing

Thanks,
Stalin - Learn To Illuminate

[Bcleaver]

Hi Stalin,

Thanks very much for the response.

I carried out your suggestions to further diagnose the issue and found something interesting. Connections-wise, it all appears ok however some elements of the gallery item seem to be hidden. I believe this hidden item to be the one group assigned item on the list. The labels I put in place as per your suggestion seem to appear ok but nothing else does (see screenshot - in the box outlined in the screenshot I would expect to see a clipboard and other info as stated in the item above):

Any ideas on why this seems to be the case? Again, given that I have 2 'trues' and have successfully listed group members it would suggest that this 'hidden' item is the one assigned to the group.

Thanks once again for all your help.

[StalinPonnusamy]

Hi @Bcleaver 

1. I assume no filter on the Items property when you are testing above right.
2. Both true means, user exists on the group and group assigned to SP item. Is it correct?
3. When you are listing members on that group, does list your email id?

Thanks,
Stalin - Learn To Illuminate