cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Benjix
Frequent Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Benjix
Frequent Visitor

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.

View solution in original post

9 REPLIES 9
WarrenBelz
Super User III
Super User III

Hi @Benjix ,

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.

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?

Hi @Benjix ,

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.

 

WarrenBelz
Super User III
Super User III

Hi @Benjix ,

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.

Benjix
Frequent Visitor

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.

View solution in original post

Thanks @Benjix ,

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.

@Benjix ,

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).

 

Benjix
Frequent Visitor

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!

Hi @Benjix ,

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

GalHideDelegation.png

 

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.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,415)