cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jo_25
Helper I
Helper I

troubleshoot if and lookup formula with multiple conditions

I want my leave app  to display warning msg to my users when the following conditions are met:

(a) Name field of a leave request in sharepoint list matches the powerapps login id of user (varName)
(b) Selected start and end date of leave in powerapps of the user overlapps with a existing leave request in sharepoint list.
(c) Status of leave request in sharepoint list = "Approved" or "Pending Approval".

Below is the formula. The warning msg is displayed if the date and name conditions as well as status of request in Sharepoint list is ="Approved". However,
the warning msg is not displayed for requests that have date and conditions met but the status ="Pending Approval". Please see formula below.
I have tried listing the status condition for Pending Approval and Approved separately in the formula, i.e. Status ="Approved" || Status ="Pending Approval" but this does not help at all. I also tried moving the status condition before the date condition but this is also useless.

Would appreciate if you could help troubleshoot the formula.

Thanks!

Formula:

 

If(!IsBlank(LookUp(LeaveRequest, Name=varName && Value(Text(StartDate,"[$-en-US]yyyymmdd")) <= Value(Text(LeaveStartDatePicker.SelectedDate, "[$-en-US]yyyymmdd")) &&
Value(Text(EndDate, "[$-en-US]yyyymmdd")) >=Value(Text(LeaveEndDatePicker.SelectedDate, "[$-en-US]yyyymmdd"))&& Status ="Approved" || "Pending Approval" )) ||

!IsBlank(LookUp(LeaveRequest, Name=varName && Value(Text(StartDate, "[$-en-US]yyyymmdd")) <= Value(Text(LeaveEndDatePicker.SelectedDate, "[$-en-US]yyyymmdd")) &&
Value(Text(StartDate, "[$-en-US]yyyymmdd")) <=Value(Text(LeaveStartDatePicker.SelectedDate, "[$-en-US]yyyymmdd"))&&Status ="Approved" || "Pending Approval")) ||

!IsBlank(LookUp(LeaveRequest, Name=varName && Value(Text(EndDate, "[$-en-US]yyyymmdd")) >= Value(Text(LeaveEndDatePicker.SelectedDate, "[$-en-US]yyyymmdd")) &&
Value(Text(EndDate, "[$-en-US]yyyymmdd")) >=Value(Text(LeaveEndDatePicker.SelectedDate, "[$-en-US]yyyymmdd"))&&Status ="Approved" || "Pending Approval")) ||

!IsBlank(LookUp(LeaveRequest, Name=varName && Value(Text(EndDate, "[$-en-US]yyyymmdd")) <= Value(Text(LeaveEndDatePicker.SelectedDate, "[$-en-US]yyyymmdd")) && Value(Text(EndDate, "[$-en-US]yyyymmdd")) <=Value(Text(LeaveEndDatePicker.SelectedDate, "[$-en-US]yyyymmdd"))&&Status ="Approved" || "Pending Approval")),
"Selected leave dates overlapped with existing leave request record")

1 ACCEPTED SOLUTION

Accepted Solutions
Alex_10
Super User
Super User

@Jo_25 

what is the datatype of Status column in SharePoint?

 

OR-|| operator logic needs to be encapsulated to work with multiple AND-&&.


// if single choice column
(Status.Value ="Approved" || Status.Value = "Pending Approval")

 

// if multichoice column
("Approved" in Status.Value || "Pending Approval" in Status.Value)

 

// if single line of text
(Status ="Approved" || Status = "Pending Approval")

View solution in original post

4 REPLIES 4
Alex_10
Super User
Super User

@Jo_25 

what is the datatype of Status column in SharePoint?

 

OR-|| operator logic needs to be encapsulated to work with multiple AND-&&.


// if single choice column
(Status.Value ="Approved" || Status.Value = "Pending Approval")

 

// if multichoice column
("Approved" in Status.Value || "Pending Approval" in Status.Value)

 

// if single line of text
(Status ="Approved" || Status = "Pending Approval")

View solution in original post

Many thanks Alex! It works beautifully! Didnt know it is a missing pair of parenthesis. As I am new to powerapps, just wondering when parenthesis is needed and why it is needed in the case.  

 

Once again thank you!!

Jo_25

Many thanks, Alex for your suggestion. The formula works beautifully when I tried it. As I am new in powerapps & still learning, just wondering under what scenario to use the parenthesis and in this scenario why is it needed. 

 

Best regards

 

Jo_25

Alex_10
Super User
Super User

@Jo_25 

 

you have 3 conditions: so you can encapsulate each condition with (). Doing that prevents any condition affect on other.

 

for example:
If(

 

// condition 1 
(
!IsBlank(LookUp(LeaveRequest, Name=varName && Value(Text(StartDate,"[$-en-US]yyyymmdd")) <= Value(Text(LeaveStartDatePicker.SelectedDate, "[$-en-US]yyyymmdd"))

)

 

&&

 

// condition 2

(
Value(Text(EndDate, "[$-en-US]yyyymmdd")) >=Value(Text(LeaveEndDatePicker.SelectedDate, "[$-en-US]yyyymmdd"))
)


&&

 

// condition 3

(

Status ="Approved" || Status ="Pending Approval"
)

 

))........

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,303)