Workflow to read data from external list

Question

I have just recently started working with external lists using BDC and was quite surprised to discover that I can’t really interact with the data in any way using SharePoint 2013 workflows (Even just read from it). Or so it seems at least, but I hope you might be able to help out.

I would expect to be able to use the REST endpoint for lists to be able to query the external list, but no matter what I do I get Unauthorized as response.

What I have done so far:

• Made sure all users have access to the external content type
• Made sure all users have read access to the external list
• Made sure all user have access to the secure store service
• Created a user with read privileges to the SQL database (The source of the external list) and set it in the secure store as a group login

What I can do so far:

• See the entire external list in my browser
• Query the external list using U2U CAML Query Builder
• Call the lists(guid’[guid of the external list]’)/Items endpoint using the browser and get the expected xml response
• Call the lists(guid’[guid of the external list]’) endpoint using a workflow and get the expected json response

What I cannot do yet:

• Use the query build by the U2U tool. I get Unauthorized as response
• Call the lists(guid’[guid of the external list]’)/Items endpoint using a workflow. I get Unauthorized as response

In essence I need a way – using SharePoint 2013 workflows – to pull the value from a field in an item in the external list where the ID match my query.

It would seem that my workflow is simply missing permissions to execute the query, but I cannot for the life of me figure out what I might have overlooked.

I realize SharePoint 2010 workflows can pull the data without using the REST calls, but that’s not an option in our current environment.

From the ULS logs I get this:

System.UnauthorizedAccessException: Apps cannot use External Content Types defined in the Business Data Connectivity Shared Service Application metadata catalog. Apps should package any External Content Type definitions in a BDC model file which is packaged and deployed as part of the SharePoint App.

Sadly that doesn't really help me much.

Answer 1

first the short answere if you are using SP Designer 2013: Accessing data on external lists with SP Workflow Engine 2013 ist not supported in SharePoint 2013 (with the SharePoint Designer 2013).

https://support.office.com/en-us/article/SharePoint-Server-2013-known-issues-780242a6-236e-4d7a-8379-118a1aaa2a51?CorrelationId=fd9cf927-1a2c-493c-9556-6f470806f92c&ui=en-US&rs=en-US&ad=US#_SharePoint_Designer

A work around would be to use a SP 2010 Workflow as mentioned.

Here is an example how to use a workflow 2010 with a external list: http://blogs.msdn.com/b/bcs/archive/2010/01/20/using-sharepoint-workflows-with-business-connectivity-services-bcs.aspx

However, if you still want to use a 2013 workflow then this has to be inside of an app. But you are limited to external content types which consume OData APIs. From the SharePoint Designer you can consume only webservices (WCF), databases or .NET Framework Connectivity Assembly but not OData APIs. So you see the problem? If you want to access external lists with a workflow you could try to package the content types, lists and workflows into that app. Before you need to have either a OData API or you need to write one. Depending on you solution this can be easy or not.

To give you an idea for using SQL Server and OData-API: https://msdn.microsoft.com/en-us/data/gg192995.aspx

Answer 2

The external lists should still expose REST endpoints. I would suggest checking out SharePoint 2013 Workflow and the new HTTP Actions to perform REST calls, if you are not using it already.

For example: https://msdn.microsoft.com/en-us/library/office/jj822159.aspx

I have successfully read data from an external list with this method. ( If you already have tried that method, remember to add an empty Authorization "" in the request header for the HTTP Action to avoid the "Not authorized" in addition to Accept | Application/json;odata=verbose and Content-Type | Application/json;odata=verbose.

My scenario might differs though, but I think it can be worth while to check it out. HTTP Actions requires some configurations steps before you can use it in SP Designer 2013.