Using External Data with Outlook 2010

To see what happens when you connect your external data to Outlook, let’s create a new external content type.  Why begin with a new external content type rather than edit an existing one?  The
best reason is that you or someone else in your organization might be using the current external content type and changing it could break another process.  Another reason is that for many external content types, you may have created several operations.  When making changes, each operation has to be reviewed and changed individually whereas creating a new external content type can generate all of the operations with a single pass through the parameters as we will see below.
In order to integrate the External Content Type with Outlook, you also need to associate your external data with an Office item type.  Outlook supports four primary item types into which you can map your external data including: Appointment, Contact, Task, and Post.  After selecting an item type, you need to map each of your external fields into one of the fields supported by that item type.  For instance, if you have a table of names along with contact information, you may want to map the external data to a Contact item type.  Let’s assume you have such a table in one of your SQL Server databases and see what steps you need to follow to display and manage the data from Outlook.

  1. Open SharePoint Designer.
  2. Open the site where you want to store the external list of the data.
  3. Select External Content Type from the left navigation menu.
  4. If you have any existing external content types, you will see their names in the main content panel, but assume you want to create a new external content type, not just modify an existing one.
  5. Select External Content Type from the New group of the External Content Type ribbon.
  6. In the External Content Type Information group, click New External content type to the right of Name and enter a name for you content type.  Although SharePoint Designer automatically copies this value to the Display Name field, you  an enter a different display name if you want.
  7. Next, select an Office Item Type (such as Contact).
  8. Since we want to use our external data with other office products like Outlook, make sure that the value for Office Sync for external list is set to Enabled.
  9. Next, click the link to the right of External System to define the connection to the external data.
  10. If you have defined previous connections, you may be able to reuse the connection.  The connection applies to the database, not to a specific table in the database, so reusing a connection could save system resources if you need to use several tables from the same external data source.  If you do not have a connection to use, you can create a new one by first identifying the data source type (such as SQL Server – other types include .NET and WCF or web services).  If you select SQL Server, you must then identify the Database Server, the Database Name, a user-friendly name for the connection, and how you want to validate the user against the data source.
  11. You can now drill down through the connection to select a specific table, view or stored procedure you want to use as the data source.  Remember if you want to modify the data, you may not be able to use a view or a stored procedure as your data source.
  12. To select a table, open the table’s folder and right-click on the table you want to use.
  13. Next, choose the operation you want to be able to perform against the data source.  If you only want users to view data, choose the New Read List and the New Read Item operations. (Both are required to get data from the external list and display it.  However, if you want to edit, delete and add records, the easiest thing to do is to select Create All Operations because  ou can define the parameters for all operations at one time and SharePoint Designer applies them appropriately to each of the operations.
  14. If you selected Create All Operations, the first page of the dialog shown next simply tells you that all operations will be created.  There is nothing you need do on this page.
  15. Clicking Next, you get to define the parameters of the operations.  These are the fields and all field properties.
  16. Deselect any fields from the table that you do not want to use.
  17. Next, identify the columns used in the external item picker.  This can be one or more columns that uniquely identify each record in the data source, but it does not have to be the primary key, especially if the primary key is a numeric ID field.  Select each field you want to use and select the checkbox: Show in Picker.
  18. Next identify any fields that have the Required and ReadOnly checkbox selected.  Deselect the Required checkbox but leave the ReadOnly checkbox selected.  If you do not do this, you will not be able to update the data when you display it.
  19. Next, use the Office Property dropdown to map each of the external data fields to one of the Outlook item fields.
  20. You might also want to change the Display Name to something that is more user-friendly.
  21. When done, you can click the Next button to go to the last page of the dialog, the Filter Parameters.
  22. You use filter parameters when reading large tables to keep the number of records returned to under 2000.  Business Data Connectivity enforces this limit unless you raise it in Central Administrator.  However, large values will degrade performance.
  23. Click Finish to complete the definition of the operations.
  24. Right click the page’s tab and select Save and right-click the tab a second time to Close the external data content type definition.
  25. The External Data Type is not ready to use yet because you have not yet defined permissions.
  26. Open Central Administrator (or ask your SharePoint Administrator) and select Manage service application in the Application Management group.
  27. Click Business Data Connectivity Service.  On the resulting page you should see your External Content Type.
  28. Use the dropdown list associated with the type name and select Set Permissions.
  29. In the dialog box, add the SharePoint users or groups you want to give rights to use this external content type.
  30. For each user or group you add, you must select from the four permissions shown.  The Edit and Execute permissions are necessary to simply use the external content type in an external list, but if you want to connect that external list to Outlook, you must select the permission: Selectable in Clients.  You can also choose whether this user can set permissions (by using the Manage Permissions option of individual items in the list.)
  31. Now open the site and create an external list from this external content type as described in last week’s post.  Open the list after creating it.
  32. With the list open, click Connect to Outlook in the Connect & Export group of the List ribbon.
  33. Some dialog boxes may appear as the Business Connectivity Service add-in starts, but eventually the data connects to Outlook and Outlook should open automatically if it is not already open.  However, I have seen a case (my machine) where Outlook does not automatically open in which case the process may still work but you have to manually open Outlook.
  34. If you open the Folder List or Contacts icons at the bottom of the left navigation of Outlook, you should see a SharePoint External List group in the Folder List Panel or you may just see the external list at the bottom of the Contacts panel (assuming you used the Contact Item Type when defining the External Content Type).
  35. You can now click on the name of your external content type to open it and then update or delete contacts from within Outlook even though you store the data in SQL Server and pass it through SharePoint using an External Content Type.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s