Why i got 2 columns inside out Power Apps which represents a sharepoint lookup field

[johnjohn123]

We have the following SharePoint lookup field named large:-

and when i added the list, i got 2 fields which represent the SharePoint lookup field, as follow:-

and i can get the lookup id by using any of these formulas:-

LookUp(testtestlist,large.Id=9)

LookUp(testtestlist,'large: ID'.Id=9)

so any advice why we got 2 identical fields?

[BCBuizer]

Hi @johnjohn123 ,

Because you selected the ID column to be shown as an additional field in SharePoint:

[johnjohn123]

so at the end those 2 formulas

LookUp(testtestlist,large.Id=9)

LookUp(testtestlist,'large: ID'.Id=9)

are exactly the same?

Second question, are both search not deligable ?

@BCBuizer

[BCBuizer]

Hi @johnjohn123 ,

Both formulas will return the same items since the Id will be common across both columns. If you were to look for the Value, that's different, since 'large' has the values for large123.Title and 'large: ID' has the values for large123.ID.

Indeed both are not delegable, but can be made to be as described in this topic: https://powerusers.microsoft.com/t5/Building-Power-Apps/Nested-Gallery-Filtering-with-complex-column...

LookUp(
    AddColumns(
        testtestlist,
        "LargeId",
        large.Id
    ),
    LargeId=9
)

[johnjohn123]

@BCBuizer thanks.. so you mean , SharePoint lookups can be delegable ? i always get an advice in this forum not to use sharepoint lookup because they are not delegable, and we are always adviced to store the id as a single line of text instead of using sharepoint lookup fields...

[BCBuizer]

Hi @johnjohn123,

The formula I posted made the delegation warning go away and there are quite some tutorials out there that are promoting this method as a way of dealing with delegation issues, but it seems for LookUp columns this does not work.

I did some tests and what seems to work is to look for the primary key value. With the below settings in Sharepoint for the 'LookUp" column, I got different results for different formulas:

Filter(TEST, 'LookUp'.Value="")	No delegation warning, shows more items than the Data Row Limit
Filter(TEST, 'LookUp'.Id="")	Delegation warning, limited by the Data Row Limit
Filter(TEST, 'LookUp:ID'.Value="")	Delegation warning, limited by the Data Row Limit
Filter(TEST, 'LookUp:ID'.Value="")	Delegation warning, limited by the Data Row Limit

I then changed the SharePoint settings and ran similar tests:

Filter(TEST, 'LookUp'.Value="")	No delegation warning, shows more items than the Data Row Limit
Filter(TEST, 'LookUp'.Id="")	Delegation warning, limited by the Data Row Limit
Filter(TEST, 'LookUp:Title'.Value="")	Delegation warning, limited by the Data Row Limit
Filter(TEST, 'LookUp:Title'.Id="")	Delegation warning, limited by the Data Row Limit

My conclusion is that on LookUp type columns, you can have a delegable filter on the primary key value, not the Id, nor on the value or Id of an additional column. The workaround with AddColumns makes the delegation warning go away, but not the delegation issue.

Ps. Yes I did actually fill out values in the formulas to make sure items were targeted that should be excluded if the Data Row Limit applied.

[johnjohn123]

@BCBuizer so what is the idfferent between primary key and Id are not they the same? which is the id? can you collaborate more please?

[BCBuizer]

Hi @johnjohn123 ,

In case you set the ID column as the primary key, the value and Id will match:

This means you can filter for that information in a delegable way.

[johnjohn123]

@BCBuizer thanks for the great info. i always thought that SharePoint Lookups are not deligable ... and that we should/prefer to store the lookup id in a numeric field.. but i think ThisItem will not raise any delegation error unlike using Lookup

[johnjohn123]

@BCBuizer I am not sure if your conclusion is correct, because i modified the SharePoint lookup as follow:-

and when i try to do this lookup i got a delegation error:-