PowerApps lookup with conditions in With function

[leonardbd]

Hi there,

I have two SP list ,

1. 'Exit Clearance V02'

2. 'ExitCL02-Approvers'

Im trying to Lookup the user email and store it in a Variable (local) that I want to use in my IF Statement. The formula on first line is showing incorrect.

This formula should look for a user email that is common in both SP List and store it variable and then navigate to specific screen if the logged-in user email is matches with the variable

Dont what is the issue on this formula. Looking for you advice. 

With({wSupervisor: LookUp('Exit Clearance V02',Supervisor.Email) And LookUp('ExitCL02-Approvers',Name.Email)});

If(wSupervisor = User().Email, Navigate,Screen1, Navigate,Screen2)

 @WarrenBelz 

[WarrenBelz]

Hi @leonardbd ,

I think you are trying to do this ?

If(
   !IsBlank(
      LookUp(
         'Exit Clearance V02',
         Supervisor.Email = User().Email
      ).ID
   )||
   !IsBlank(
      LookUp(
         'ExitCL02-Approvers',
         Name.Email = USer().Email
      ).ID
   ),
   Navigate,Screen1, 
   Navigate,Screen2
)

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.

Visit my blog Practical Power Apps

[leonardbd]

HI Warren,

Your formula is correct but my goal is ..

The first LookUp should look the Supervisor.Email in VarItemX variable which has defined at the startup and 2nd one will look in the entire List ('ExitCL02-Approvers') but not in a specific record. Something like this..

But I want to set a local variable = the Lookup Email address. 

Then I will use the variable not only within this formula but also in other object's action with IF condition in the same screen such as ..

UpdateContext(VarEmail({
   !IsBlank(
      LookUp(
         VarItemX,
         Supervisor.Email = User().Email
      )
   )||
   !IsBlank(
      LookUp(
         'ExitCL02-Approvers',
         Name.Email = USer().Email
      )
   )});

If(VarEmail = User().Email, then action1, action2) 

Is it the right way?? 😞

And also does ' ||  'symbol stand for 'And' function? 

[WarrenBelz]

Hi @leonardbd ,

A bit clunky, but I think this is what you are doing

UpdateContext(
   {
      VarEmail:
      If(
         Supervisor.Email = User().Email,
         Supervisor.Email,
         LookUp(
            'ExitCL02-Approvers',
            Name.Email = User().Email
         ).Name.Email
      )
   }
);
If(
   VarEmail = User().Email,
   action1, 
   action2
) 

Also || is Or - && is And

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.

Visit my blog Practical Power Apps

[leonardbd]

Hi Warren,

This is showing error in the formula. However I think this formula will stop when it matches the Supervisor email with logged in user email and not going to lookUp in ExitCL02-Approvers' list.

But my goal is if the logged in user Email is in item 'VarItemX' as supervisor.Email and in 'ExitCL02-Approvers' list as Name.Email then VarEmail = that email address. otherwise VarEmail=Blank.

[poweractivate]

@leonardbd 

Change 

Name.Email = USer().Email

 to 

Name.Email = User().Email

 Power Apps is case sensitive.

Hower, @WarrenBelz gave it correctly.

If you could, in general, make sure to try and adjust the formulas given based on the error, I think the formula @WarrenBelz was correct. Even if there are some minor errors, see if you could correct them. 

So try it like this:

UpdateContext(
   {
      VarEmail:
      If(
         Supervisor.Email = User().Email,
         Supervisor.Email,
         LookUp(
            'ExitCL02-Approvers',
            Name.Email = User().Email
         ).Name.Email
      )
   }
);
If(
   VarEmail = User().Email,
   action1, 
   action2
) 

[WarrenBelz · Accepted solution]

Hi @leonardbd ,

I must admit your logic was a bit hard to fathom from your posts, however this should do what you need

UpdateContext(
   {
      VarEmail:
      If(
         Supervisor.Email = User().Email &&
         !IsBlank(
            LookUp(
               'ExitCL02-Approvers',
               Name.Email = User().Email
            ).Name.Email
         ),
         User().Email,
         Blank()
      )
   }
);
If(
   VarEmail = User().Email,
   action1, 
   action2
) 

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.

Visit my blog Practical Power Apps

[leonardbd]

Dear Warren, 

Sorry, I was away and couldn't manage the time to apply your solution. 

That's worked!! WoW... With this I was able to do other conditional action in my app. You are the Boss ! 

Regards

Leo

[leonardbd]

Thanks Poweractive, you were right. 🙂

Thanks