cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lewkir
Level: Powered On

Alternative to Lookup

Hi,

 

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.

 

cheers,

TimRohr
Level 10

Re: Alternative to Lookup

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.

Lewkir
Level: Powered On

Re: Alternative to Lookup

Hi Tim,
 
Thanks for the response.
 
I didn't include too much detail in my original post as I wanted to avoid overwhelming anyone with information which is what I suspect happened in my previous question.
 
Now I have you I'll try to be as clear as possible.
 
I am trying to create a way of visualising which rooms are booked on a particular day at particular times using nested galleries as the below picture poorly represents:
HorriblePaintJob.png
 
 
Gallery1 refers to a list of room numbers and gallery2 to a table imported from excel of times from 00:00 to 24:45 in 15 min increments. (1a, 1b,1c should really be Room 1 00:00, Room 1 00:15...etc)
 
Using the code in the original post I have managed to get each room to display one set of booked hours by using buttons placed in gallery2 items which are set to Visible = true if the item's time falls within To and From in a list of Room bookings.
 
I added two columns to the table in gallery two so they refernce the current day and correct room for the item in gallry1:
Gallery2.PNG
 
Gantt date is the name of gallery2 as my manager said that I was building a gantt chart but now I'm not sure.
 
Moving on.
 
Now all I need to do is get the buttons to be visible if they fall within any of the To or From dates in the original list.
 
let me know if you need further clarification, I'm aware my prose may not be the best for conveying what I'm trying to get at.
 
Cheers again,
 
 
 
 
TimRohr
Level 10

Re: Alternative to Lookup

It sounds like you want the button to be visible if there is something in the cell (that is, if the room is booked already)... that seems backward to me, but if you're sure about that, we'll go with it. Your sub-gallery is pulling from an Excel spreadsheet. That sheet, I'm assuming, can have one of two arrangements... either it has a column for every 15 min. increment, or it has a row. Your potential solution will depend on how your data is structured. Can you share more of the structure of your 2 tables? In short, if you have a column for every 15 min increment, your buttons will individually check the field they are "assigned" to, having a function like: !IsBlank(ThisItem.myFifteenMinuteField) ...in its Visible property. That would mean that if the field for that button was blank (not booked), your button would be invisible. On the other hand, if the arrangement of the time slots are in a single column, you might have to do individual LookUps() in the Visible properties, or CountIf(Filter()) > 0 type constructions. The CountIf() statement isn't delegable, I don't think, but that won't matter. Given the right criteria in your Filter statement, you will only ever be returning 1 row. If that doesn't make sense, post back with more information about your setup.
Community Support Team Rank
Community Support Team

Re: Alternative to Lookup

Hi @Lewkir,

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.

 

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.
Lewkir
Level: Powered On

Re: Alternative to Lookup

@TimRohr @v-xida-msft

Hi both,

 

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

00:00

00:15

00:30

00:45

.

.

 

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.

 

Code is:

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.

TimRohr
Level 10

Re: Alternative to Lookup

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.

Lewkir
Level: Powered On

Re: Alternative to Lookup

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.

TimRohr
Level 10

Re: Alternative to Lookup

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.

Lewkir
Level: Powered On

Re: Alternative to Lookup

Hi Tim,

 

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:

AddColumns(TimeTable,"RoomNumber", Title)

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?