web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / troubleshoot if and lo...
Power Apps
Answered

troubleshoot if and lookup formula with multiple conditions

(0) ShareShare
ReportReport
Posted on by 43

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

Categories:
  • Verified answer
    Al_10 Profile Picture
    1,691 Super User 2024 Season 1 on at

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

  • Jo_25 Profile Picture
    43 on at

    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

  • Jo_25 Profile Picture
    43 on at

    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

  • Al_10 Profile Picture
    1,691 Super User 2024 Season 1 on at

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

     

    ))........

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Leaderboard > Power Apps

#1
11manish Profile Picture

11manish 220 Super User 2026 Season 2

#2
WarrenBelz Profile Picture

WarrenBelz 189 Most Valuable Professional

#3
Mohsin Ali Profile Picture

Mohsin Ali 139

Last 30 days Overall leaderboard