0

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.

0

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

|improve this answer|||||
  • Thank you for the thorough answer. SharePoint never stop to surprise me when it comes to shortcommings and lack of what should have been OOTB features. I guess this mean - as I cannot use 2010 workflows - I cannot get to interact with the data in any easily manner. – Morten K Nov 4 '15 at 11:47
  • There is a possibility. You could use a third party tool named Nintex Workflow. It's a great tool and I used it with many of my customers. But it's not cheap. This one still uses the Workflow 2010 Engine but the UI is rich and fully build with HTML5 and JavaScript and can be accessed with the SP UI. – Sebastian Schütze Nov 4 '15 at 11:54
  • For the setup I'm trying to make I cannot justify to purchase 3rd party software. And we're steering clear of the 2010 workflows as we do not want to develop solutions that might or might not work in the near future :-( – Morten K Nov 4 '15 at 11:56
  • The good thing is that Nintex has a nice export feature which exports their workflows to xml. That means as long as the features exist in future Nintex versions you will be able to import those Nintex Workflows in future versions of Nintex as well. I know that Nintex is migrating their Nintex SP Solutions to SharePoint (Provider hosted) apps and they will provide future solutions as they are the number one 3rd party tool provider for SP and work together with Microsoft. – Sebastian Schütze Nov 4 '15 at 12:07
  • I just made a test on SP 2016 IT Preview and with SP2013 Designer you still can use 2010 Workflows and publish them. They also work since I could start them. So WF 2010 will still be available through SP 2016 I expect. – Sebastian Schütze Nov 4 '15 at 12:11
0

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.