Creating List Items in an External List with SharePoint Designer Workflows

One very useful feature in SharePoint Designer (SPD) Workflows is the ability to interact with External Lists, thanks to the new Business Connectivity Services. This primarily allows you to create and update items through workflow actions. You will need to make sure that your External List’s Content Type has at least Get Item and Create Item methods defined. Let’s go through how to build a workflow action that creates a new item in an External List.

1. Open up SharePoint Designer 2010 and connect to your site.

2. Click “Workflows” on the side menu
clip_image001

3. Let’s create a List Workflow, so select “List Workflow” on the ribbon and choose the list that this workflow will associate with.
clip_image002

4. Give the workflow a name and description then click Ok.
clip_image004

5. Click inside “Step 1” and click the “Action” button on the ribbon. Choose “Create List Item”
clip_image006

6. A new workflow action will have been created inside “Step 1”. Click the hyperlink “this list” to choose the external list to create the new item in.
clip_image007

7. When the dialog opens, choose your External List in the “List” drop down. Click the “Add…” button and start mapping fields from your External List to workflow properties or variables.
clip_image009

8. Click OK when you’re done. Step 1 in the workflow should look like the below image
clip_image010

9. Add any additional actions to your workflow then save and publish it to the list.
clip_image011

10. Go back to your SharePoint list that’s associated to workflow we just created. Create a new item and manually trigger the workflow. When the Create Item action is executed, a new entry should be created in your External List.

Additional References:
Using SharePoint workflows with Business Connectivity Services (BCS) by JD Klaka

The workflow could not update the item in the external data source. Make sure the user has permissions to access the external data source and update items.

If you have just created an External List and tried to update an item through a SPD Workflow action, you may have received the error below in the History List when the workflow action executes:

The workflow could not update the item in the external data source. Make sure the user has permissions to access the external data source and update items.

In your process of narrowing down this issue, you may go straight to the External list and try to create and item directly. To your surprise, the action completes without error. …

There is a very good chance that this error is related to the security configuration and connection properties for the External Content Type. In the example below, the External Content Type interacts with a WCF service. Let’s look into why this error has surfaced and how to go about resolving it.

By default, you will get the following connection properties when creating a link to external content (WCF Service).

clip_image002

If you configure your web service to use integration authentication (Negotiate\Kerberos) or anonymous access, this should generally be sufficient for interacting with the external list through the interface. However, when you try to write back to the external list through a workflow, you will get the error message above. This is because workflows will always run as the service account (generally the IIS application pool account) when accessing content via Business Connectivity Services. Due to this, workflows that interact with External Content (via BCS) only support using the Secure Store Service or RevertToSelf (not enabled by default due to the security implications) to help protect the external system. This is by design. For a more detailed explanation about this, please check out Using SharePoint workflows with Business Connectivity Services (BCS) by JD Klaka.

The error message above is actually thrown by Business Connectivity Services and not your external content source. If you look at the logs from your external content source (WCF service in my case), you will notice that BCS doesn’t even attempt to connect. If you also look at the logs in the 14 hive, you will see an “Access Denied” error thrown by BCS for the service account the workflow is running as.

The way I solved this error was to configure an application in my Secure Store Service and grant the service account permissions in the External Content Type. To create an application in your Secure Store Service, you will need to have access to central administration and the right permissions to manage the Service. Here are the steps I went through to create application in the Secure Store Service. For more information, refer to MSDN – Configure the Secure Store Service.

1. On the Manage Services page in Central Admin, select the Secure Store Service then click the “Manage” button on the ribbon.
clip_image003

2. Click the “New” on the ribbon to create a new application
clip_image004

3. Enter and ID for the Application and Display name. Make sure you choose “Group” for the Target Application Type. Click “Next”.
clip_image006

4. Accept the default and click next on the following page.
clip_image008

5. Specify an administration account and put the service account that the workflow will run as in the members section. Click Ok.
clip_image010
Note: You may wish to create a Security Group in Active Directory that contains all the users that will be allowed access to this external content. This will make administration easier as you can also use this group to grant appropriate roles in the External Content Type’s permissions. If you try to access external content and you’re not in the Members section of the Secure Store Service Application, you will get a “Connection manager did not return valid connection” message.

Now that we have created an application in the Secure Store Service, we will need to configure the connection properties for the External Content Type.

1. Go to SharePoint Designer and connect to your site. Choose the External Content Types Site Object and open your External Content Type. Click “Edit Connection Properties” in the ribbon:
clip_image011

2. In the Endpoint Properties, change the Authentication Mode to be one of the Impersonate options (depending on your requirements). Also choose the appropriate Impersonal Level for your application. Set the Secure Store Application ID to be the ID of the Secure Store Application we created above.
clip_image012

3. In the Metadata Properties, change the Authentication Mode to be one of the Impersonate options (depending on your requirements). Set the Secure Store Application ID to be the ID of the Secure Store Application we created above.
clip_image013

4. Click OK and run your workflow again. The permissions error should have disappeared.

You may also need to ensure the service account that the workflow is running under has permission in the External Content Type. You can view the permissions in SharePoint Designer. However, if you wish to change them, you will have to do this via Central Administration. For more information about setting these permissions, see the Manage External Systems TechNet article.

Defining Custom Columns in SharePoint – Field Types and Options

A common task for SharePoint developers is defining Site Columns (or Fields) as part of a Feature. When defining a field, you need to assign the field a type. I have created this reference table below to help you map between the the “Create Site Column” SharePoint page to the attributes you would set when defining the field as part of a feature.

Defining Columns using the UI


Create Site Column Page

Defining Columns in an Elements file as part of a feature
  1: <Field ID="{CCF0A1BF-4823-459c-9773-73890231E070}"
  2:        Name="ActiveProject"
  3:        DisplayName="Is Project Active"
  4:        Group="Litware"
  5:        Type="Boolean"/>

 

Type (as shown on the Create Column page)

Field Type

Notes

MSDN Links

Single line of text

Type=”Text”

 

Microsoft.Sharepoint.SPFieldText

Multiple lines of text

Type=”Note”

In the Create Column page, can pick the type of text to allow. To enable the different sub-types, you need to set a couple of other attributes on the field element.

Plain Text
RichText=”FALSE”  (default value)

Rich Text
RichText=”TRUE”
RichTextMode=”Compatible”  (default value)

Enhanced Text
RichText=”TRUE”
RichTextMode=”FullHtml”

In addition, you can also set the number of lines to display using the NumLine attribute.

Further Information on MSDN:

Microsoft.Sharepoint.SPFieldMultiLineText

Choice (menu to choose from)

Single Choice
Type=”Choice”

Multi-Choice
Type=”MultiChoice"

Pick the display format for the Choice and Multi-Choice types, respectively:

Drop-Down Menu or Combo Box
Format=”Dropdowns”

Radio Buttons or Check Boxes
Format=”RadioButtons”

Define the options a user can pick from using the Choices element. Below is a skeleton to explain this.

<Field Name=”MyOptions” Type=……>
  <CHOICES>
     <CHOICE>Option 1</CHOICE>
     <CHOICE>Option 2</CHOICE>
  </CHOICES>
</Field>

If you would like to give the user an option to add their own value, set the attribute FillInChoice=”TRUE”.

Microsoft.Sharepoint.SPFieldChoice
Microsoft.Sharepoint.SPFieldMultiChoice

Number

Type=”Integer”

This field type also lets you define:

Minimum Value (0 for example)
Min=”0”

Maximum Value (100 for example)
Max=”100”

Decimal Places (Default is Automatic, example 2 decimal places)
Decimals=”2”

Show as Percentage (Default is False)
Percentage=”FALSE”

Microsoft.SharePoint.SPFieldNumber

Currency

Type=”Currency”

This field type also lets you define:

Minimum Value (0 for example)
Min=”0”

Maximum Value (100 for example)
Max=”100”

Decimal Places (Default is Automatic, example 2 decimal places)
Decimals=”2”

Currency Format
This sets the display format of the currency.

LCID=”3081”

3081 sets the format English – Australian. For a complete list of Locales, see the MSDN link in the next column.

Microsoft.Sharepoint.SPFieldCurrency

Locale IDs

Date and Time

Type=”DateTime”

This field also lets you define:

Date and Time Format
Show the date only:
Format=”DateOnly”

Show the date and time:
Format=”DateTime”

Microsoft.Sharepoint.SPFieldDateTime

Yes/No

Type=”Boolean”

When setting the default value, you need to use the binary values:

No/False = 0
Yes/True = 1

Microsoft.Sharepoint.SPFieldBoolean

Person or Group

Single User or Group
Type=”User”

Multiple Users or Groups
Type=”MultiUser”

This field also lets you define:

Allow multiple selections
Set the Type to MultUser and the attribute Mult=”TRUE”

Allow selection of
People Only
UserSelectionMode=”PeopleOnly”

People and Groups
UserSelectionMode=”PeopleAndGroups”

Choose from
If you want to limit the option list to a SharePoint Group, use the UserSelectionScope attribute. Set it to the ID of the SharePoint group (Integer value). For example, UserSelectionScope=”3”.

Show field
Set the name of the field from the User’s profile properties that you want to display. For example, show the user’s name property:
ShowField=”Name”

If you would also like to show presence (Office Communicator integration required):
Presence=”TRUE”

Microsoft.Sharepoint.SPFieldUser

Hyperlink or Picture

Type=”URL”

You can display the URL as a Hyperlink or Picture. Use the Format attribute to pick which one:

Hyperlink
Format=”Hyperlink”

Picture
Format=”Image”

Microsoft.Sharepoint.SPFieldUrl

 

Hope this helps you when defining site columns/field.

And one last thing, please remember to also define a group to your organise your fields using the Group=”My Columns” attribute!

Other references:

- Ton Stegeman’s Blog

Connecting to SharePoint 2007 Web Services using a Service Reference in Visual Studio 2008

When using Service References to consume SharePoint web services, there are a few additional configuration changes that you need to make. The service model configuration that Visual Studio generates is not sufficient when consuming SharePoint web services. This is mainly what we will cover in this post.

You may have noticed in Visual Studio 2008 that Web References are no longer the default option when you wish to reference a Web Service. Instead, you now have an option called “Add Service Reference….”

So what’s the difference between a Web Reference and a Service Reference? Well basically, a Web Reference is a wrapper over wsdl.exe and a Service Reference is a wrapper over svcutil.exe. Service references also have some additional configuration options and are a good choice when planning to consume WCF services.

1. First step, create a new project in Visual Studio. I have created a new Windows Forms Application and targeted it at .NET 3.5.

2. Right click on “References” in the solution explorer and select “Add Service Reference”

3. I am going to connect to the Lists service on my local SharePoint instance. My web application URL is http://localhost:120/ so the Address for Lists Web Service will be http://localhost:120/_vti_bin/Lists.asmx

Enter the URL in the Address section and click Go. Make sure you give the service reference a Namespace. I have used “ListReference” for this demo.

4. Next, let’s create a button and combo box on the form. We will use the button to execute a method that gets a list of SharePoint lists on the site and display them in the combo box.

5. Double click the button and we will add some code to the “click” event to invoke the web service method and get the list of Lists.

  1: private void button1_Click(object sender, EventArgs e)
  2: {
  3:     ListsSoapClient client = null;
  4:
  5:     try
  6:     {
  7:         client = new ListsSoapClient();
  8:
  9:         // Open the connection to the web service
 10:         client.Open();
 11:
 12:         // Write some code to query the web service and get a list of 
 13:         // Lists.
 14:
 15:
 16:     }
 17:     catch (Exception ex)
 18:     {
 19:         MessageBox.Show(ex.Message, "An error has occured", MessageBoxButtons.OK, MessageBoxIcon.Error);
 20:     }
 21:     finally
 22:     {
 23:         client.Close();
 24:     }
 25: }

6. Next things get interesting. We will need to make some changes to the app.config file (or web.config if you in Web Application) as the automatically generated service binding configuration doesn’t work for a SharePoint service. The main changes we will make are around security as we need to enable impersonation and ensure the connection is made using Windows Authentication.

First change we need to make is in the binding configuration security element. The automatically generated code produces the following configuration settings:

  1: <security mode="None">
  2:      <transport clientCredentialType="None" proxyCredentialType="None" realm="" />
  3:      <message clientCredentialType="UserName" algorithmSuite="Default" />
  4: </security>

we will need to change it to the following:

  1: <security mode="TransportCredentialOnly">
  2:   <transport clientCredentialType="Ntlm" proxyCredentialType="Ntlm"/>
  3: </security>

Next, we will need to add a service behaviour. By default, no service behaviours are generated. This behaviour will enable impersonation.

  1: <behaviors>
  2:   <endpointBehaviors>
  3:     <behavior name="ListServiceBehavior">
  4:       <clientCredentials>
  5:         <windows allowedImpersonationLevel="Impersonation"/>
  6:       </clientCredentials>
  7:     </behavior>
  8:   </endpointBehaviors>
  9: </behaviors>

And add a reference to this Behaviour in the endpoint configuration:

  1: <endpoint address="http://localhost:120/_vti_bin/Lists.asmx"
  2:     binding="basicHttpBinding" bindingConfiguration="ListsSoap"
  3:     contract="ListReference.ListsSoap" name="ListsSoap"
  4:     behaviorConfiguration="ListsServiceBehavior"/>

OK. That’s the service binding configuration sorted. Now back to our code….

7.  In the button click method, add the following code in the try{..} block after we open the connection to the service.

  1: var response = client.GetListCollection();
  2: foreach (XmlNode listNode in response.ChildNodes)
  3: {
  4:     comboBox1.Items.Add(listNode.Attributes.GetNamedItem("Title").Value);
  5: }

And there you have it. Run your application, click the button and you should get a list of Lists!

I have packaged this demo application up in a zip file.

http://cid-96d0e96711d2a69e.skydrive.live.com/embedrowdetail.aspx/Public/SPListServiceDemo.zip

A few extra notes……

  • You can still create Web References in Visual Studio 2008. This can be done using the “Advanced” button on the Create Service Reference dialog box.
  • If you want to make the URL to your web service configurable, you just need to change the line that creates the client.
  1: EndpointAddress address = new EndpointAddress("http://sharepointsite/_vti_bin/Lists.asmx");
  2: client = new ListsSoapClient("ListsSoap",address);

Thanks also to Buddhike de Silva for his help with this Service Reference.

Follow

Get every new post delivered to your Inbox.