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

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

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

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

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.

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.

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

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

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).

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.

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

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

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

5. Specify an administration account and put the service account that the workflow will run as in the members section. Click Ok.

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:

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.

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.

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.

Problems building Database Project on a TFS 2010 Build Server – Unable to load sqlceme35.dll

Please note, this problem only applies to TFS 2010 Beta 2. The bug has been corrected in later builds.

As part of the current project I am working on, we have a 2008 Database project (Data Dude) in our solution. We found that the project built fine in our local Visual Studio 2010 environment however it would break the build on the build server. The message received was:

C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.TSqlTasks.targets (56): The "SqlBuildTask" task failed unexpectedly. System.DllNotFoundException: Unable to load DLL ‘sqlceme35.dll’: The specified module could not be found. (Exception from HRESULT: 0x8007007E)    at System.Data.SqlServerCe.NativeMethods.DllAddRef()……..

The build server for this project is a Windows Server 2008 R2 x64 virtual machine. After a series of emails, IMs and research with a fellow Readify colleague, Steve Godbold, we managed to discover it was an issue with the database project building on a x64 build server (as it worked correctly on a x86 build server). The issue is related more specifically to the build process not being able to locate the correct version of the sqlceme35.dll when building with a x64 target platform.  So here is the workaround.

In your Team Build definition, set the MSBuild Platform property to X86. You will find this property under Process > 3. Advanced

 

Save and rebuild.

Steve has put some more detail into this issue on his related blog post. Good luck!

More Information

VSTS DB Team Blog – What’s new for Data Dude in VS 2010

TF250025 and TF262600 errors on Team Foundation Server (TFS) 2010 Project Portal Dashboard

I came across these errors when configuring TFS 2010 Beta 2.

TF250025: The following URL does not point to a valid SharePoint site: <URL to your Project Portal> Verify that you have the correct URL and that it points to a SharePoint site.

or

TF262600: This SharePoint site was created using a site definition that requires data from Team Foundation Server. However, the site is not currently associated with data for a team project. To ensure that this site functions correctly, you must configure a team project to refer data for that project to this site.

or

This site doesn’t have a default Team Foundation Server instance. You can explicitly select a Team Foundation Server.

If you receive these errors, then you are most likely trying to access the project portal via a URL that is not recognised by SharePoint. Each URL that you use to access the site must be registered with SharePoint – even if your IIS Web Site is set to accept all host headers on a specified port.

For example, your may have configured your project site’s URL to be http://tfsserver but when you try to access it via http://tfsserver.companydomain.com your get the above errors.

To resolve this issue, you will need to register a new internal URL for the SharePoint site using Alternate Access Mappings. Please follow these steps:

1. Log into the SharePoint Central Administration site (a link can be found under Control Panel > Administrative Tools)
2. Click on the “Operations” tab at the top
3. Under “Global Configuration”, choose “Alternate Access Mappings”
4. On the menu bar, select “Add Internal URLs”
5. Choose the web application that hosts your Project Portals (usually Default Web Site)
6. In the “URL protocol, host and port” text box, enter the alternate URL that can be used to access the Project Portals (must include http:// or https://). Using the example above, this would be “http://tfsserver.companydomain.com”
7. Click Save

Perhaps a good practice would be to use FQNs when referencing servers during TFS configuration.

Hope this helps.

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.  

Microsoft SharePoint Online

This week at PDC, Microsoft SharePoint Online was talked about in the Keynote. To find out more, have a look a the official Microsoft site. Unfortunately trials are only available for people in the U.S – cannot wait for it to get to Australia!

http://www.microsoft.com/online/sharepoint-online.mspx

The SharePoint team have also written a blog about it discussing the direction for developers leveraging SharePoint Online in "the cloud".

http://blogs.msdn.com/sharepoint/archive/2008/10/27/pdc-2008-announcing-azure-services-platform-and-microsoft-sharepoint-services.aspx

Personally, I think this product is going to fantastic for small businesses who wish to use the power of SharePoint however don’t have the capital or infrastructure to host their own dedicated system. Of course, enterprises will also see the 99.9% uptime SLA, integration with their in-house Active Directory and the maintenance plans as attractive offers as well.

The details for the Developer’s experience have not been fully announced. At PDC they did demonstrate developing against the Online Services, which is great for integration with other systems. However it will be interesting to see the possibilities/limitations around other custom development, i.e. web parts.

These are exciting times!

Tools for Live Writer

Today I moved from my WordPress blog to Live Spaces.

 

Omar sent me this link to download 10 great add-ins for Live Writer. If you are a developer and blogging code, I recommend that you install some of these add-ins. They make tasks like formatting code for blog entries, much easier.

 

http://devintelligence.com/blogs/netadventures/archive/2007/10/05/10-plugins-for-windows-live-writer.aspx

Viewing a SharePoint site via Windows Explorer and Network Places

In both SharePoint 2003 and 2007, you are able to connect to a SharePoint site and browse it using Windows Explorer. This is very useful if you need to do things like:

• Take a copy of all the items in a library
• Copy all the documents in a TFS project (site) to migrate to a different TFS server (unusual requirement – but that is why I hunted for this solution)
• Do a bulk upload by “copy and pasting” or “dragging and dropping” (Note: If you have required meta data on items in a document library, for example, this method will not enforce that requirement!)

I came across a couple of issues when trying to get this working on Windows Server 2003. Here are a couple of tips that could help when troubleshooting:

1. Make sure that you have WebDAV allowed on IIS on the server hosting your WSS Site.

2. If you are trying to add the WSS Site to “My Network Places” on a Windows Server 2003, you will need to install a patch from Microsoft – Software Update for Web Folders (KB907306). You know that you will need to do this when you get an error message about the address not being recognised as a folder. Also, if you a get a “404 – Page not Found” panel when switching to Explorer view, you need to install this update.

For more information on setting this up and about the web folders download, have a look at these articles:

http://www.intermedia.net/support/kb/default.asp?id=1207

http://blogs.tamtam.nl/mart/SoftwareUpdateForWebFolders.aspx

Faceted Search v2.5 has been announced

The details of the next version are covered in detail on the Enterprise Search blog:

 http://blogs.msdn.com/enterprisesearch/archive/2008/08/12/announcing-faceted-search-v2-5.aspx 

In summary, the main new features include:

• Caching – dramatically improves performance and decreases the load on the search engine
• Synchronization with Core Search Results web part
• Support for advanced search
• Match of search counters
• Introducing Parent-Child relationships
• Extending search to logical “OR” queries
• Simplified web part properties

Make sure you also check out the Codeplex project too: http://www.codeplex.com/FacetedSearch