Filter gallery based on 2 columns from 1 SharePoint list and 1 column from another SharePoint list.

[PADave]

Good evening hive mind,

I have a requirement to build an app to book desks for people that need to go to one of our hub offices.

The app is based on 2 lists in a SharePoint site 'Desks' (available desks to book) & 'Desk Reservations' (list of who has booked what desk and when).

The app functions as needed but the only issue I have left to resolve is from a delegation error I get when trying to filter the gallery of available desks for a certain date.

The gallery for the available desks will need to be filtered on 3 criteria:

1. A combo box that filters the directorate of where the desk is located (this is imported from the choices of column 'Directorate' in the 'Desks' SharePoint lists).

2. The 'Active' column (choice) in the 'Desks' SharePoint lists is set to Yes

3. The desk hasn't already been taken (pulls information from the 'Desk Reservations' list).

I currently have the filter working as follows but it seems to me you can't delegate using 'Not' and can't figure a way to switch it round to not use the 'Not' function:

SortByColumns(
Filter(
Desks,
Directorate.Value = cmbDirectorate.Selected.Value || If(IsBlank(cmbDirectorate.Selected.Value),
true,
false
),
Active.Value="Yes",
Not(
Title in Filter(
'Desk Reservations',
CheckOutFrom >= startTime && CheckOutTo <= endTime || CheckOutFrom <= endTime && CheckOutTo >= endTime
).DeskText
)
),
"Location",
Ascending
)

On app start I have:

ClearCollect(
colDirectorate,
Choices(Desks.Directorate)
);

ClearCollect(
colActive,
Choices(Desks.Active)

);

Variables are:

CheckOutFrom - Set when you pick a date before you get to the desk booking screen

CheckOutTo - Set when you pick a date before you get to the desk booking screen

Can anyone help me with how I can pull in information for part 3 of the requirements and not show desks that have already been booked?  What I need is to replace the 'Not' part with something that does the same job but doesn't raise a delegation error.

On a side note:

At the end of the app there is a button to add to calendar which patches the information into the users calendar.  Does anyone know of a way we can get the calendar entry ID or someway or recalling that calendar entry so it can be deleted at a later date if the desk is no longer needed and the booking is cancelled?

Thanks in advance,

Dave

[WarrenBelz]

Hi @PADave ,

There are two things here not Delegable - the Not() function and the Relational (to another list) Filter. You can solve both (and have a bit of "future proofing" with the below. The ID sort is only to use the newest (500-2,000 depending on your settings) records in the filter.

With(
   {wReserve:Sort('Desk Reservations',ID,Descending)},
   SortByColumns(
      Filter( 
         Desks,
         (
            IsBlank(cmbDirectorate.Selected.Value) ||            
            Directorate.Value = cmbDirectorate.Selected.Value 
         ) &&
         Active.Value="Yes" && 
         !(
            Title in Filter(
               wReserve',
               (
                  CheckOutFrom >= startTime && 
                  CheckOutTo <= endTime
               ) || 
               (
                  CheckOutFrom <= endTime && 
                  CheckOutTo >= endTime
               )
            ).DeskText
         )
      ),
      "Location",
      Ascending
   )
)

I have also done blogs on Delegation and the With() statement that may be of interest to you.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[PADave]

Thanks @WarrenBelz.

I added this into the dev app but got an invalid number of arguments error.

I now know that there are 2 non delegable in the formula so I'll look into that using your with() statement suggestion. I hadn't come across that yet but I'll take a look at your blogs later today to see if I can work it out.

Thanks for your help.

[WarrenBelz]

@PADave ,

I free-typed this (and cannot test it) - Try it without a couple of brackets as below

With(
   {wReserve:Sort('Desk Reservations',ID,Descending)},
   SortByColumns(
      Filter( 
         Desks,
         (
            IsBlank(cmbDirectorate.Selected.Value) ||            
            Directorate.Value = cmbDirectorate.Selected.Value 
         ) &&
         Active.Value="Yes" && 
         !Title in Filter(
            wReserve',
            (
               CheckOutFrom >= startTime && 
               CheckOutTo <= endTime
            ) || 
            (
               CheckOutFrom <= endTime && 
               CheckOutTo >= endTime
            )
         ).DeskText
      ),
      "Location",
      Ascending
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[PADave]

Thanks for your help with this @WarrenBelz .

Unfortunately, I couldn't get the with function working as I needed so in the end I added 2 combo boxes and used these to filter the data before it get's to the not (I think that's how it worked).  The not function still throws the delegation error but even if I reduce the delegation number to 30 in settings (I have nearly 400 items in the list) it pulls all the correct information into the gallery.  I didn't think that would work but it appear to.

I ended up using the following:

SortByColumns(Filter(
Filter(
Desks,
IsBlank(cmbLocation.Selected) || Location = cmbLocation.Selected.Title,
Active.Value = "Yes"
),
IsBlank(cmbDirectorate.Selected) || Directorate.Value = cmbDirectorate.Selected.Title,
Not(
Title in Filter(
'Desk Reservations',
CheckOutFrom >= startTime && CheckOutTo <= endTime || CheckOutFrom <= endTime && CheckOutTo >= endTime
).DeskText
)
),
"Location",Ascending,
"Title",Ascending)

[WarrenBelz]

Hi @PADave ,

Firstly, please have a read of my blog on Delegation - if you are going to use both (or either) the Not() and In filters directly on a SharePoint list, you will get a Delegation warning as Power Apps has no control over your future list size.

You could actually do this if you do not want to see the warning and have the filter work up to your Delegation limit on the newest records in both lists.

With(
   {
      wReserve:
      Sort(
         'Desk Reservations',
         ID,
         Descending
      ),
      wDesks:
      Sort(
         Desks,
         ID,
         Descending
      )
   },
   SortByColumns(
      Filter( 
         wDesks,
         (
            IsBlank(cmbDirectorate.Selected.Value) ||            
            Directorate.Value = cmbDirectorate.Selected.Value 
         ) &&
         Active.Value="Yes" && 
         !Title in Filter(
            wReserve',
            (
               CheckOutFrom >= startTime && 
               CheckOutTo <= endTime
            ) || 
            (
               CheckOutFrom <= endTime && 
               CheckOutTo >= endTime
            )
         ).DeskText
      ),
      "Location",
      Ascending
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[PADave]

@WarrenBelz that's great thanks.

I'll add this to the dev version of the app and try it out with a view of migrating it to production when I get it working.

Thanks for your help with this.