Cannot update Sharepoint List Item from External Content Type


  • Hi

    I wonder if anyone else can help?  I have a very simple sharepoint list with employee details in it including a unique employee ID.  I am trying (unsuccessfully) to retrieve a training completed value held in an External Content Type using BCS using workflow.  I have tried dozens of times, colleagues have also tried and even followed countless blogs/tutorials and set up exactly the same way but it doesn't return the value in any way, always 0 and with no workflow errors. I can open the external list no problem in SP and see the data.

    In the workflow I use the Update List Item action and then select my list and the Training feld I want to update.  In the lookups section I choose the External List and then the ID I want to "join" on (in this case EmployeeID), then I click fx and select the employeeID from my SP List, it gives the usual warning about returning multiple rows and when I run the workflow it completes ok but does not update the list item.  I have even tried setting a workflow variable to the field in the external list and this doesn't work either!

    BCS is set up ok and connecting to the SQL Server table as I can see the contents of the external list fine when I open the list in the site.

    Am I doing something wrong or has anyone else experienced this?  Using data from external lists is a fundamental part of BCS but sadly it's not working for me.

    I have replicated the External List as a Custom List in SP and using the same workflow and editing the lookup , it works perfectly!  Go figure!

    Any ideas greatly appreciated.


    • Editado Jon Lawton domingo, 10 de junio de 2012 8:10
    sábado, 09 de junio de 2012 10:50

Todas las respuestas

  • I am assuming you are writing Workflow on the SharePoint list. So, on update list item do not select the list just keep it as current item.

    Click on Add -> Set this field to the field that needs to be updated.

    On fx button on Data Source select the external list.

    On Field from Source select the field from external list that you want to update in the current sharepoint list.

    Under Find the List Item ->

    Enter the field EmployeeID from External List and then the value select the current Item's (SharePoint list) Employee ID.

    Let us know if this works.


    Ameet Phadnis (Alexander Open Systems) Please remember to click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if it was useful.

    domingo, 10 de junio de 2012 16:16
  • Hi thanks for your reply.

    I had already done exactly as you had outlined but it doesnt work.  It's as if it's a bug in the build.  I know the workflow action is working ok because I have created a standard sharepoint list with exactly the same data as the external list and used that to perform the lookup on and it works perfectly.  The workflow is attached to the sharepoint list also.

    I am interested if you could replicate in your environment and let me know.  I originally followed this blog to test and this didn't work either:

    If this doesn't work for anyone else then I reckon there is an issue with SP2010. Could you spare any time in trying out the above blog example?

    Many thanks - Jon

    martes, 12 de junio de 2012 9:11
  • Hi ,

    Use SPD workflow activities like Log to History List, Create List Item, Update List Item to access item in External List. Workflow completes without updating the right External List item since lookup to External List returns no data. This is caused by the following :

    1. External Content Type that External List is based on has Authentication Mode as User's Identity.

    2. Non-unique field is used to lookup to External List.


    I. Set Authentication Mode of External Content Type to be one of following:

       A. Impersonate Windows Identity.

       B. Impersonate Custom Identity.

       C. BDC Identity.

    Workflows accessing Business Data Connectivity (BDC) service always run as a service account, even under the impersonation step. A workflow always runs as a service account and is supported only when using Secure Store Service or RevertToSelf (which is disabled by default because of security implications, see Business Connectivity Services Security Overview for more information

    Secure Store Target Application is used for both A and B.

    Steps to configure Secure Store for External Content Type:

    1. Create Secure Store Service

    a. Central Administration -> Manage Service Applications -> New -> Secure Store Service Application

    b. Once Secure Store Service is created, add an Administrtor Account and give Full Control

    c. Permissions - give Aministrator Account - Full Control

    d. Click into the Secure Store Service Application and Generate New key

    2. . Create Secure Store Target Application

    a. Open Central Administration

    b. Click Manage service applications -> Secure Store Service

    c. Click New to Create New Secure Store Target Application with following properties

    Target Application ID:

    Display Name:

    Contact E-mail:

    Target Application Type: Group

    d. Click Next

    e. At the page to Specify the Credential Fields for your Secure Store Target Application

    -If plan to use Impersonate Windows Identity for ECT, go with default settings:

    Windows User Name and Windows Password.

    -If ECT will use Impersonate Custom Identity, set following

    Custom User Name, Custom Password.

    The account chosen is what users will be mapped to when connecting to External Database.

    f. Click Next

    g.Fill in:

    -Target Application Administrators:


    Note: Members are users whose accounts will be mapped to Credential Fields account above

    Please refer to http://bemis/40/Pages/2502813_en-US.aspx for related information.

    If choose to (C) BDC Identity, you need to set RevertToSelf for BDC application:

       a. RevertToSelf is disabled by default and must be enabled with the following PowerShell script:

                 $bdc = Get-SPServiceApplication | where {$_ -match "Business Data Connectivity Service"};

                 $bdc.RevertToSelfAllowed = True;


       b. Go to the ECT in SPD, Edit Connection Properties and flip the authentication to "BDC Identity".

    II. Use an unique field to lookup to External List. This requires some planning ahead.


    Entan Ming

    TechNet Community Support

    miércoles, 13 de junio de 2012 9:44
  • Thank you Entan for your reply.  Unfortunately I have completely deleted Secure Store and recreated as per your instructions and then recreated ECT in SPD but it still returns 0!

    In my SQL Table the Primary Key value is called EmployeeID and has a value of 10 (int).  In my Sharepoint custom list the Employee ID field is a Number field and the value is also 10.

    The Lookup is using both of these to return the limit of £1000 from the SQL table however, workflow completes and returns 0 instead of £1000.

    In Lists in SP if I click on the External List created from the ECT I can see it no problem.

    Have you ever managed to get this to return anything but 0?  If so maybe I could share your example and recreate it?

    Many thanks again - Jon

    domingo, 17 de junio de 2012 8:54
  • Hello Jon,

    I'm having the same issue. Any luck with getting this working?


    lunes, 05 de noviembre de 2012 15:19