Creating advanced External Item Picker filter control using External Content Type - SharePoint 2010

Learn how to use the External Item Picker filter to create an InfoPath form to search data from external data source and SharePoint content in one go.

With the use of External Item Picker control you can create powerful InfoPath forms that allow users to search SharePoint contents, database contents or any other external data sources. Let’s say you have an InfoPath form that needs to find data from thousands of items stored on your SharePoint environment and at the same time also to search other environments. In previous versions of SharePoint, It was a big challenge and could take hours of work. Now, with the help of SharePoint Designer 2010 and InfoPath 2010 it is much easier.

In this walkthrough, we will create a Person Picker control to search users from SharePoint User Profile and from external data source. First we will develop a WCF Service to help us to search users from both sources then we will create a simple InfoPath form with an External Item Picker control connected to External Content Type.

In this walkthrough we will cover the following topics:
Create WCF service in SharePoint 2010 using Visual Studio 2010
Create an External Content Type with Filters to show in picker using SharePoint Designer 2010
Add an External Item Picker Control Filter in an InfoPath Form Using InfoPath 2010
Set up administrator-approved form template using Central Administration

Prerequisites
• Server running Microsoft SharePoint Server 2010, you should have access to Central Administration, including the search service and the business data connectivity service.
• Microsoft SharePoint Designer 2010
• InfoPath Designer 2010
• Microsoft .NET Framework 4.0 installed
• Visual Studio 2010
CKSDEV Templates for Visual Studio (Recommended)

To create External Content Type in SharePoint Designer 2010 is very straightforward and easy to use. But before we do so, we must define the necessary queries in the external data source (WCF, web services or .NET assemblies). So, first and most importantly in this walkthrough is to bring the data from our external data sources to SharePoint. Then we need to implement these two operations: Read Item and Read List (with filter) to support our External Content Type.

Create WCF service in SharePoint 2010 using Visual Studio 2010
You can follow the step below or Download it from here

1. New Project, Under SharePoint 2010 templates, Select Empty SharePoint Project, and name it EggheadcafeWCF
2. Select Deploy as farm solution
3. Add new item: WCF Service (CKSDev) and name it EggheadcafeWCF
4. Adding Code to the WCF Service

The IEggheadcafeWCF interface defines the service and user profiles supported by the service. The EggheadcafeWCF class provides the implementation of the IEggheadcafeWCF interface. The following steps describe how to implement the Finder, Specific Finder operations that are required so that the service can be used to create an external content type.

a. Click the Add Reference dialog box. Add these references:
• Microsoft.Office.Server.UserProfiles
• Microsoft.Office.Server
• System.Runtime.Serialization
• System.Web

b. Create new class and call it User.cs - Replace the contents of the User.cs file with the following code:

using System.Runtime.Serialization;

namespace EggheadcafeWCF
{
[DataContract]
public class User
{
[DataMember]
public string Id;
[DataMember]
public string Name;


public User(string id, string name)
{
Id = id;
Name = name;
}

public User()
{
}
}
}

c. In the Visual Studio Solution Explorer, double-click the IEggheadcafeWCF.cs file to open it in the Code Editor. Replace the contents of the IEggheadcafeWCF.cs file with the following code.

using System.Collections.Generic;
using System.ServiceModel;

namespace EggheadcafeWCF
{
[ServiceContract]
public interface IEggheadcafeWCF
{
[OperationContract]
User GetUserById(string id);

[OperationContract]
List<User> GetUsers(string filter);
}
}

d. In the Visual Studio Solution Explorer, double-click the EggheadcafeWCF.svc.cs file to open it in the Code Editor. Replace the contents of the EggheadcafeWCF.svc.cs file with the following code.

using System.Collections.Generic;
using System.Linq;
using Microsoft.Office.Server.UserProfiles;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Client.Services;
using System.ServiceModel.Activation;

namespace EggheadcafeWCF
{
[BasicHttpBindingServiceMetadataExchangeEndpoint]
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
public class EggheadcafeWCF : IEggheadcafeWCF
{
public User GetUserById(string id)
{
if (string.IsNullOrEmpty(id))
return null;

var users = GetUsersFromDatabase();
users = GetUsersFromSharePoint(users, id);
var user = (from u in users
where u.Id.ToLower() == id.ToLower()
select u).OfType<User>().FirstOrDefault();

return user;
}

public List<User> GetUsers(string filter)
{
if (string.IsNullOrEmpty(filter))
return null;

var users = GetUsersFromDatabase();
users = GetUsersFromSharePoint(users, filter);
var userQuery = (from user in users
where user.Name.ToLower().Contains(filter.ToLower())
select user);

return userQuery.ToList().Count == 0 ? null : userQuery.ToList();
}

private static List<User> GetUsersFromSharePoint(List<User> sharepointUsers, string filter)
{
if (string.IsNullOrEmpty(filter))
return null;

SPSecurity.RunWithElevatedPrivileges(delegate()
{
var site = new SPSite("http://moss");
var serviceContext = SPServiceContext.GetContext(site);
var userProfileManager = new UserProfileManager(serviceContext);
ProfileBase[] searchResults = userProfileManager.Search(filter);
sharepointUsers.AddRange(searchResults.Select(profile =>
new User(profile.PublicUrl.ToString().Substring(profile.PublicUrl.ToString().IndexOf("?accountname=") + 13),
profile.DisplayName)));
site.Dispose();
});

return sharepointUsers;

}

private static List<User> GetUsersFromDatabase()
{
var users = new List<User>();
var user = new User { Id = "1", Name = "Gustavo Achong" }; users.Add(user);
user = new User { Id = "2", Name = "Catherine Abel" }; users.Add(user);
user = new User { Id = "3", Name = "Kim Abercrombie" }; users.Add(user);
user = new User { Id = "4", Name = "Humberto Acevedo" }; users.Add(user);
user = new User { Id = "5", Name = "Pilar Ackerman" }; users.Add(user);
user = new User { Id = "6", Name = "Frances Adams" }; users.Add(user);
user = new User { Id = "7", Name = "Margaret Smith" }; users.Add(user);
user = new User { Id = "8", Name = "Carla Adams" }; users.Add(user);
user = new User { Id = "9", Name = "Jay Adams" }; users.Add(user);
user = new User { Id = "10", Name = "Ronald Adina" }; users.Add(user);
user = new User { Id = "11", Name = "Samuel Agcaoili" }; users.Add(user);
user = new User { Id = "12", Name = "James Aguilar" }; users.Add(user);
user = new User { Id = "13", Name = "Robert Ahlering" }; users.Add(user);
user = new User { Id = "14", Name = "François Ferrier" }; users.Add(user);
user = new User { Id = "15", Name = "Kim Akers" }; users.Add(user);
user = new User { Id = "16", Name = "Lili Alameda" }; users.Add(user);
user = new User { Id = "17", Name = "Amy Alberts" }; users.Add(user);
user = new User { Id = "18", Name = "Anna Albright" }; users.Add(user);
user = new User { Id = "19", Name = "Milton Albury" }; users.Add(user);
user = new User { Id = "20", Name = "Paul Albury" }; users.Add(user);
user = new User { Id = "21", Name = "Gregory Alcorn" }; users.Add(user);
user = new User { Id = "22", Name = "J. Phillip Alderson" }; users.Add(user);
user = new User { Id = "23", Name = "Michelle Alexander" }; users.Add(user);
user = new User { Id = "24", Name = "Sean Alexander" }; users.Add(user);
user = new User { Id = "25", Name = "Phyllis Allen" }; users.Add(user);
user = new User { Id = "26", Name = "Marvin Allen" }; users.Add(user);
user = new User { Id = "27", Name = "Michael Allen" }; users.Add(user);
user = new User { Id = "28", Name = "Cecil Altamirano" }; users.Add(user);
user = new User { Id = "29", Name = "Oscar Alexander" }; users.Add(user);
user = new User { Id = "30", Name = "Sandra Alpuerto" }; users.Add(user);
return users;
}
}
}

5. Deploy the solution
6. Test your WCF service with the Test Client tool (WcfTestClient.exe). You can find the WCF Test Client (WcfTestClient.exe) in the following location: C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\
File Add new Service: type http://<SERVER>/_vti_bin/EggheadcafeWCF/EggheadcafeWCF.svc/mex

Create an External Content Type with Filters to show in picker

When you create operations for the external content type, you can see them in the External Content Type Operations part in the Operations Design View.

1. In SharePoint Designer 2010, open the SharePoint site where you want to store the External Content Type
2. In the left navigation pane, click External Content Types.
3. In the contextual Ribbon for entities, click External Content Type.
4. Click the Name link and type Users.
5. Click the Display Name link and type Users.
6. Click the Namespace link and type EggheadcafeWCF
7. Click the External System link and click Add Connection, select WCF Service

Type these properties:
Service Metadata URL: http://<SERVER>/_vti_bin/EggheadcafeWCF/EggheadcafeWCF.svc/mex
Metadata Connection Mode: WSDL
Service Endpoint URL: http://<SERVER>/_vti_bin/EggheadcafeWCF/EggheadcafeWCF.svc


After validating the connection to the WCF service, the Operation Design View pane lists the methods in the WCF service definition in the Data Source Explorer window as shown below:



Once you have created a Read Item and a Read List operation, you can create an external list by using this external content type.

8. Create a Read Item operation.
a. Right-click the GetUserById method in the Data Source Explorer, and then select New Read Item Operation on the context menu.
b. In the Read Item wizard, click Next to keep the default name and display name values for the operation.
c. Click the Id field in the Data Source Elements group box and select the Map to Identifier checkbox in the Properties group box.



d. Click Next to continue
e. Click the id parameter in the Data Source Elements group box.
f. Select the Map to Identifier checkbox in the Properties group box.



g. Click Finish.

9. Create a Read List operation.

a. Right click the GetUsers method, and then click New Read List Operation on the context menu.
b. In the Read List wizard, click Next to keep the default name and display name values for the operation.
c. Click the Click to Add filter on the Input Parameters Configuration
Select Comparison as Filter Type, Name as Filter Field and Checked the Use to create match list in external item picker



d. Click Ok and then Next
e. Click op Id in the Data Source Elements group box.
f. Checked Map to Identifier



g. Select Last Name field and click the Show In Picker checkbox.



10. Click Finish.
11. Save the External Content Type
12. Set permissions and verify that the External Content Type is saved correctly
a. On the Central Administration Home page, click Application Management.
b. On the Application Management page, in the Service Applications section, click Manage service applications.
c. On the Service Applications page, find the Business Data Connectivity Service Application, in the Service Application Information page you will found your BDCApplication:



d. Set permissions on the Users external system. In the Set Object Permissions dialog box, type all authenticated users into the field. Click Check Names, and then click Add.


Add an External Item Picker Control Filter in an InfoPath Form Using InfoPath 2010

1. Open InfoPath Designer 2010, click Blank Form, and then click Design Form.
2. Add some text (Users) and an External Item Picker



3. The figure below shows how the General tab might look for getting the Users external content type from the WCF Service



4. The figure below shows how the General tab might look for getting the Users external content type from the WCF Service



5. Select File, Info. Click the Form Options button. The Form Options dialog appears.
6. In the Form Options dialog, select Security and Trust.
7. Change the security level to Full Trust, Click OK.
8. Save the InfoPathForm
9. Publish the form, Select File, Publish and click SharePoint Server.

Select your SharePoint site


Checked Enable this form to be filled out by using a browser


Publish the form on your computer


Click Next, Next and Publish.

Set up administrator-approved form template using Central Administration


1. Navigate to the Central Administration, Click General Application Settings then Manage form templates
2. Upload the form you published on your machine.




3. Activate the form on a site collection; navigate to the site collection you want to publish the form.
4. Go to Site Actions > Site Settings > Site Collection Features
5. Activate the EggheadcafeInfoPath feature



6. Create new Forms library, go to the Site Actions menu, and select More Options. Click on the Library and Select Form Library, Type Eggheadcafe Forms in de name field


Click Create

7. Click Library Settings, In the Advanced Settings Page, select Yes under Allow management of content types and click OK.


8. Choose the link Add from existing site content types. Add EggheadcafeInfoPath content type


9. Click on Form, under Content Type, then click Delete this content type
10. Test your form, navigate to Eggheadcafe Forms library and click Add document



Sorce code

You can download the Visual Studio and the InfoPath form from here.

Summary

This article is to show you how to build a truly powerful InfoPath forms with search capabilities. With only few lines of code the user can now perform complex search operations on SharePoint content and external data sources.

By Alon Havivi   Popularity  (7643 Views)
Picture
Biography - Alon Havivi

Alon Havivi is a Microsoft Certified Technology Specialist, working as SharePoint Consultant / Developer at e-office. Specialized in SharePoint 2007/2010 and SharePoint Online (Office 365). With more than 10 years of experience in analysis, design and development complex Internet and Intranet portals using the latest Microsoft tools and practices, such as C# .NET 4.0, Silverlight and Windows Azure platform. Besides professional work, I write articles/blog and publish open source projects on CodePlex
View Alon Havivi's professional profile on LinkedIn. View Alon Havivi's projects on CodePlex. View Alon Havivi's articels on Eggheadcafe. Follow Alon Havivi on Twitter Connect with Alon Havivi via Facebook View Alon Havivi's Blog Subscribe to Alon Havivi RSS Feed