I've created a list "My List" with a column called "RelatedRecord", which is a lookup against other items in "My List". When I defined the column, I selected the field "Title" under In this column:
, and also selected "ID" under Add a column to show each of these additional fields:
. When I look at the list in Sharepoint, I see a "Related Record" column with a link to the other item, and I see a column "RelatedRecord:ID" which shows the ID number for the linked item. This all works as expected.
I am retrieving the items in a web application using the REST API (via the @pnp/sp
NPM package, but I don't think that's relevant to my question). I'm trying to dynamically display the list items, so I read the /lists/getByTitle('My List')/fields
endpoint to determine the structure of the list. This works well for almost all column types, except this pair of Lookup columns. The /fields
endpoint returns (trimmed to what I think is relevant):
[{
"EntityPropertyName": "RelatedRecord",
"FieldTypeKind": 7,
"Id": "4550737e-9f64-463f-b948-722b60de461d"
"InternalName": "RelatedRecord",
"IsRelationship": true,
"LookupField": "Title",
"LookupList": "{5761b1f6-ca0e-4841-81b1-4fff1feee3f0}",
"LookupWebId": "17d5405b-c55a-4296-ad22-23c59525c414",
"PrimaryFieldId": null,
"ReadOnlyField": false,
"Title": "RelatedRecord",
}, {
"EntityPropertyName": "RelatedRecord_x003a_ID",
"FieldTypeKind": 7,
"Id": "edfc59b3-03b8-4534-820b-cea865b36cbc",
"InternalName": "RelatedRecord_x003a_ID",
"IsRelationship": false,
"LookupField": "ID",
"LookupList": "{5761b1f6-ca0e-4841-81b1-4fff1feee3f0}",
"LookupWebId": "17d5405b-c55a-4296-ad22-23c59525c414",
"PrimaryFieldId": "4550737e-9f64-463f-b948-722b60de461d",
"ReadOnlyField": true,
"Title": "RelatedRecord:ID",
}]
If I select all items via /lists/getByTitle('My List')/items
, the result includes a field called RelatedRecordID
. (Note that neither property has an EntityPropertyName
with this value!) I can use $select
/$expand
to create a lookup like /items?$select=*,RelatedRecord/Id,RelatedRecord/Title&$expand=RelatedRecord
. This returns the values for the two columns that I see when viewing the list in Sharepoint.
I figured this out "manually", but I'd like my application to be able to display arbitrary lists (with lookup columns) by parsing the definitions returned by the /fields
endpoint. So, my question is, how can I go from the field definitions in the JSON above, to constructing a REST API query for items that adds the correct lookup fields? The definition for the "additional column" (RelatedRecord:ID) does not actually include the string I need to pass to $expand
(RelatedRecord
), or does it give me the information I need to build the correct string to pass to $select
(RelatedRecord/ID
).
$select=RelatedRecord/SomeCalculatedColumn&$expand=RelatedRecord
causes the REST call to fail with an "invalid field" error.