Search within a Collection

[RMD211]

I'm hoping someone can help me solve this issues I'm having.  What I have is:

3 collections creating OnStart - one called AllRoles, PGroupMembers and SGroupMembers.

Next what I want to do is set the current user role based on whether they are in one of those collections - how do I search within a collection? 

I've tried this so far:

I basically want to ask "Does varUserEmail exist in All Roles?  If so, set varRole as Member.  If no, Does varUserEmail exist in SGroupMembers? If yes, set as SMember. If no, set as PMember.

[WarrenBelz]

Hi @RMD211 ,

You need to lookup the field compared with the user's email

Set(
   varRole,
   If(
      !IsBlank(
         Lookup(
            SGroupMembers,
            mail = User.Email
         ).mail
      ),
      "SMember",
      "PMember"
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[RMD211]

This still doesn't work me for - the mail = Users.Email part is red underlined.

[WarrenBelz]

@RMD211 ,

You need to use whatever field contains the email address in AllRoles - I was using mail as it would be in the other two collections from the Group membership details.

[RMD211]

Still no joy I'm afraid! I've worked out that in my case varUserEmail is equal to UserPrincipleName but the formula isn't working at all. Neither the lookup in AllRoles or in SMembers is working:

[WarrenBelz]

Hi @RMD211 ,

Please post your code in Text - also rather than redacting, just change the name.

[RMD211]

Hello!

Thanks, see below my code - from the part which gathers the collections onward.  AllRoles is from a SharePoint list which uses a People or Group field

//Gather Members from Roles SharePoint list
ClearCollect(
    AllRoles,
    Roles
);
//Set Current User Properties
Set(
    varUserName,
    Office365Users.MyProfileV2().displayName
);
Set(
    varUserEmail,
    User().Email
);
//Collect Users in SGroup
ClearCollect(SGroup,Office365Groups.ListGroupMembers("54313431344").value)
;
//Collect Users in PGroup
ClearCollect(PGroup,Office365Groups.ListGroupMembers("8434431343").value)
;
//Set highest privilege role
Set(varRole,
    If(
        IsBlank(
            LookUp(AllRoles,varUserEmail = Users.Email).Users.Email),

    If(
        IsBlank(
            LookUp(SGroup, userPrincipalName = varUserEmail).userPrincipalName),
            "PGroup",
            "SGroup"
        )
        ,
            "Member"
        )

[WarrenBelz]

Hi @RMD211 ,

To save a few "one-use" Collections and Variables hanging around

With(
   {_SGroup: Office365Groups.ListGroupMembers("54313431344").value},
   Set(
      varRole,
      If(
         !IsBlank(
            LookUp(
               AllRoles,
               Users.Email = User().Email
            ).Users.Email
         )
      ),
      "Member",
      IsBlank(
         LookUp(
            _SGroup, 
            userPrincipalName = User().Email
         ).userPrincipalName
      ),
      "PGroup",
      "SGroup"
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[RMD211]

Sorry still no joy!! Everything is red underlined for me.  

Also my variable is not one-use, I use varUserEmail elsewhere so I'm happy to keep it in the formula.

[WarrenBelz]

@RMD211 ,

Comma missing - now fixed. Also User().Email is available everywhere, so you have an extra Variable to manage.