I'm trying to compare a date in an item in a gallery against a date in an item in a SharePoint list. The items refer to the time a room is booked and I want to display whether or not a room is booked between two times.
Currently I'm using a lookup and this works for a single room, my code looks like:
If( ThisItem.NewDate <= LookUp( 'Booking List', ThisItem.RoomNumber = 'Room Number', To ) && ThisItem.NewDate >= LookUp( 'Booking List', ThisItem.RoomNumber = 'Room Number', From ), true, false )
I know the problem is that the look up just finds the first Room Number that matches and uses that as the only compare.
I have fairly limited coding experience so I am struggling to think of a way to look at every booking for certain room. Possibly using Filter?
Any help is much appreciated.
Solved! Go to Solution.
Just thinking out loud, here... but I'm starting to think you need to arrive at a different construction of your data. Whether that is by a change to the schema of how you lay it out or by ad hoc construction of your table (like a ForAll() and/or a Collect() and/or Patch()). If it were me, I would want my final presentation of data to be something like:
RoomID Date Slot1 Slot2 Slot3 Slot4 Slot5... =================================================================================================== Conf1 1/15/2019 BookingID4 BookingID4 Conf2 1/16/2019 BookingID1 BookingID1 BookingID3
Here, the Slots (1, 2, 3, etc.) represents the 15 minute increment of a given day. The combination of RoomID and Date is unique, so that the data is aggregated for each room on each day (each room can only have one booking engagement during any given 15 minute increment). You can see that in how Conf2 has 2 different Booking engagements (1 & 3).
Laying the data out this way, I could filter that dataset based on my date, which would return an effectively "Distinct" choice of rooms. Then each entry in the Gallery could have a button that represented the 15 minute increment of each field. The Visible property would just have to check the individual field it was assigned to... so the button for Slot1 would read:
Doing things this way also means there is no sub-Gallery, and no LookUps() in the presentation of the data. You just have to get your data in that kind of layout to begin with. So... how to go about that? You're right to have your Room list separate from your booking list. Again, if it were me, I would store the "booking" in a human-friendly format (start time & end time) for each room/day combination (RoomID, Date, StartTime, EndTime, EmployeeID, etc.). However that Booking would also have an ID. When the Booking is saved to the Booking table, I would immediately go to my table that I described, above, with the 15 minute breakouts during the day. Let's call it BookingBreakout. I would delete every appearance of the BookingID I just created/edited, and add them back in appropriately (using good record management if I have created a blank row) to match the new version of the Booking.
No sub-Gallery and no LookUp()s at presentation means there are far fewer calculations PowerApps has to perform.
You *can* get there through Collect(), but I would suggest making your data model in the form that makes the most sense for what you're going to ask of it from the beginning.
Filter will get you all* the records that match your given criteria. What you do with that recordset is up to you.
(* -- as long as you don't step on delegation)
So if you feed your date checks into the criteria portion of the Filter() statement, you will get the return of dates where the room is booked (but there are considerations and concerns):
Filter('Booking List', 'Room Number' = ThisItem.RoomNumber && To >= ThisItem.BookingDate && From <= ThisItem.BookingDate)
That version of the statement is going to only show returns where the booked dates fall completely within the To/From date range. That may not be what you're after.
Filter('Booking List', 'Room Number' = ThisItem.RoomNumber && (To >= ThisItem.BookingDate || From <= ThisItem.BookingDate))
That version is going to return records where the booking date overlaps either end of the date range. That might be closer to what you're looking for.
Can you share more details about your setup? You mentioned a Gallery... would that be a Gallery of Rooms? And how do you see the users interacting with the app (and the date pickers)?
Going further afield...
Let's back up a minute... If your app is designed to show room availability, there might be ways to do that with boolean evaluations for your basic unit of time. For instance, let's say your basic unit of time was a "day." Any room could be booked (true) or not booked (false) in that unit of time. If you have a control that represents a particular day, you could put an If() statement in the Fill property to give a visual indication... if booked, fill it in one way. If not, fill it a different way.
Another option would be a "booked" indicator for that date... like a badge icon or a checkmark. In that case, put your test in the Visible property. Because the property is already a boolean, you wouldn't need an If() statement, just a formula that evaluates to true or false. For instance, CountRows(...testing for booking records on this date...)>0 would evaluate to true if there was a booking record for that date, making the control visible.
In either case, you need the ability to control how many days you are showing, and what day each control represents (as they would change based on the date you selected). For that, look at this blog post for a neat way to build a calendar control.
Could you please share a bit more about the Excel table that you mentioned?
I agree with @TimRohr's thought almost. You could consider take a try to add a column within your Excel table to store the 15 mins Increment value.
If the Room has been booked, set a value within the corresponding 15-mins Increment column. Then within your Gallery2, you could visible/invisible the buttons via checking if the corresponding 15-mins Increment column is empty.
Thanks for the feedback.
So the excel spreadsheet is a single column int the format:
Column1 <was autonamed by excel I should have given it something more relevent
I am using it to form the structure of 24hr display of room bookings.
I've had to add a second column to this called "NewDate" which basically takes the date selected by the user and adds it to each value in order to get a a list of times for that date. (as on importing the dates were auto set to 31/01/1899)
Just tried adding a new column to the table with a true or false value depending on whether it's booked or not (Tim you are right the button should be visible if it's not booked) and I'm running into the same LookUp issue.
AddColumns(Table1_1,"RoomNumber",Title,"NewDate",If( DateAdd(Column1, (DateDiff(Column1, GanttDate.SelectedDate+Time(0,0,0),Days)), Days) <= LookUp( 'Booking List', "M101" = 'Room Number', To ) && DateAdd(Column1, (DateDiff(Column1, GanttDate.SelectedDate+Time(0,0,0),Days)), Days) >= LookUp( 'Booking List', "M101" = 'Room Number', From ), true, false ) )
Currently I'm just trying for one room (M101) otherwise I'd reference gallery 1. However, I'm worried this won't work as I think the sheer number of calculations is causing powerapps to crash and I get the message "An error occured on the server.
Although this doesn't happen if I add two columns to the table, one for the room number and another for the NewDate with
AddColumns( Table1_1, "RoomNumber", Title, "NewDate", DateAdd( Column1, (DateDiff( Column1, GanttDate.SelectedDate + Time( 0, 0, 0 ), Days )), Days ) )
Cheers for the help again.
How are your days and your rooms organized? If we're talking about a single column table, are you looking at different sheets or data sources for your different rooms?
For instance, I see from your formula where you reference a "Gantt" date. I'm figuring this is the day that the user has selected to check for availability... but if you're not then looking at a dataset that represents that date, you'll be constantly working against the same, single-column table in excel.
Help me understand the larger picture of your data setup.
You're right about Gantt date, it's for the user to choose a day to see bookings on.
I have two SharePoint lists, one for the rooms and another for the bookings and the excel spread sheet which just has the time intervals.
The Room List has room number + a few other details for each room
Booking List has room number and To and From dates for each booking.
So in this section of code:
Table1_1,"RoomNumber",Title,"NewDate",If( DateAdd(Column1, (DateDiff(Column1, GanttDate.SelectedDate+Time(0,0,0),Days)), Days) <= LookUp( 'Booking List', "M101" = 'Room Number', To
The look up is checking in the Booking List for bookings for room M101. I can also replace M101 with "Title" which would refer to Gallery1 (the Room List) such that each item in gallery1 contains the same formula comparing different rooms.
This is where my problem lies as the look up only finds the first booking for a certain room rather than all of them.
Let me know if you need more info I might be explaining this badly.
Based on how I understand your data to be constructed, I believe you are going about it the right way (to AddColumn() via a LookUp() statement). If PowerApps is dumping out, I would wonder (like you do) whether there are just too many calculations going on.
Let me think about this and see if there I can come up with another idea.
My current set up no longer dumps out but I'm continually thwarted by the limitations of LookUp.
In the OnVisible of the booking screen I've used:
ClearCollect( TimeTable, AddColumns( Times, "NewDate", DateAdd( timesCol, (DateDiff( timesCol, GanttDate.SelectedDate + Time( 0, 0, 0 ), Days )), Days ) ) )
to add the chosen date from the date picker before any other calculations are done. (there is also a refresh button to change the date later on)
The Items field in Gallery2 now simply has:
to add a column for the room number from Gallery1.
And finally the problem code in the Visible property of the button is:
If( ThisItem.NewDate <= LookUp( 'Booking List', ThisItem.RoomNumber = 'Room Number', To ) && ThisItem.NewDate >= LookUp( 'Booking List', ThisItem.RoomNumber = 'Room Number', From ), false, true )
This seems to be few enough calculations to stop it from failing but still lacks the ability to show multiple bookings.
I'm beggining to think that it's not just my lack of experience thats holding me back here.
Perhaps it's not possible?
Continue your learning in our online communities.
Next Wednesday, August 21st at 8am PDT
Let's thank our top community contributors
Features releasing from October 2019 through March 2020
Coming to a city near you
Fill out a quick form to claim your user group badge now!
Connect, share, and learn with your peers year-round
Features releasing from April 2019 through September 2019!
August 20-23rd 2019