Check if User is in Sharepoint List column for current item

[un3xpected]

I have a simple form where I want to make certain datacards only be editable if the current user is the 'Teamleader'. There is a Teamleader column on my SP-List.

I searched the forum and came to the conclusion that this formula should work (DisplayMode Setting on the Datacard):

If(!IsBlank(LookUp(Teamrequests,Teamleader.Email = User().Email)),DisplayMode.Edit,DisplayMode.View)

However, for the Teamleader I put in a Person which is not me (People Column) and it still let me edit it.

When I tried comparing :

Teamleader.Displayname = User().Fullname

it worked better since it resulted in a failure and let me only view.

(It should generate an "false" anyway since Displayname is like: Surname, First name whereas User().Fullname is First name, Surname )

But I think you get the idea. In my opinion something is wrong with the E-Mail comparison.

Could anyone please help here?

[poweractivate]

@un3xpected 

Check if this below helps as a guide:

Redirect users to different pages based on their O365 group membership 

[StretchFredrik]

Hello,

you need to compare the current item, which you arent doing when u do "lookup()" Ur just seeing if the email is present in ANY of the rows in the list. 

[poweractivate]

---

@un3xpected wrote:

However, for the Teamleader I put in a Person which is not me (People Column) and it still let me edit it.

---

Yes but if you are still a Team Leader and your corresponding record still exists in SharePoint you will still be allowed to edit it - regardless what other records may be there too - right?

[poweractivate]

@un3xpected Yes it is as @StretchFredrik  says.

If there is ANY one Record who has a team leader's Email address matching the User().Email the check will pass anyway, no matter what.

You should try and do the check another way, such as like this

//pseudo-formula adjust as needed
//change MyUniqueEmailColumn below to something that makes sense for you
//also adjust the below as needed
With({_rec:LookUp(Teamrequests,MyUniqueEmailColumn = User().Email)},If(!IsBlank(_rec) && _rec.Teamleader.Email = User().Email ,DisplayMode.Edit,DisplayMode.View))

However, if the above works, it only works if your email on your unique Record also has your Email for Team Leader. If this is what you want, something like the above might work.

[un3xpected]

Thanks for the quick response. Understood. But how should I edit my formula to make sure it compares only this record?

[poweractivate]

@un3xpected 

You can check my response where I gave an example formula. The formula may need to be adjusted.

[poweractivate]

@un3xpected 

If you don't have anything along the lines of 

MyUniqueEmailColumn 

 Then you must have some way of knowing who is the current User, get a Record (whether it's this List or another one) that has a Teamleader in it, then get that Teamleader and find their e-mail.

In my formula I am assuming a simple case where a user has some Record in that same list, and looking it up will get the one Record, and Teamleader will also be in there.

If that's not the case, you'll have to use some slightly more complex formulas. For that I would like to know how your data is structured in case. For now I'll give a possible assumed alternative solution in case you have the data in more than one List.

//adjust as needed
With
(
    {_rec:LookUp(SomeOtherList,MyUniqueEmailColumn = User().Email)}
   ,If
    (
       !IsBlank(_rec)
      ,With
      (
         {_rec2:LookUp(SomeTeamLeadersList,SomeEmail = _rec.Teamleader.Email}
         ,If
         (
            !IsBlank(_rec2)
            ,DisplayMode.Edit
            ,DisplayMode.View
          )
      )
      ,DisplayMode.View
    )
)

[un3xpected]

Thanks for your quick and good response. I'd like to try out your formular. However, I'm not sure what _rec is. Is it a variable in this formular? 

And don't get me wrong with Teamleader I do not mean the set teamleader in the office365-environment. It's a custom column which I created where you can select anyone. Maybe I should have pointed that out more clearly.

[poweractivate]

@un3xpected I am using With function to reduce repetition. You can just not use it if you want not to use it, but if so, you have to repeat the same formula each time it is used, which may make it more difficult to change later (you have to change each time it is repeated if you need to change it later, rather than just once inside the With function).

You can do this to not use With at all:

//pseudo-formula adjust as needed
//change MyUniqueEmailColumn below to something that makes sense for you
//also adjust the below as needed
If(!IsBlank(LookUp(Teamrequests,MyUniqueEmailColumn = User().Email)) && LookUp(Teamrequests,MyUniqueEmailColumn = User().Email).Teamleader.Email = User().Email ,DisplayMode.Edit,DisplayMode.View)

or for the complex example, to not use With at all:

//adjust as needed

If
(
   !IsBlank(LookUp(SomeOtherList,MyUniqueEmailColumn = User().Email))
   ,If
   (
      !IsBlank(LookUp(SomeTeamLeadersList, SomeEmail = LookUp(SomeOtherList,MyUniqueEmailColumn = User().Email).Teamleader.Email)
      ,DisplayMode.Edit
      ,DisplayMode.View
    )
   ,DisplayMode.View
)

I just used With because some formulas are repeated again, and the repetition can be reduced to some extent using With is all I was doing.