Delegation warning

[AG2019]

All,

I have a question about a delegation warning with Sharepointlists. Below 2 code examples for a label in a gallery

1) LookUp(TableA, ID = LookUp(TableB, ID = ThisItem.ForeignKeyToB, TableA_ID), Column X)

2) LookUp(TableA, ID = First(Filter(TableB, ID = ThisItem.ForeignKeyToB)).TableA_ID, Column X)

Both give me a delegation warning (underlined in the examples). I don't understand this warning in combination with https://docs.microsoft.com/en-us/connectors/sharepointonline/

Can anyone explain

1) will it give me an issue during runtime (missing data)?

2) what is the best approach option 1 or 2 (or maybe something else)?

[Anonymous]

@AG2019 

For formula 1, there's a type mismatch ie ID = this formula. SP only supports ID= ID in regard to IDs. You could try set a variable to the underlined part, but it looks like you are in a gallery so that may not work? You could try adding a label with the underlined part and reference test instead?

Formula 2, First will only return at most 2000 records, hence the warning. Yes, you will be missing data where you have delegation issues. 

Solving, galleries provide access to all your data, then you can use filters to find what you need. @RezaDorrani has a great video series on how to manage delegation with SharePoint, which is well worth watching.

[AG2019]

@Anonymous 

I don't see the type mismatch you're referring to. And as described this are items in a gallery so a variable is not going to work.

I assume that "First(Filter(TableB, ID = ThisItem.ForeignKeyToB))" will return 2000 items of TableB filtered by ThisItem.ForeignKeyToB, is that correct?

If so, then I'm sure that this will work. Because it will only have 1 hit in the database, that is why a lookup will have the same result 🙂

The only issue is that the lookup also gives me a delegation warning.

The video's of reza are indeed interesting, but in this case not applicable. I have already filtered my dataset for the gallery without warnings, now I try to display data in the gallery in labels where I also get warnings.
Note: we have a dataset with only referenceID's not lookups, that's why I need to do the lookup myself

[Anonymous · Accepted solution]

@AG2019 

I was able to get a working solution using a separate label in a Gallery, which you can hide. My setup

Table C

- Gallery1 - is where ThisItem sits, Items Property = Table C (I think this is correct?)

- has ForeignKeyToB

Table B

- has TableA_ID

Table A

- data we are trying to access

In Gallery1, add a label with this code. Call this label FK_A (or whatever you like):

LookUp(TableB, ID = ThisItem.ForeignKeyToB, TableA_ID)

In Gallery 1, add another label with this code

LookUp(TableA, ID = Value(FK_A.Text), Column X)

I do get the same delegation warning if I try to combine these formulas but not if I do the above. I also reduce the Max number of records setting (in Advanced settings) down to 2 to test this out because sometimes you won't get a delegation warning but the correct data won't get returned. Reza explains the importance of this in his videos.

Note: my ForeignKeyToB and TableA_ID columns in SP are both of type Number so make sure yours are the same as well 🙂 

[KrishnaV]

Hi @AG2019 ,

I totally agree with @Anonymous please find the below article explains the functions that are delegable and not delegable.

https://powerapps.microsoft.com/en-us/blog/sharepoint-delegation-improvements/

Here is a great playlist created by reputed PowerApps MVP @RezaDorrani about deligation:
PowerApps Delegation 

I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.