Compare date/time in SharePoint list to one selected in PowerApps

[Lewkir]

I'm trying to build a room booking app in powerapps (the template wont work for us as our Outlook is on a local server or something) and I'm struggling to work out how to compare a date and time picked in power apps to what already exists in SharePoint.

What I'm working with:

I have two Lists in SharePoint, one is a list of possible rooms and their info eg. location/facilities etc. 

the second list is the booking list which is a list rooms wich have been booked with the specific date/time of each booking.

Currently I have a gallery and a couple of dropdowns which allow me to filter by location and facilities.

I'd like to be able to also choose a particular date range which would then filter out any rooms which have ben booked for this range.

I'm fairly new to both PowerApps and programming in general but I have an idea of what I need to do but not how to implement it.

Can anyone help me out?

Hopefully there's enough info here.

Cheers,

[v-xida-msft]

Hi @Lewkir,

Do you add a date/time type column in your second SP list to store the booking date time?

If you want to filter your Booking list records based on a range of date, I think two Date Picker controls could achieve your needs.

I have made a test on my side, please take a try with the following workaround:

Set the Items property of the Gallery control to following formula:

Filter(
    '20181122_case14',
     ProductionDate >= DatePicker1.SelectedDate && ProductionDate <= DatePicker2.SelectedDate  /* <-- ProductionDate is a Date Time type column in my SP list */
)

On your side, you should type:

Filter(
    'YourSPList',
     DateColumn >= DatePicker1.SelectedDate && DateColumn <= DatePicker2.SelectedDate  /* <-- DateColumn is a Date Time type column in your SP list. DatePicker1 represents the Start Time, DatePicker2 represents the End Time */
)

Above solution may cause a Delegation issue, in order to get rid of this issue, you could consider take a try with the following workaround:

Set the OnVisible property of the first screen of your app to following:

ClearCollect(RecordsCollection, 'YourSPList')

Set the Items property of the Gallery control to following formula:

Filter(
     RecordsCollection,
     DateColumn >= DatePicker1.SelectedDate && DateColumn <= DatePicker2.SelectedDate  /* <-- DateColumn is a Date Time type column in your SP list */
)

Note: Due to Time Zone issue, the Date Time value you pulled from your SP list within your app may not be same with that within your SP list. You could get the proper Date Time value using DateAdd, DateDiff and TimeZoneOffset function. In addition, when working Date Time within PowerApps, the Date Time value would be converted into UTC format automatically.

Best regards,

Kris

[Lewkir]

Hi Kris,

Thanks for the response, unfortunately the problem is a little more complex than that (possibly I failed to explain it properly.

I want to diplay a gallery of the room list but filter out those which have been booked (showing in the booking list).

I've done some more playing today and have come up with a few formulae which separately do some of what I need:

I can filter the rooms which show up in the booking list :

Filter(RoomList, LookUp('Booking List', 'Room Number'.Value <> RoomList[@Title], 'Room Number').Value = Title)

and filter out the bookings which occur in the time frame of the date/time pickers: 

Filter(

    'Booking List',

      From > FromDate.SelectedDate +

            Time(

                Value(FromHour.Selected.Value),

                Value(FromMin.Selected.Value),

                0) &&

      To > toDate.SelectedDate +

            Time(

                Value(ToHour.Selected.Value),

                Value(ToMin.Selected.Value),

                0)

                Or

      From < FromDate.SelectedDate +

            Time(

                Value(FromHour.Selected.Value),

                Value(FromMin.Selected.Value),

                0) &&

      To < toDate.SelectedDate +

            Time(

                Value(ToHour.Selected.Value),

                Value(ToMin.Selected.Value),

                0)

        )  

I also have a couple of drop downs to filter room locations and types:

Filter(RoomList, locationDrop.Selected.Value = Location, RoomTypeDrop.Selected.Value = 'Room Group')

So now I need to find a way to fit them together.

Will continue to fiddle with it but any advice is appreciated.

regards,

[Lewkir · Accepted solution]

Update:

Managed to get it to work using:

Filter(RoomList, locationDrop.Selected.Value = Location, RoomTypeDrop.Selected.Value = 'Room Group', LookUp(Filter(
'Booking List',
From <= FromDate.SelectedDate +
Time(
Value(FromHour.Selected.Value),
Value(FromMin.Selected.Value),
0) &&
To >= ToDate.SelectedDate +
Time(
Value(ToHour.Selected.Value),
Value(ToMin.Selected.Value),
0)
)
, 'Room Number'.Value = RoomList[@Title], 'Room Number').Value <> Title)

Cheers for your help anyway.

Edit: spoke to soon no I didn't.

It's close though I'm sure of it.

Edit of above edit:

Seem to have sussed it:

Filter(RoomList,locationDrop.Selected.Value = Location, RoomTypeDrop.Selected.Value = 'Room Group', LookUp(Filter(
    'Booking List',
      From >= FromDate.SelectedDate +
            Time(
                Value(FromHour.Selected.Value),
                Value(FromMin.Selected.Value),
                0) &&
      From < ToDate.SelectedDate +
            Time(
                Value(ToHour.Selected.Value),
                Value(ToMin.Selected.Value),
                0) Or
      To > FromDate.SelectedDate +
            Time(
                Value(FromHour.Selected.Value),
                Value(FromMin.Selected.Value),
                0) &&
      To <= ToDate.SelectedDate +
            Time(
                Value(ToHour.Selected.Value),
                Value(ToMin.Selected.Value),
                0)

        )
, 'Room Number'.Value = RoomList[@Title], 'Room Number').Value <> Title)