cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AnthonyRegnier
Helper V
Helper V

Filter out days in between startday and endday

Hello guys,

 

I have a tough one for you that my brain is still trying to crack down.

 

Basically I have on one side a source that contains desks reservations:

A desk number

a check in date

a check out date

 

I have another source that contains the list of all desks:

Desk number

Office

Address

Etc.

 

When the app user wants to book a desk, he's selecting from a date picker a start date and an end date and I have then a formula that queries available desks to only return the one that are free.

 

-> Right now, I must enforce a maximum of 2 days booking, to avoid having any days in between the start and the end. That way I can rely on the start date and end date to filter out properly the desks that are already reserved.

 

-> If I remove this 2 days limitation, I have then people booking desks for more than 2 days and I then need to find a ways to "loop" into each days in between in order to properly exclude those already taken.

 

-> I have made a table that returns every days between the start and an end date from the date picker.... BUT! All the current reservations must also have their "table" with days in between...  so at the end I have 2 arrays and each record in array 1, should loop in to each record in array 2.

 

i.e.

1- User1 reserved a desk001 for the 1st of October to the 5th.

2- User2 wants to book a desk for the 2nd of October to the 4th.

3- Among the list of calculated free desks, desk001 is seen as available because I cannot find a way to filter it out since user2 chose dates that are in between what user1 chose.

 

So now he's a bit of code:

 

collection to return free desks according to start and end date:

Then I just have a gallery that connects to the collection to return the result so not very important to mention

ClearCollect(
    ColFreeDesks,
    Filter(
        Desks,
        Office = SelectedLocation,
        Not(
            Title in Filter(
                'Desk Reservations',
                StartsWith(
                    CheckInTxt,
                    selectedStartDate
                ) || StartsWith(
                    CheckOutTxt,
                    selectedEndDate
                ) || StartsWith(
                    CheckOutTxt,
                    selectedStartDate
                ) || StartsWith(
                    CheckInTxt,
                    selectedEndDate
                )
            ).Desk
        )
    )
)

 

I have made a custom table that returns each days in between 2 days, not sure if this is useful

AddColumns(
    FirstN(
        [
            0,
            1,
            2,
            3,
            4,
            5,
            6,
            7,
            8,
            9,
            10,
            11,
            12,
            13,
            14,
            15,
            16,
            17,
            18,
            19,
            20,
            21,
            22,
            23,
            24,
            25,
            26,
            27,
            28,
            29,
            30,
            31
        ],
        DateDiff(
            DateValue(selectedStartDate),
            DateValue(selectedEndDate)
        ) + 1
    ),
    "Day",
    "Day " & (Value + 1),
    "Date",
    DateAdd(
        DateValue(selectedStartDate),
        Value,
        Days
    )
)

 

 

So how do you think I can somehow filter out desks that are already booked when having dates that contains days in between the start and the end? Do I need a ForAll inside a ForAll inside a filter? 

 

Thanks for your answer...

Anthony

1 ACCEPTED SOLUTION

Accepted Solutions
AnthonyRegnier
Helper V
Helper V

ClearCollect(ColAvailDesks, Filter(Desks, Office = SelectedLocation, Not(Title in Filter('Desk Reservations', Or(startTime >= 'Check In' && startTime <= 'Check Out', endTime >= 'Check In' && endTime <= 'Check Out', startTime < 'Check In' && endTime > 'Check Out')).Desk)))

 

 

 

That's the one!!

 

Thanks

View solution in original post

3 REPLIES 3
AnthonyRegnier
Helper V
Helper V

Hello everyone, 

 

Anyone has an idea on how we can achieve this? 

Didn't know it would be such a tough one to a point that nobody would answer :p. 

 

Thank you in advance 

Anthony 

rimatos
Community Support
Community Support

Hi @AnthonyRegnier ,

 

Since you have a data source with the desk reservations that contains:

 

  • Desk number
  • Check in date
  • Check out

I would say in the filter the easiest way would be to first detect if the desk that was selected is contained within this data source, and then you can make comparisons with the check in date selected.

e.g.

 

LookUp('YourDataSource', 'Reserved Desk'  = SelectedDesk , 'Check out Date') >= CheckInSelected && LookUp('YourDataSource', 'Reserved Desk'  = SelectedDesk , 'Check in Date') <= CheckInSelected

 

I believe something like this could potentially work (although not optimized at all). You verify if the check in selected date is in between both the already booked check in and check out dates.

Problem here is the fact that there will be multiple reservations for the Desk, so a ForAll expression would probably need to be inserted somewhere there.

Some good examples on date comparison can be found here:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Compare-date/td-p/2440

&
https://powerusers.microsoft.com/t5/Building-Power-Apps/Compare-date-in-PowerApps/td-p/254067

 

Hope this helps!

 

Regards,

Ricardo

AnthonyRegnier
Helper V
Helper V

ClearCollect(ColAvailDesks, Filter(Desks, Office = SelectedLocation, Not(Title in Filter('Desk Reservations', Or(startTime >= 'Check In' && startTime <= 'Check Out', endTime >= 'Check In' && endTime <= 'Check Out', startTime < 'Check In' && endTime > 'Check Out')).Desk)))

 

 

 

That's the one!!

 

Thanks

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,179)