Lookup field not returned in SharePoint REST

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

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

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

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"/>

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"/>

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" />

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.