Filter items in SharePoint lists linked with One-to-Many relationships (satisfying delegation)

(0) Share

Report

Posted on by Community Power Pla...

Hi,

I have three SharePoint lists:

One list "Cases List";
One list "Documents List", which has one Lookup field "Case" matching the ID of an item in the list "Cases List";
One list "Reviews", which has one Lookup field "Document" matching the ID of an item in the list "Documents List".

I am desperately trying to get a Gallery or Data table listing items in "Cases List" whose ID would be in the Lookup field of items in "Documents List" whose ID would be in the Lookup field of items in "Reviews" that respect a certain condition (3 steps).

In other words, I need to retrieve items according to cascaded one-to-many relationships.

I spent hours trying many different ways io order to figure out a code that would satisfy delegation requirements, without success. Actually, "Cases List" could quickly reach more than 20,000 items, with several documents per case and several reviews per documents).

Moreover, I do not understand on of the most elegant attempts I did:

Filter('Cases List',
 ID in Filter(
 'Documents List',
 ID in Filter(Reviews, Reviewer.Email = varUserEmail).Document.Value
 ).Case.Value
)

Here is the syntax error I get: Name isn't valid. This identifier isn't recognized.

Capture d’écran 2021-03-26 122345.png

I know the use of "IN" operator is not delegable, but in case there is no other solution to make it work with SharePoint lists, I will consider using SQL, which supports delegation on "IN" operator. I could also consider changing to Dataverse if it allows me to enforce delegation.

Any help would be greatly appreciated ! 😊

Many thank in advance

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

WarrenBelz 153,084 Most Valuable Professional on at

Like (0)

Report

Hi @Anonymous ,

Firstly SharePoint is not a relational database and relational Lookups are simply not Delegable (neither is the in Filter). Also the "double lookup" you are doing is not something that a SharePoint design should have (in my humble opinion at least) as it will cause you nothing but unnecessary grief.

If you have 20,000 items (I have a couple of list of that size), you simply need to confine yourself to Delegable filters.

I have a blog on data structure that may be of some use to you, but the real "trick" in easily planning any likely query on big lists is to "de-normalize" it. Sounds old school (and I can hear the SQLphiles shuddering), but I write any likely relational data into the list on record save (or sometimes the OnChange of a control). It also helps users of SharePoint on the data as they can group/filter/sort on it and export all the relevant fields to Excel.

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.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi @WarrenBelz ,
Thanks for your reply!
Waaah, your blog is actually a goldmine! You address questions that I was wondering without managing to find decent answers on the web or on Microsoft's documentation.

Getting along with lookup fields (and also SharePoint list IDs) is not particularly easy. I think I will follow your advice and use text and/or number fields instead (probably maintained via automatically triggered Power Automate flows).

Regarding de-normalization, that was in fact something I had in my mind but it sounded so horrible that I did not even dare suggesting the idea to my coworkers. At least lookup fields had the advantage of allowing multiple values for one single item. That would have sweetened the pill...

What do you think about doing some minor changes to the present data structure in a first time like adding a fourth list that would act as a map table linking all three other lists thanks to their respective IDs (in a nutshell, three numeric fields within this fourth list)? Would this do the trick or is there some hidden trap that I do not see yet?

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (0)

Report

Hi @Anonymous ,

Numeric fields in themselves are Delegable and you can use a Collection to reference smaller lists (then the "relational" LookUp is Delegable as it does not reference the data source, but rather an internal value). This is a large subject and if you have not read them yet, I have a blog on Delegation and the further item on using the With() Statement where I refer to Relational Lookups.

As for LookUp fields - I encourage you to follow your suggestion, do these in Power Apps and simply write back to a text field. If you must have them - "leave them alone" in Power Apps (let the system add all the settings) and they will work, but once you start modifying particularly the Items, you will have a lot of unwanted grief.

The whole point here is that with an E3 licence, you have bought the "base model" - like a car, it will not have self-driving, but it will still get you to your destination with a little planning. Forget SQL-type queries to a large extent - it has its own "engine" handling these - SharePoint has limited (but effective when you use them properly) Delegation capabilities.

We run a substantial office and field-based operation on SharePoint.

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.

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (0)

Report

Hi @Anonymous ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

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.

Was this reply helpful? Yes No
Verified answer

Community Power Pla... on at

Like (0)

Report
Hi @WarrenBelz ,

Sorry for this late reply, I have been kept busy on other topics.
I tried using a fourth list behaving as a map table, but that only moves the issue to somewhere else without solving any delegation issue. I would consider denormalizing the data as a last resort. The use of a collection is not something I am particularly pleased to rely one, but this seems to be a more acceptable solution as long as we bring such limitation to the end user (transparency).

But I just figured out a way that does not require the use of collections nor de-normalization and still seems compliant with delegation. That may interest some people here I think.
I just discovered via the youtube channel MySPQuestions (video here) that using a flexible-height gallery, you could use "IN" operator in the visible property of items without any delegation issue. I mean that does not only remove the delegation warning, but it seems also to work fine with lists counting several thousand rows. However, that still did not fully solved my issue, which also deals with chained lookups between lists. After several days of "die and retry", I just found out that you can use 3 flexible galleries, one for each SharePoint list.

The galleries corresponding to lists C and B are kept hidden in another screen. They just need to be present somewhere. The gallery corresponding to list A is the gallery in am interested in. The the visible property of items in this latter gallery, I wrote nested filters with "IN" operator. Somtehing like this:

ThisItem.ID in Filter( Gallery2.AllItems, ID in Filter( Gallery3.AllItems, <other condition> ).LookupIDinGal3 ).LookupIDinGal2

I decreased the delegation limit to a very small number, and this seems to be working fine. I encourage you to try on your side and give me feedback if I celebrated too soon.

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (0)

Report

Thanks @Anonymous ,

Interesting solution - someone thinking "outside the box".

It will of course only work on Galleries (not Collections or other data gathering functions) and I think will struggle with large data sets as Delegable queries in a gallery are only returned 100 at time (they resolve as you scroll down).

I will however mostly stay with my Delegable field types and de-normalized data model - I do not generally need any workarounds with this.

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (0)

Report

@Anonymous ,

Just tested it here on a reasonable-sized list (4,500 items). I was half-correct in the issue with the issue of 100 items displaying. I tested it on a partial string that appears in name throughout the list and it slowly found them - took over a minute to resolve a number of them, with a slider at the right that seemed to speed things up a bit if manually slid down. Bear in mind that you can use the With() Statement for lists (or delegable filtered results of these) up to 2,000 items and collect up to 4,000 easily without having a gallery resolving the height of thousands of rows (which as well as the extra items over 100 is why I suspect it is very slow on large data sets).

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi @WarrenBelz ,
Many thanks for having given a try with a (reasonnably) large dataset.
I admit I still did not perform a scale test using this solution and, as you mentionned, I may have to face performance issues.
I may end up with implementing the wise suggestions you provided.
I thank you a lot for all the expertise you brought to my knowledge!

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (1)

Report

Hi @Anonymous ,

You might revisit the article now - I posted a clarification question and the response is below.

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.

Was this reply helpful? Yes No