SharePoint REST – Get Fields whose Name starts with a Number or Special Character

Premise

I had a SharePoint hosted app. In this app, I was querying a List’s item using SharePoint REST API.

Problem

While I had no issues querying items based on a specific query, surprisingly, for certain columns, I was not getting any values. Following is my code snippet that was trying out initially:

executor.executeAsync({
 url: "<app web url>/_api/SP.AppContextSite(@target)/web/lists(guid'47f89cf2-359d-4792-9dc4-8da877a5050a')
 /getitems
 ?$select=_x0031_23_x0020__x002d__x0020_Nu,LinkTitle&@target='<host web url>'",
 method: "POST",
 body: "{ 'query' : {'__metadata': { 'type': 'SP.CamlQuery' }, "ViewXml": "<View><Query><Where><Contains><FieldRef Name='Status'/><Value Type='CHOICE'>in progress</Value></Contains></Where></Query></View>" } }",
 headers: {
 "accept": "application/json; odata=verbose",
 "content-type": "application/json; odata=verbose"
 },
 success: successHandler,
 error: errorHandler
});

Following were the fields I was trying to query above:

  1. 123 – Number [_x0031_23_x0020__x002d__x0020_Nu]
  2. Title [LinkTitle]

While I was able to fetch items for the ‘Title’ column, no value was being returned for the first column!

Solution

The issue was that the first column’s internal was beginning with a hex code. This particular issue holds true for any column whose name begins with either a special character or a number. For ex,

  • Column with name, Number – 123 was working fine however,
  • column with name, 123 – Number was not working.

Now that the issue has been identified, so let’s solve it. 🙂

In SharePoint, if we query fields beginning with a hex code then, we need to prepend, OData_, to the internal name of the field. So now, my url becomes,

 <app web url>/_api/SP.AppContextSite(@target)/web/lists(guid'47f89cf2-359d-4792-9dc4-8da877a5050a')
 /getitems
 ?$select=OData__x0031_23_x0020__x002d__x0020_Nu,LinkTitle&@target='<host web url>'

Look at the Select System Query Option ($select) for the difference,

$select=_x0031_23_x0020__x002d__x0020_Nu,LinkTitle

became

$select=OData__x0031_23_x0020__x002d__x0020_Nu,LinkTitle

That’s it! Now I can also fetch items from fields whose internal name was beginning with a hex code. 😎

3 thoughts on “SharePoint REST – Get Fields whose Name starts with a Number or Special Character

  1. One follow-up to this: If the field name starts with the underscore character (Category) then you would use OData__Category. You would use 2 underscores after the word OData. Also, notice that you would not be allowed to encode anything. I tried using _x002d in place of one or both underscores and it does not work.

    Like

    • SharePoint fields can be queried using their internal names only. So, even if the display name is, _Category, I suggest you first check it’s internal name, which, I am guessing might be, _x002d_Category but, you should confirm it once. Now that you know the internal name, just prepend it with OData_.

      Also, encoding of _ is _x002d_. Note the trailing underscore at the end. However, you don’t need to guess the encoding/name as its already available.

      There are many ways by which you can check a field’s internal name. However, the easiest being, go to List Settings > Right Click on your target field name and select Copy Link/Copy Link Address > Paste it in your notepad. At the very end after the phrase Field=, is your field’s unique internal name.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s