Help with working around delegation

[Anonymous]

I have a canvas app where the datasets used are likely to grow to over 2,000 records.
I have two galleries which will be linked to one dataset called Projects.

The first one should filter on records where the logged in user is either the Author or matches one of two people fields in the data source. This relies on ‘in’ which isn’t delegable.

I also need a gallery for reviewers where they should be able to search and filter within all Projects.

I followed the advice on this video which has a formula to find the minimum and maximum records and bring them into a collection in the OnStart of the app. Although this has got around the ‘in’ issue with the first gallery, when selecting the record and navigating to the first screen with the edit form I couldn’t bring up the record and it no longer likes the variable which is used within Item. The other issue with this is that if someone adds a record and goes back to the gallery to see all their records, it won’t be updated.

Is there any alternative?

[Suchitra1996]

@Anonymous 

You can try to associate the records to a primary key to make sure that for a particular set of records, it doesn't cross the 2000 limit.

In order to display the form with selected record from gallery, you can change the item property of the form to Gallery.selected or choose to use lookup(datasource,primarykey=gallery.selected.primarykey)

On update of the record on form, you can refresh the collection when navigating back to the gallery screen.

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

[RusselThomas]

Hi @Anonymous ,

Without seeing your actual functions or resulting galleries, my suggestion would be to ensure you're making full use of your delegable functions before being forced to collect and then applying non-delegable functions.

For example, in your statement you said;

---

@Anonymous wrote:

The first one should filter on records where the logged in user is either the Author or matches one of two people fields in the data source. This relies on ‘in’ which isn’t delegable.

---

Why does this rely on 'in'?  Is there some reason you cannot use something like;

Filter(source, 'Created By'.Email = gvarUserEmail || peoplefield2.Email = gvarUserEmail || peoplefield3.Email = gvarUserEmail) 

= is delegable, as is AND and OR - provided you feed the comparitor email in as a variable and not a function you should be fine.

Your last resort should be collecting all the data and then recollecting it every time someone updates or changes something.  Sometimes non-delegation is unavoidable and we have to resort to the messier side of managing data with collections, but it might be worth revisiting the question of whether it really is unavoidable before going there.

It also helps to make sure you index your condition columns in SPO.

Kind regards,

RT

[Anonymous]

Hi @RusselThomas this is the issue I get when using = :

Author.Claims is still turning up as delegable. It doesn't like it for InfoAssetAdmin.Claims because that people column is set to allow multiple selections. Only 'in' seems to work for the latter. 

I see what you mean regarding collections. I did also try creating a collection which filtered on the above and used that for the gallery. However this still came up with a delegation warning, so perhaps there is no point in me using a collection like this anyway?

I will look at indexing the columns, thanks.

[Anonymous]

Hi @Suchitra1996 , 

I already use Gallery.Selected and this does seem to bring up the record now. When you say Primary key, I guess that would be like the ID?

That's good to know I can just refresh the collection on the navigation buttons and it will update with new entries. Thanks, I'll try that.

[Anonymous]

I could not understand your data model properly, but could you please try this:

SortByColumns(Filter( ProjectRegister, Or(Author.Claims = loggedUserClaims, IAO.Claims = loggedUserClaims, InfoAssetAdmin.Claims = loggedUserClaims), "ID", Descending)

Regards,

Himanshu

[RusselThomas]

Hi @Anonymous ,

Ok, so I see where 'in' comes in to it (pun intended 😊)

It's pretty ugly when you need to filter a set of data by subsets of data within that data - invariably, unless PowerApps starts catering for connecting to list views instead of just list sources, you may be stuck with workarounds like building collections or using Power Automate to go fetch filtered data for you - eg: substringof(multipersonColumn, 'user@home.com') should do the trick in PowerAutomate, but when applied in the context of using it in place of a data connector - 'yuck' is about all I can say.  Slow and a waste of Flow quota.

The idea behind using a list view would be to construct a view where the relevant filter is already applied  - i.e. [Me] in multipersonColumn.  Effectively implemented, it could solve a lot of delegation issues.  

Any way you look at it, not a pretty picture - as you've seen, your collections will be "stunted" by the delegation issues - you could try unpivoting the column into another table with a reference to the root record and provide a gallery view with lookups to the root record through that - it would address your delegation, but if you have multiple columns with multiple values - egh....

Sorry I couldn't be of more help,

RT 

[Anonymous]

Thanks @Anonymous. I've tried this but the main issue is that my column InfoAssetAdmin is multi-select so the = won't work here.

[Anonymous]

@RusselThomas That would be great if PowerApps could connect to list views. If it's in development it gives me hope as it will take a while before this dataset reaches 2,000 items. 

Yeah, unpivoting the column into another column will be too messy in this scenario. 
There is no ideal solution by the sounds of it. Thanks for your advice - it's good to see what options there are at least.

[Anonymous]

For reference, I found this idea is under review but has been since 2018!