cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lewkir
Resolver I
Resolver I

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

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,

1 ACCEPTED SOLUTION

Accepted Solutions

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)

 

View solution in original post

3 REPLIES 3
v-xida-msft
Community Support
Community Support

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:1.JPG

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

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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,

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)

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,128)