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:
- 123 – Number [_x0031_23_x0020__x002d__x0020_Nu]
- 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. 😎
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.
LikeLike
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.
LikeLike
Hmm, the website erased the underscore I put in front of Category but I think you get the picture.
LikeLike