Join two SharePoint lists / AddColumns - delegation problem

[peteaxtell]

I have two SharePoint lists:

Person (Title)

Account (Title, Person)

• I need a gallery that shows all records from Person, and the Account title if one exists for that Person.
• There will be more than 2000 records, so collections cannot be used
• There will be more than 2000 records, so delegation is required on all queries
• The join has to use the ID field, because the Title field in the lookup table will not be unique

Joining the tables using Person.Id is not delegable:

I have read in a number of articles that you need to use Person.Value rather than Person.Id when using lookup fields in Filter/LookUp functions. So I have set the lookup field to have the ID as Person.Value:

Joining the tables using .Value does not work, because you are comparing a string (Person.Value) with a number (Person[@ID]):

Converting Person.Value to a number or Person[@ID] to text does not work, because the Text and Value functions are not delegable:

This is the most basic form of join for two SharePoint lists. How can this be achieved without using collections and only using delegation?

[v-siky-msft · Accepted solution]

Hi @peteaxtell ,

The Join Method you used is not delegable, that has been officially claimed from here .

However, if you just want to display the related field value in Gallery, there is no need to join the related field to table using AddColumns() function. Just add a Label in gallery and set Text property as follows, the related field value can be displayed well without any delegable warning.

LookUp(Account, Person.Value=Text(ThisItem.ID)).Title

Hope this helps.

Sik