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")

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
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

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