Manipulating SharePoint External Lists using REST

In this third post on accessing SharePoint External Lists we are going to be manipulating sharePoint external lists using REST APIs. You can view the previous two posts of this series by clicking on one of the following links:

Of all the methods of coding against external lists I would have to say that REST is probably the easiest. You don’t have to create any complicated CAML queries to find items to read, delete or update as you would with CSOM. You don’t have to add Service Context scope as you do with PowerShell. In fact, with REST you can actually access data using the ID of the list item.

“But Dave, I thought you said external lists don’t have IDs”

Actually, they do. However, as I said in the first post, the do not have Numerical IDs. The IDs are actual text based. Once fantastic about SharePoint REST API is you actually have a method you can call named: GetItemByStringId. This method is exactly as it sounds. Using it you will be able to get the internal ID of the list item generated when the item is added into the external list.

Unlike my previous posts I am not going to show you how to access internal lists and then compare the external access. There isn’t a lot of code to write for this as I just want to give you the basics and Fiddler allows for that perfectly. There is a great post on how to work with internal lists on the Office Dev Center. You can access that here.

Manipulating SharePoint External Lists using REST

I decided that because I had done my previous posts using SharePoint on-prem that this time to show it doesn’t matter which environment you develop against, I am actually updating lists in my SharePoint Online tenant.

Just a quick disclaimer: You will notice that all of my Fiddler screenshots contain a cookie entry in the Request Header. This is simply for accessing SharePoint Online via Fiddler. It is not required for SharePoint on-premises.

For the purpose of this post I will be working with the following list:

Note I have included the BDC Identity column. For external lists, this is the item id.

Read Items

To read an item from the list we will use the endpoint: “_api/Web/Lists/getbytitle(‘<ListName>’)/GetItemByStringId(‘<item id>’)” Special note: when using the BDC Identity, ensure it starts with a double underscore.

Insert New Items

To insert data into an external list is actually exactly the same as it is for an internal list. The endpoint: “_api/web/lists/getbytitle(‘SmallAsset’)/Items” is used here. Having data in the Request Body (seen in the picture below) is what tells SharePoint to insert the data.

You can see that line 10 was added to the list:

Update List Item

As discussed above, getting an item from an external list is a bit different. With external lists you have to use GetByStringID instead of simply placing the item id after the indicating you are accessing items from a list in the REST call. The call to the endpoint is similar to: “_api/Web/Lists/getbytitle(‘<List Title>’)/GetItemByStringId(‘<BDC Identity>’)“. In the following I want to update AssetID#3 and set the location to Regina and the Details to “Updated with Rest”.

You can see that the list item updated successfully.

Delete List Item

Deleting a list item is very similar to updating an existing item. You simply need to change the http method to “DELETE” and remove the request body. The call to the endpoint will be the same as the Update one in fact (“_api/Web/Lists/getbytitle(‘<List Title>’)/GetItemByStringId(‘<BDC Identity>’)“). You can see in following screenshots that I deleted the entry that indicated it was added by Fiddler (AssetID: #9).

You can see the item is now removed

That’s the basics of working with External Lists and REST APIs. As you can see it’s really easy. Each of the methods I illustrated today could be moved to on-prem SharePoint simply by changing the URL and nothing else.

Thanks for reading!!

Manipulating SharePoint External Lists using REST