Answered

Date and Time in-between

(1) Share

Report

Posted on by raassd

Hi Team,

I am trying to filter data from my table from based on selected start date time and end date time I am using below query which is not working as required please help me on this .

Filter(
Not(
Seat_x0020_Number in Filter(
'Desk Reservations',
DateTimeValue(CheckOutFromText) >= startTime && DateTimeValue(CheckOutToText) <= endTime || DateTimeValue(CheckOutFromText) <= endTime && DateTimeValue(CheckOutToText) >= endTime
).DeskText)

)

Categories:

Building Power Apps

All responses (12)

Answers (1)

Sort by

WarrenBelz 156,545 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @raassd ,

You are missing the list name and also had end time in there three times. As well, some bracketing is needed between And/Or

Filter( YourListNameHere, !( Seat_x0020_Number in Filter( 'Desk Reservations', ( DateTimeValue(CheckOutFromText) >= startTime && DateTimeValue(CheckOutToText) <= endTime ) || ( DateTimeValue(CheckOutFromText) <= startTime && DateTimeValue(CheckOutToText) >= endTime ) ).DeskText ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps
raassd 56 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz ,
Thanks for your reply !
I have used the above code but still I am not getting my required output.
User 1 had booked a set from 10/06/2022 12.00 PM to 10/06/2022 6.00 PM Desk1
User 2 is trying to book from 10/06/2022 1.00 PM to 10/06/2022 7.00 PM as per the logic desk 1 should not be visible but here in the above code desk 1 is visible.

Thanks in Advance !
WarrenBelz 156,545 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@raassd ,

I was simply fixing the syntax of what you posted. The logic is looking for records in 'Desk Reservations' where the 'Seat Number' in your main list does not appear in 'Desk Reservations' in the DeskText field when either the checkout to or checkout from are between the start and end time of the record (which appears to be what you need)

You are also using DateTimeValue on a Text field, so maybe you need to do some debugging on the outputs of your controls.
raassd 56 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz ,
Thanks for your reply !

Please help me on this, I have spent more time but no use.

Below is my patch code.

Thanks in Advance !
WarrenBelz 156,545 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@raassd ,

That is a different topic to your post, but what exactly is wrong with it and why are you using TimeZoneOffset? Also can you please post your code in Text.
raassd 56 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz

Here is my code

ForAll(
SelectedDesk,
Patch(
'Desk Reservations',
Defaults('Desk Reservations'),
{
Title: currentUser.FullName & " " & ThisRecord.Title,
DeskText: ThisRecord.Seat_x0020_Number,
CheckOutFrom: DateAdd(
startTime,
TimeZoneOffset(),
Minutes
),
CheckOutFromText: startTime,
CheckOutFromNumber: Value(
Text(
startTime,
"yyyymmddhhmm"
)
),
CheckOutTo: DateAdd(
endTime,
TimeZoneOffset(),
Minutes
),
CheckOutToText: endTime,
CheckOutToNumber: Value(
Text(
endTime,
"yyyymmddhhmm"
)
),
'Reserved By': currentUser.FullName
}
)
);
WarrenBelz 156,545 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@raassd ,

Yes, but what is wrong with it ?
raassd 56 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz ,
I am trying to filter by desks numbers if the user has already booked desk that desk number should be visible in the selected time frame for the next user so please help me on fixing the filter code.
Filter(
Desks,GOL = GOLData,Not(
Seat_x0020_Number in Filter(
'Desk Reservations',
DateTimeValue(CheckOutFromText) >= startTime && DateTimeValue(CheckOutToText) <= endTime || DateTimeValue(CheckOutFromText) <= endTime && DateTimeValue(CheckOutToText) >= endTime
).DeskText)

)

I am using above code .
WarrenBelz 156,545 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@raassd ,

I meant your Patch code - I have already responded to the filter - you need to check your data outputs first as the code is valid.
raassd 56 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz ,

Filter is not working as expected, as you said check my output data so I showed you my pact code.
If anything needs to changed in my patch code please suggest me .

Thanks !