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

Delegable Gallery (SharePoint list) filtering based on LookUp of multiple records in table

Hi everybody,

I’m trying to filter a Gallery (with many SharePoint items) based on multiple refiners values selected by the user. The selected values for refinement are stored in a collection and when trying to filterthe gallery's item's based on multiple values I am running into delegation issues.

I’ve tried several different things but none of them seem to be a good workaround, below you’ll find a shortened version of the app structure.

Filter collection:
Using multiple combo boxes, I create a collection (selectedFilters) that stores all the refiner values selected by the users. the example below shows this collection format for the Cost and Project Progress refiners (both corresponding to SharePoint columns.

FilterName

SelectedFilterProperties

Cost

[Table]

Project Progress

[Table]

 

Each [Table] in the collection above represent a single column table containing one or more records (the comboxes are multiselect). For Project Progress that [Table] could for example be:

Value

Started

Completed

i.e. the user want to find all projects that have either been started or are marked as completed.

Gallery:
I filter the SharePoint items (Projects) in the gallery using the following formula;

 

Filter(Projects,
    (IsBlank(LookUp(selectedFilters, FilterName = "Cost", SelectedFilterProperties)) Or Not(IsBlank(LookUp(LookUp(selectedFilters, FilterName = "Cost", SelectedFilterProperties), Value = Cost))))
    And
    (IsBlank(LookUp(selectedFilters, FilterName = "Project Progress", SelectedFilterProperties)) Or Not(IsBlank(LookUp(LookUp(selectedFilters, FilterName = "Project Progress", SelectedFilterProperties), Value = Cost))))
)

The filter formula is esentually evaluating each record;

 

  • As true if the filter property is Blank (meaning the user hasn’t made a selection)
  • Or as true if the LookUp to the values of a specific filter property in the selectedFilters collection compared to the record’s property returns as not Blank (meaning there’s a match).

This formula works well within the initially loaded SharePoint list items, but isn’t delegable because Not(IsBlank(something)) is not delegable.

I’ve tried to work around it by using =Blank instead - which should be delegable - like so:

Filter(Projects, Switch(LookUp(LookUp(selectedFilters, FilterName = "Cost", SelectedFilterProperties), Value = Cost).Value = Blank(), false, true, false))

Like it's predecessor, this workaround does also work, but also it is prone to the same delegation problem.

 

I’m hoping one of you PowerApps gurus here can help me out or at least point me in the right direction.


Thanks in advance,
Rob

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Delegable Gallery (SharePoint list) filtering based on LookUp of multiple records in table

Hi @ROB365 ,

There are ways around the delegation issue in Sharepoint and most have to do with the number of Items that you are working with in your gallery.  If it is less than 2000, you can ignore the delegation issue.  If it is between 2000 and 4000 you can use a technique that @martinav and I came up with, essentially creating two collections from the list, one sorted ascending and the other descending, combining them and then removing the duplicates, (delegation isn't an issue when you deal with collections). Collections-maxes-out-at-2000-when-populating-with-SharePoint/ However, if there are > 4k it becomes more complicated.  So the issue is how many items do you have in your lists?  

 

Super User
Super User

Re: Delegable Gallery (SharePoint list) filtering based on LookUp of multiple records in table

@ROB365 

If it was me, I would plan on migrating to CDS or Sql in a few years. In the meantime use SharePoint to perfect your app.

5 REPLIES 5
Super User
Super User

Re: Delegable Gallery (SharePoint list) filtering based on LookUp of multiple records in table

Hi @ROB365 ,

There are ways around the delegation issue in Sharepoint and most have to do with the number of Items that you are working with in your gallery.  If it is less than 2000, you can ignore the delegation issue.  If it is between 2000 and 4000 you can use a technique that @martinav and I came up with, essentially creating two collections from the list, one sorted ascending and the other descending, combining them and then removing the duplicates, (delegation isn't an issue when you deal with collections). Collections-maxes-out-at-2000-when-populating-with-SharePoint/ However, if there are > 4k it becomes more complicated.  So the issue is how many items do you have in your lists?  

 

Super User
Super User

Re: Delegable Gallery (SharePoint list) filtering based on LookUp of multiple records in table

@ROB365 , @Drrickryp ,

 

Check this out as well.  If you have massive rows of data.  I tested this with an sql table of over 21,000 rows and it worked great.  I'm not sure if there is a limit with this method, just be aware that the bigger it is, the longer it will take to load.  But, it at least works.

 

https://powerusers.microsoft.com/t5/General-Discussion/Another-method-for-making-a-collection-from-S...

 

Good luck!!!

ROB365
Level: Powered On

Re: Delegable Gallery (SharePoint list) filtering based on LookUp of multiple records in table

Thanks for both of your suggestions @Drrickryp & @martinav. I've currently only got a data set of 1350 listitems but this number is expected to grow with ~1000 items each year. Ideally I'd design the solution with this 'future state' in mind so I will take both of your options into consideration.

Do you (or anybody else) see any way in which I could improve my current filtering formulas to make them work (with delegation)?

Super User
Super User

Re: Delegable Gallery (SharePoint list) filtering based on LookUp of multiple records in table

@ROB365 

If it was me, I would plan on migrating to CDS or Sql in a few years. In the meantime use SharePoint to perfect your app.

ROB365
Level: Powered On

Re: Delegable Gallery (SharePoint list) filtering based on LookUp of multiple records in table

@Drrickryp  You raise a good point! Hadn't looked at that option for future purposes but I have decided to stop 'over engineering' it now and mitigite the risk of this issue through keeping an eye on growing numbers and any PowerApp development related to the SharePoint connector in particular. A couple years down the track (if requirements don't change) we can always address the issue like you said.

In the meantime I'll use the '<4000' solution you and @martinav came up with. Many thanks to both of you.