Problems with the filter between date and time

[Rene83]

Hi. I have a big problem with filtering between date and time.
I have adapted the app from https://github.com/aprildunnam/PowerApps/tree/master/DeskReservation as a basis.

As an example, "Table 1" is reserved from 08:00-12:00.

If I select 08:15-13:00, "Table 1" is displayed as available again.

But if I select 07:00-13:00, no table is available.

Sharepoint

The filter in the gallery is

Filter(Desks; Not(Title in Filter('Desk Reservations';
CheckOutFrom >= startTime && CheckOutTo <= endTime || CheckOutFrom <= endTime && CheckOutTo >= endTime).DeskText
) && Active = 1)

Please does anyone have any ideas.
Thank you

Kind regards
Rene

[WarrenBelz]

Hi @Rene83 ,

Firstly you need some bracketing around your and/or and you also have a potential Delegation issue with the relational Filter - try this

With(
   {
      wList:
      Filter(
         Desks; 
         Active = 1
      )
   };
   Filter(
      wList;
      !(
         Title in Filter(
            'Desk Reservations';
            (CheckOutFrom >= startTime && CheckOutTo <= endTime) || 
            (CheckOutFrom <= endTime && CheckOutTo >= 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

[Rene83]

Hi @WarrenBelz,

Thank you for your answer. Because of the delegation problem, I have a flow that deletes expired reservations every 24 hours.

Unfortunately, the table is still suggested to me.

[WarrenBelz]

Hi @Rene83 ,

Looking at it again - it you want to exclude anything where either the CheckOutFrom or CheckOutTo are between the startTime and endTime

With(
   {
      wList:
      Filter(
         Desks; 
         Active = 1
      )
   };
   Filter(
      wList;
      !(
         Title in Filter(
            'Desk Reservations';
            (CheckOutFrom <= endTime && CheckOutFrom >= startTime) ||
            (CheckOutTo <= endTime && CheckOutTo >= startTime)
         ).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

[Rene83]

Hi @WarrenBelz ,

Here is an example to describe it better.

A table (table 1) is already reserved by a person from 14.04.2022 08:00 (CheckOutFrom) to 14.04.2022 09:00 (CheckOutTo).

However, the table (Table 1) should be available again before 08:00 or after 09:00.

Unfortunately, the problem persists

[WarrenBelz]

@Rene83 ,

Please have another go with the revised code.

[Rene83]

Hi @WarrenBelz ,

i have tried it but unfortunately it does not work.

I have uploaded the app (*.msapp) with the screenshots from SharePoint.
Maybe you can recreate it and discover an error.

Thanks again

 

 

[WarrenBelz]

@Rene83 ,

The app will not work without the data (regarding your msapp file), however are you saying that a table is showing as available when it is reserved, or that it is not showing as available when it should be ?
If the first, this code

!(
   Title in Filter(
      'Desk Reservations';
      (CheckOutFrom < endTime && CheckOutFrom > startTime) ||
      (CheckOutTo < endTime && CheckOutTo > startTime)
   ).DeskText
)

looks for items where both the CheckOutForm and CheckOut are not between the startTime and endTime (I have changed slightly to allow for the times themselves - and simply should work).
If the second issue (should be available but is not), Title in 'Desk Reservations' needs to match DeskText in Desks and Active in Desks needs to equal 1 (if this is a true/false field, simply use Active)

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

[sidiggi]

Sadly I am running into the same issue, and the proposed fix does not solve it.

With(
    {
        wList:
        Filter(
            Desks;
            Active = 1
        )
    };
    Filter( 
        wList;
        !(
            Title in Filter( 
            'Desk Reservations'; 
            'Check Out From' >= startTime && 'Check Out To' >= endTime
//            ('Check Out From' < endTime && 'Check Out From' > startTime) || 
//            ('Check Out To' < endTime && 'Check Out To' > startTime)
         ).DeskText
      )
   )
)
//Filter(Desks;Not(Title in Filter('Desk Reservations';'Check Out From'>=startTime&&'Check Out To'<=endTime||'Check Out From'<=endTime&&'Check Out To'>=endTime).DeskText)&&Active=1)

Logic should be as shown in image.

[WarrenBelz]

@sidiggi ,

I cannot comment on the rest of the code as I am not familiar with that template (although it seems to be problematic from the posts I have seen), however this bit of code

('Check Out From' < endTime && 'Check Out From' > startTime) || 
('Check Out To' < endTime && 'Check Out To' > startTime)

which you have commented out filters out items where either the 'Check out from' or 'Check Out To' are between the startTime and endTime (not including the times themselves). You will need to check that Title in Desks also matches DeskText in 'Desk Reservations' and Active is 1.