cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TimRohr
Level 10

Re: Alternative to Lookup

I had another thought...

What about a sub-gallery that was in a horizontal layout. Each 15 min slot would be represented by a template, and each template would have a button that would simply have to be Visible or not based on the 1 field that it had to be concerned with.

That might reduce the effective number of calculations PowerApps has to do. If necessary to get the data in the right format/orientation, you might need to create a pivot table presentation of your table in Excel and then connect that as a datasource. 

Lewkir
Level: Powered On

Re: Alternative to Lookup

I'm not sure I completely follow.

 

So do you mean the excel table would also be horizontal and the template would do the calculation?

 

I think I would still run into the Lookup problem unless this template could work without it.

 

Could you elaborate a little further?

 

Thanks

Lewkir
Level: Powered On

Re: Alternative to Lookup

Just thinking again.

 

Perhaps the ForAll function could come into play here.

 

If I first filter the Booking list to show only bookings for the room on a particular day it could then check if each booking's To and From falls either side of a particular item in the gallery and then add true/false in a new column in the gallery if so.

 

That might end up being too much work for many rooms but I'm going to see if I can do it (it might not be possible).

TimRohr
Level 10

Re: Alternative to Lookup

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:

!IsBlank(ThisItem.Slot1)

 

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.

Lewkir
Level: Powered On

Re: Alternative to Lookup

Hi Tim,

 

Sorry for the late response.

 

Took  break from PowerAps for my sanity/to work on other projects.

 

Thanks I tried a similar thing to the above and it has worked (still using the nested galleries as I wasn't sure how else to get everthing todisplay).

 

I may do some more tweaking as I am getting a few delegation warnings but for now I have other parts to build.

 

Thanks for your help.