Geeks With Blogs
Thorvald Bøe
When working with SharePoint and REST, you will sometimes need to update list items that contains lookup columns.

This is quite straightforward as long as you know how the lookup value is represented in the json string. However, the problem occured because I had worked with lookup columns just many enough times to not give much thought to the process of creating them, and just too few times to have experienced this situation before.

The thing is that when retrieving list items with lookup values in REST, the lookup value will be represented by a field name and a value. The field name will be the column plus an 'Id' postfix, the value will be an integer matching the Id of the referenced value. For example:

The list "Order" contains a title and a lookup to list "Customer" like this:
'Title': single line of text
'Customer': lookup to customer list
In which case the returned json will contain a 'Title' field and a 'CustomerId' field, where the CustomerId will point to the SharePoint Id of the referenced customer.

This is fine, and I knew this. The problem came when I was going to update the item. I got this strange error message:
'PrimitiveValue' node with non-null value was found when trying to read the value of a navigation property;

I had no problems adding a list item without the lookup value, so the problem had to be connected somehow to the lookup field. It should not be any magic, I was simply adding the field to my json string with a simple integer value like this: 

,'CustomerId': 4 //hard coded for simplicity - even that failed

After a long and fruitless session of googling, debugging and fiddler'ing, I tried to do a GET request for the list items of the same list, just to see if the lookup fields were somehow represented in a different, mysterious way. That gave me the following:

CustomerIdId=4

Which of course instantly spiked a flash of insight to my exhausted brain: In the routinely manner that the creation of new columns has become, I had inadvertently given the customer column the name "CustomerId". When transformed to json, an extra 'Id' postfix was added.

I immediately changed the column name to "Customer", and everything went fine.

Had I spelled the column name wrongly in a different way, e.g. "NonExistingColumn", I would have gotten the more useful 

"The property 'NonExistingColumn' does not exist on type 'SP.Data.OrdersListItem'"

But for some reason, SharePoint got confused when supplying a column name that was somehow known, thus the strange error message.

Lesson to be learned: If you do something often enough, you will sooner or later get sloppy and mess it up! :-)
Posted on Tuesday, March 11, 2014 4:12 PM sharepoint , REST | Back to top


Comments on this post: SharePoint REST - adding list items with lookup columns

# re: SharePoint REST - adding list items with lookup columns
Requesting Gravatar...
Thanks a lot!
Left by Sébastien Sougnez on Jun 22, 2015 7:58 PM

# re: SharePoint REST - adding list items with lookup columns
Requesting Gravatar...
Thanks so much! - this has probally saved me hours of debugging.
Left by Adam Larner on Nov 20, 2015 10:25 AM

# re: SharePoint REST - adding list items with lookup columns
Requesting Gravatar...
Thank you! it is working fine! god bless you :)
Left by Bishwajit on May 18, 2017 4:33 PM

# re: SharePoint REST - adding list items with lookup columns
Requesting Gravatar...
Thank you so much for your article. Its really help me lot.. after spent one day got this solution and my issue is resolved.

Thank you so much :-)
Left by Dhirendra Kumar on Jul 04, 2017 9:31 AM

# re: SharePoint REST - adding list items with lookup columns
Requesting Gravatar...
Glad to hear. Cheers! :)
Left by Thorvald on Jul 04, 2017 10:53 AM

# re: SharePoint REST - adding list items with lookup columns
Requesting Gravatar...
You saved my days!
Left by akkbright on Nov 08, 2017 2:13 PM

Your comment:
 (will show your gravatar)


Copyright © Thorvald Bøe | Powered by: GeeksWithBlogs.net