DEV Community

loading...
Cover image for Lookup field not returned in SharePoint REST

Lookup field not returned in SharePoint REST

Ole Kristian Mørch-Storstein
Engineer. Developer. Dad. Robot rights activist and occasional vampire hunter.
・3 min read

Recently I reviewed a strange issue where a SharePoint document library had a Person/User field "Originator" which was impossible to access through REST.

TLDR

if you add a person/user/lookup field named Originator, you there will be no name collision when creating the field since there are no other field with the same internal name, but accessing this field through REST cannot be done since SP REST will have a name collision with an internal field due to the <internal name>Id convention used by the API.

Details

Here is my simple test case, a list with only two manually configured user (person) fields Orignator as well as AnotherUserField. The same user has been added both places

Alt Text

First a bit of background. I assume the original field was created through the UI, with the title Originator. This means the internal name aka static name for this field would be the same, i.e. Originator. This does not conflict with any OOB fields, so creating it is straight forward.

As this was a user field, its internally handled like any other lookup field. What this means is that the field is returned as OriginatorId when you do a rest query. Well, that's the theory anyway. Lets have a look

Alt Text

I can find my item title as well as AnotherUserField which here is returned as AnotherUserFieldId as expected. But no OriginatorId. On a whim we pulled out the SchemaXml for the list, like so (my test list is aptly called 'test')

(Get-PnPList -Identity test -Includes SchemaXml).SchemaXml | clip
Enter fullscreen mode Exit fullscreen mode

If we search through that text (preferably pretty printed) we find our Originator field

    <Field Type="User" DisplayName="Originator" List="UserInfo" Required="FALSE" EnforceUniqueValues="FALSE" ShowField="ImnName" UserSelectionMode="PeopleOnly" UserSelectionScope="0" ID="{ef9f5a10-56dd-41e8-9e22-bfd095095ae0}" SourceID="{4fb24039-a619-44e7-a2cf-2be13e015f4b}" StaticName="Originator" Name="Originator" ColName="int4" RowOrdinal="0"/>
Enter fullscreen mode Exit fullscreen mode

But searching further for the word Originator we found this internal hidden field which is added automatically to any list and required by some internal functionality in SPO, thus impossible to delete

<Field ID="{14ee99cd-bed9-474a-bf99-8f753fbad6b4}" Name="OriginatorId" DisplaceOnUpgrade="TRUE" Hidden="TRUE" ReadOnly="TRUE" ShowInFileDlg="FALSE" Type="Lookup" DisplayName="Originator Id" List="Docs" FieldRef="ID" ShowField="OriginatorId" JoinColName="DoclibRowId" JoinRowOrdinal="0" JoinType="INNER" SchemaVersion="16.0.77.0" RecreateIfMissing="TRUE" SourceID="http://schemas.microsoft.com/sharepoint/v3" StaticName="OriginatorId" FromBaseType="TRUE"/>
Enter fullscreen mode Exit fullscreen mode

This means, if you add a person/user/lookup field named Originator, you there will be no name collision when creating the field since there are no other field with the same internal name, but accessing this field through REST cannot be done since SP REST will have a name collision with an internal field due to the <internal name>Id convention used by the API.

There's a bunch more of these names that are valid, but should be avoided, here are some which have names that do not start with underscore:

  • Prog
  • Scope
  • Unique
  • ComplianceAsset
  • SyncClient

Conclusion

The simplest way to avoid this issue is to prefix internal names of your field. E.g. could the Originator field have been created like this

    <Field Type="User" DisplayName="Originator" List="UserInfo" Required="FALSE" EnforceUniqueValues="FALSE" ShowField="ImnName" UserSelectionMode="PeopleOnly" UserSelectionScope="0" ID="{ef9f5a10-56dd-41e8-9e22-bfd095095ae0}" SourceID="{4fb24039-a619-44e7-a2cf-2be13e015f4b}" StaticName="OKMSOriginator" Name="OKMSOriginator" />
Enter fullscreen mode Exit fullscreen mode

This is, however, something you only would do if you created this field programmatically. Using the UI it is possible to create a field using the internal name you would like it to have, and then rename it, but this is not something regular users would ever do.

Discussion (0)