“I’m creating SharePoint items in a dynamic list in my Power Automate flow, but it returns error when the item contains lookup columns.”
“A ‘PrimitiveValue’ node with non-null value was found when trying to read the value of a navigation property; however, a ‘StartArray’ node, a ‘StartObject’ node, or a ‘PrimitiveValue’ node with null value was expected.”
If you’re using Power Automate to create SharePoint items in various lists, you might use an HTTP request to do that. It doesn’t ask you to select a site or a list so you can use variables or configuration lists, but on the other side it doesn’t show the available fields. Which is fine, you just use the column internal name and assign a value. Unless it’s a lookup column. Why doesn’t it just create the item? What’s the meaning of the confusing error message?
What’s the right name for the lookup column?
When you create the item, you define the values as pairs of the column internal name and its value.
{
"__metadata": {
"type": "SP.Data.<ListName>ListItem"
},
"Column1": "Value1",
"Column2": "Value2",
"Column3": "Value3"
}
But for lookup it’s not just the column internal name.
If you enter the combination of the Site Address and the Uri in a web browser, it’ll return an XML with the list items (if there’re any items).
Once you enter it in some code editor, e.g. Visual Studio Code, you’ll see that there’s no property that would have just the lookup column internal name. There’s a property with the <internal name>Id instead.
My column has internal name ‘Lookup’ so the property name is ‘LookupId’. If I called it ‘TomsLookupColumn’ it would be ‘TomsLookupColumnId’.
This is the column name you must use in the HTTP request. You can also see that it contains only the ID of the lookup item, therefore, you should set it just to the id number.
{
"__metadata": {
"type": "SP.Data.<ListName>ListItem"
},
"LookupId": 1
}
Summary
When you use Power Automate to create items in a dynamic list, you’ll have to deal with the special fields, e.g. lookup or people picker. But using the http request directly in a browser can give you a hint – try to call it and check the result. In this example you could clearly see that it’s not a ‘Lookup’ column, it’s ‘LookupId’, in the same way as there’s no ‘Person’ column, but ‘PersonId‘.