cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hannah_S
New Member

Filter Gallery - Hide items based off records in a second SharePoint list (Date&ID)

Hello, I'm a very new user of power apps and I've hit a wall on how to filter my gallery and any help would be very appreciated!

 

Here is my current situation: I am creating a desk booking app and when a user is picking their desk to book from the gallery - I want to hide the desks that have already been booked for that day.

 

I am currently using 2 sharepoint lists, 1 to hold the desks ('Desks') and the 2nd to hold the desk reservation records ('Desk Reservations').

 

This is what I currently have in my Gallery 'items':

Hannah_S_0-1632305224624.png

 

So I need to filter the list of 'desks' being displayed in the gallery by the following three columns in my 'desk reservations' list - 'DeskText', 'Check out from' & 'Check out to' . 

 

The aim being to hide desks from displaying in the gallery if a record in 'desk reservations' already exists with the same 'DeskText' (this is the ID) & the date you are trying to book the desk matches 'Check out from' & 'Check out to'

Hannah_S_1-1632305418460.png

 

I don't want to take the time into consideration e.g. if a desk is booked until 13:00 another user shouldn't be able to make a booking for the remainder of the day. Instead this desks should be unavailable for the whole of the day if booked.

 

Within the app I'm using 'starttime' & 'endtime' to capture the length of the booking as the desk can be booked for multiple consecutive days. - but this captures both date & time 

Hannah_S_2-1632306095743.png

 

Like I mentioned previously any help or assistance on this would be greatly appreciated! Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @v-qiaqi-msft , thank you for your response. I manged to solve my issue by creating a new column to hold the check out to/from times but just using date in a number format not date/time and then using those. 

 

 

Not(Title in Filter(
    'Desk Reservations',
    
    'Check Out From Number'<= Value(Text(startTime,"yyyymmdd"))&&
    'Check Out To Number'>= Value(Text(endTime,"yyyymmdd")) ||
    'Check Out From Number'= Value(Text(startTime,"yyyymmdd"))&&
    'Check Out To Number'= Value(Text(endTime,"yyyymmdd"))

    ).DeskText)

 

 

View solution in original post

4 REPLIES 4
PG_WorXz10
Resident Rockstar
Resident Rockstar

Hi @Hannah_S ,

 

Change the column type into Date only Type from Date and Time type.  

If this post was helpful or you need more help please consider giving Thumbs Up and Tag me in your reply I'll be happy to help. If this post helped you solve your issue please click Accept as solution and provide Thumbs Up. This will help others find it more readily.

Hi @PG_WorXz10,

 

Thank you for the reply. While I do not need to take the time into consideration for this particular filter, the time is still used in other areas of the app so I'm unable to change the SharePoint column type to only date. 

 

Am I unable to use the date and time column to only filter by the date?

Hi @Hannah_S,

Have you solved your problem?

Do you want to filter based on the date?

Could you please share a bit more about the scenario?

If you only want to compare the date from your 'Check out from' and  'Check out to'column, I think you could use the Text() function to achieve this.

DateValue(Text(DatePicker9.SelectedDate,DateTimeFormat.ShortDate))

Guess that you may have hour select and minute select in your scenario, you could modify the above formula as below:

DateValue(Text(DatePicker9.SelectedDate + Time(Value(HourValue2.Selected.Value), Value(MinuteValue2.Selected.Value), 0),DateTimeFormat.ShortDate))

Note that the HourValue2 and the MinuteValue2 represent the Dropdown to select hour and minute.

Best Regards,
Qi

Hi @v-qiaqi-msft , thank you for your response. I manged to solve my issue by creating a new column to hold the check out to/from times but just using date in a number format not date/time and then using those. 

 

 

Not(Title in Filter(
    'Desk Reservations',
    
    'Check Out From Number'<= Value(Text(startTime,"yyyymmdd"))&&
    'Check Out To Number'>= Value(Text(endTime,"yyyymmdd")) ||
    'Check Out From Number'= Value(Text(startTime,"yyyymmdd"))&&
    'Check Out To Number'= Value(Text(endTime,"yyyymmdd"))

    ).DeskText)

 

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,658)