Lookup code to lookup list not returning correct value.

(0) Share

Report

Posted on by chtraywick

184

Hi:

Have an app that executes a lookup when a new detail record is created to see if there is a conflict with date range and a room that is selected. The issue is the room and date range already saved. Example: A reservation exists for a given room from 7 to 9 AM on a given date. A new request is entered that is 8 to 10 for the same room and date. So the 8 does not conflict with the previous start time (is greater than) reservation for this room but it does overlap the ending date range. Is there a between type function to compare to?

Thank you

Chuck

Categories:

Building Power Apps

All responses (8)

Answers (2)

Sort by

v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @chtraywick ,

Could you please share more details about your scenario? How's your data source saving the meeting times, in one or two columns?

Best regards,
chtraywick 184 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi:
Thank you for reaching out.
Attached is the code fragment. A schedule for a given date exists, 12/8/2023 starting at 8 AM to 9 AM for a room. A new request comes in for 7AM to 9AM on 12/8/2023 for the same room. The start date is the same then the start time is less than the record in the list but the end time means that the new request would overlap the stored request. The date is date time, the start and end times are choice columns as text. Here is a short sample, the room is NOT in this routine yet, just trying to figure out the overlap issue on dates to find a way to test if a given request falls between an existing record.
Set(varStartTime,
LookUp(MasterCalendarV4, dtestarttime.SelectedDate = StartTime
&&
If(StartTimeofDay.Value = "PM" , Value(StartTimeHour.Value) + 12, Value(StartTimeHour.Value))
<= If(drpstarttimeofday.Selected.Value = "PM", Value(drpstarthour.Selected.Value) + 12, Value(drpstarthour.Selected.Value))
&&
(If(EndTimeofDay.Value = "PM" , Value(EndTimeHour.Value) + 12, Value(EndTimeHour.Value))
>= If(dteendtimeofday.Selected.Value = "PM", Value(dteendhour.Selected.Value) + 12, Value(dteendhour.Selected.Value))
||
If(EndTimeofDay.Value = "PM" , Value(EndTimeHour.Value) + 12, Value(EndTimeHour.Value))
>= If(dteendtimeofday.Selected.Value = "PM", Value(dteendhour.Selected.Value) + 12, Value(dteendhour.Selected.Value)))
).StartTime);

Thank you
Chuck
chtraywick 184 on at

Like
a
(0)

Report
Copy link

Link copied!

Also have tried the last IF with >= with <= does not set the value since it overlaps but it still returns blank
chtraywick 184 on at

Like
a
(0)

Report
Copy link

Link copied!

Just found this article and am testing it:

Power Apps Guide - Dates - How to detect overlapping times / scheduling conflicts - Power Apps Guide - Blog
Verified answer

v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @chtraywick ,

To put it simply, when you want to check if two time durations are overlapped, the common condition to use in PowerApps is:

a selected start time is later than the saved end time OR a selected end time is earlier than the saved start time. Selected end time needs to be later than selected start time with no doubt as well.

What's the MasterCalendarV4? It looks like an Office 365 Outlook action but I've never seen it before.

Anyway, you can try below formula I modified on yours:

Set( varStartTime, LookUp( MasterCalendarV4, dtestarttime.SelectedDate = StartTime && ( If(dteendtimeofday.Selected.Value = "PM", Value(dteendhour.Selected.Value) + 12, Value(dteendhour.Selected.Value)) < If(StartTimeofDay.Value = "PM" , Value(StartTimeHour.Value) + 12, Value(StartTimeHour.Value)) || If(EndTimeofDay.Value = "PM" , Value(EndTimeHour.Value) + 12, Value(EndTimeHour.Value)) <= If(drpstarttimeofday.Selected.Value = "PM", Value(drpstarthour.Selected.Value) + 12, Value(drpstarthour.Selected.Value)) ) ).StartTime );

Best regards,
chtraywick 184 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you, will test that. MasterCalendarV4 is a data source to SP list.
chtraywick 184 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you, that worked. For some reason the logic on dates is not always clear to me.
Verified answer

chtraywick 184 on at

Like
a
(0)

Report
Copy link

Link copied!

This is what we ended up with

Set(varStartTime,
LookUp(MasterCalendarV4, dtestarttime.SelectedDate = StartTime
&&
(If(StartTimeofDay.Value = "PM" , Value(StartTimeHour.Value) + 12, Value(StartTimeHour.Value))
<= If(drpstarttimeofday.Selected.Value = "PM", Value(drpstarthour.Selected.Value) + 12, Value(drpstarthour.Selected.Value))
&&

If(EndTimeofDay.Value = "PM" , Value(EndTimeHour.Value) + 12, Value(EndTimeHour.Value))
>= If(dteendtimeofday.Selected.Value = "PM", Value(dteendhour.Selected.Value) + 12, Value(dteendhour.Selected.Value)))

).StartTime);

Set(varOverlap,
LookUp(MasterCalendarV4, dtestarttime.SelectedDate = StartTime
// &&
// (If(StartTimeofDay.Value = "PM" , Value(StartTimeHour.Value) + 12, Value(StartTimeHour.Value))
// <= If(drpstarttimeofday.Selected.Value = "PM", Value(drpstarthour.Selected.Value) + 12, Value(drpstarthour.Selected.Value))
&&

If(dteendtimeofday.Selected.Value = "PM", Value(dteendhour.Selected.Value) + 12, Value(dteendhour.Selected.Value))
> If(StartTimeofDay.Value = "PM" , Value(StartTimeHour.Value) + 12, Value(StartTimeHour.Value))

).StartTime);
If(!IsBlank(varStartTime) || !IsBlank(varOverlap), Set(varError, false));
If(!IsBlank(varStartTime) || !IsBlank(varOverlap) ,
Notify("Schedule conflict", NotificationType.Error, 3000),
Notify("Time and room available", NotificationType.Success, 3000));