0

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

  • Bonus problem: what if the "additional" column is not in this very short list of supported column types -- what if, for example, it's "multiple lines of text" or "calculated"? I believe adding an unsupported column as in $select=RelatedRecord/SomeCalculatedColumn&$expand=RelatedRecord causes the REST call to fail with an "invalid field" error. – Coderer Aug 10 at 15:34
0

As I was writing this, I found a potential lead: the "additional" column definition (RelatedRecord:ID) has a PrimaryFieldId property which references the column I actually created (RelatedRecord). Using this, I can build the $select argument for the lookup column:

const selects = [];
const expands = [];
fieldDefinitions.forEach(field => {
  if (field.PrimaryFieldId) {         // this field is "additional"
    const primary = fieldDefinitions.find(f => f.Id === field.PrimaryFieldId);
    // first part is the name of the "primary column", e.g. "RelatedRecord"
    // second part is the target-list field name, e.g. "ID"
    selects.push(primary.InternalName + "/" + field.LookupField);
  } else {                            // this field is "primary"
    // $select=RelatedRecord/Title&$expand=RelatedRecord
    selects.push(field.InternalName + "/" + field.LookupField);
    expands.push(field.InternalName);
  }
})

query += "$select=" + selects.join(",") + "&$expand=" + expands.join(",");

I think this approach will work but I'm still interested in hearing other perspectives, or maybe if there's a better way to do this. (Please remember, I'm trying to code something that will work against arbitrary lists whose structure I won't know at design-time, so it has to be based on the data returned by /lists/getByWhatever(...)/fields.)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.