How to discover selectable/expandable fields of lists?

[gomain]

Category

• [ ] Enhancement
• [ ] Bug
• [x] Question
• [x] Documentation gap/issue

Version

Please specify what version of the library you are using: 3.7.0

Please specify what version(s) of SharePoint you are targeting: SharePoint Online

Question

This is probably not the right place to ask, but I'll give it a shot.

I am exploring usage of .expand, and .select on IItems in querying items of lists. Specifically the "Documents" list.

const documents: IList = sp.web.lists.getByTitle("Documents");
const items: IItems = documents.items;
console.log(
  await items
    .expand(...)
    .select(...)
    (),
);

I have followed the guides in resolving EntityPropertyName of fields.

This was a naive attempt to select all fields.

const fields = (await documents.fields.select("EntityPropertyName")())
  .map(({ EntityPropertyName }) => EntityPropertyName);
console.log(
  await items
    .select(...fields)
    (),
);

This led to a series of tweaks and experiments trying to figure how things work. Here are some of my findings that is causing me much confusion.

1. Not all fields are valid for selection.
   1. For example the field "StreamHash" would result in the error "The field or property 'StreamHash' does not exist.".
   2. Being "Hidden" is not the reason. The selectable field "FileRef" is also hidden.
2. There is no notion on whether a field is expandable.
   1. It's not denoted by 'odata.type': 'SP.FieldLookup'.
   2. Expandable fields are only discoverd by attempting to select them and getting error along the lines of "The query to field 'Author' is not valid. The $select query string must specify the target fields and the $expand query string must contains Author."
   3. The only discovered expandable fields are of 'odata.type': 'SP.FieldUser'. These are "Author", "Editor" and "CheckoutUser".
3. The fileds list does not include the field "FieldValuesAsText" but it is valid for expansion and selection.
   1. How is this discovered? What other fields of this nature are there?
   2. It actually does not include all fields. For example, the selectable field "FileDirRef" is not included.
   3. Cannot be used in combination with other expandable fields.

   await items
     .expand(...[
       "FieldValuesAsText",
       "Author",
     ])
     .select(...[
       "FieldValuesAsText",
       "Author/Title",
     ])
     (),
   

   Would error with "Unknown Error".

The microsoft documentations on using OData query does not provide any insight in to discovering valid fields.

[gomain]

Here's a script I wrote to inspect each field

const allFields: Array<string> = await documents.fields
  .select("EntityPropertyName")
  ()
  .then(
    fields => fields
      .map(({ EntityPropertyName }) => EntityPropertyName),
  );

let validFields: Array<string> = [];
let expandableFields: Array<string> = [];
let invalidFields: Array<string> = [];
for (const field of allFields) {
  try { // try to select it
    await documents.items.select(field)();
    validFields.push(field);
  } catch (error) {
    // get error message
    const message = await (<HttpRequestError>error).response.json()
      .then(data => data['odata.error'].message.value);
    if (/is not valid\. The \$select query string must specify the target fields and the \$expand query string must contains/.test(message)) {
      expandableFields.push(field);
    } else {
      // all other errors are "not exists"
      invalidFields.push(field);
    }
  }
}
/*
validFields: [
  'ContentTypeId',
  'Created',
  'OData__ModerationComments',
  'FileLeafRef',
  'Modified_x0020_By',
  'Created_x0020_By',
  'File_x0020_Type',
  'HTML_x0020_File_x0020_Type',
  'OData__SourceUrl',
  'OData__SharedFileIndex',
  'ComplianceAssetId',
  'Title',
  'TemplateUrl',
  'xd_ProgID',
  'xd_Signature',
  'OData__ShortcutUrl',
  'OData__ShortcutSiteId',
  'OData__ShortcutWebId',
  'OData__ShortcutUniqueId',
  'OData__ExtendedDescription',
  'TriggerFlowInfo',
  'ID',
  'ContentType',
  'Modified',
  'OData__HasCopyDestinations',
  'OData__CopySource',
  'OData__ModerationStatus',
  'FileRef',
  'FileDirRef',
  'Last_x0020_Modified',
  'Created_x0020_Date',
  'FSObjType',
  'PermMask',
  'PrincipalCount',
  'UniqueId',
  'OData__CheckinComment',
  'LinkCheckedOutTitle',
  'OData__EditMenuTableStart',
  'OData__EditMenuTableStart2',
  'OData__EditMenuTableEnd',
  'LinkFilenameNoMenu',
  'LinkFilename',
  'LinkFilename2',
  'DocIcon',
  'ServerUrl',
  'EncodedAbsUrl',
  'BaseName',
  'FileSizeDisplay',
  'OData__Level',
  'OData__IsCurrentVersion',
  'OData__IsRecord',
  'OData__DisplayName',
  'SelectTitle',
  'SelectFilename',
  'Edit',
  'owshiddenversion',
  'OData__UIVersion',
  'OData__UIVersionString',
  'InstanceID',
  'Order',
  'GUID',
  'WorkflowVersion',
  'WorkflowInstanceID',
  'Combine',
  'RepairDocument',
  'PolicyDisabledUICapabilities',
]
*/

/*
expandableFields: [
  'Author',
  'Editor',
  'CheckoutUser',
]
*/

/*
invalidFields: [
  'File_x0020_Size',
  'SortBehavior',
  'CheckedOutUserId',
  'IsCheckedoutToLocal',
  'ParentUniqueId',
  'SyncClientId',
  'ProgId',
  'ScopeId',
  'VirusStatus',
  'CheckedOutTitle',
  'MetaInfo',
  'ItemChildCount',
  'FolderChildCount',
  'Restricted',
  'OriginatorId',
  'NoExecute',
  'ContentVersion',
  'OData__ComplianceFlags',
  'OData__ComplianceTag',
  'OData__ComplianceTagWrittenTime',
  'OData__ComplianceTagUserId',
  'BSN',
  'OData__ListSchemaVersion',
  'OData__Dirty',
  'OData__Parsable',
  'OData__StubFile',
  'OData__HasEncryptedContent',
  'OData__HasUserDefinedProtection',
  'AccessPolicy',
  'OData__VirusStatus',
  'OData__VirusVendorID',
  'OData__VirusInfo',
  'OData__CommentFlags',
  'OData__CommentCount',
  'OData__LikeCount',
  'OData__RmsTemplateId',
  'OData__IpLabelId',
  'OData__IpLabelAssignmentMethod',
  'A2ODMountCount',
  'OData__ExpirationDate',
  'OData__IpLabelHash',
  'OData__IpLabelPromotionCtagVersion',
  'AppAuthor',
  'AppEditor',
  'SMTotalSize',
  'SMLastModifiedDate',
  'SMTotalFileStreamSize',
  'SMTotalFileCount',
  'ParentVersionString',
  'ParentLeafName',
  'DocConcurrencyNumber',
  'StreamHash',
]
*/

[juliemturner]

All I can tell you is welcome to the SharePoint REST api's... they're really confusing, inconsistent, and not well documented.

[patrick-rodgers]

There is not so far as we are aware a way to discover what fields are expandable. Generally, these would be fields that have object based values where you could select the child fields.

Unsure if there is more to consider here?

[patrick-rodgers]

Closing this issue as answered. If you have additional questions or we did not answer your question, please open a new issue, ref this issue, and provide any additional details available. Thank you!

[github-actions[bot]]

This issue is locked for inactivity or age. If you have a related issue please open a new issue and reference this one. Closed issues are not tracked.