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