Working with Complex Data Types in Business Connectivity Services

Article
02/10/2010

Business Connectivity Services is an enabling technology for integrating data from external systems (e.g. line-of-business like an ERP or CRM system) with SharePoint 2010 and Office. Data in external systems is frequently exposed through granular web services with complex message structures. While parameter structures of almost any complexity are supported by the schema for external content types, they can create incompatibilities with presentation technologies in SharePoint and Office.

Before I begin talking about solution approaches, let me define what makes a type too complex to handle with out-of-the box capabilities. External content types define stereotyped operations, many of which require a parameter structure that is technically complex. A specific finder (also called Read Item in SharePoint Designer) must return a single complex type which contains the set of fields for an external content type, e.g. a type “Customer” with fields “CustomerID”, “Name”, and “PhoneNumber”. The child elements of this structure map to the columns of an external list. Most external content types contain a finder method which returns a collection of the same type as the specific finder. Issues arise when any of the fields has child elements of its own or if a field is of a type that is not supported by the presentation technologies, e.g. if a “CustomerAddress” field were added to the customer structure which has in turn child elements defining “Street”, “City”, “State” and “PostalCode”.

One of the primary user interface components for external data SharePoint is the external list. It is by design a flat, row-based representation of data. Without additional work by a developer, complex types and simple types that are not supported in the SharePoint List type system will be omitted from the list view.

The BCS external content type schema supports the notion of complex formatting for read-only scenarios if the complex type can be converted to a simple string using a .NET format string (example below). If read-only support is not enough or the complex type cannot be converted with this method SharePoint 2010 provides the capability to create custom field types which provides control over rendering and editing behavior in external lists and list item forms.

Forms represent the primary user interface for external list items. Complex types pose a challenge to form generation because they are very difficult to layout automatically while maintaining a minimum level of visual appeal. External lists may be associated with SharePoint list forms (based on ASP.NET) or InfoPath forms.

Each approach warrants its own post. In the remainder of this post I’ll provide an overview of each technique and links to walkthroughs and articles that can take you the rest of the way. Please send us your comments about what was helpful or where there are gaps so we can add more content where needed.

So, let’s talk about solutions.

Complex Formatting in BCS External Content Type

If the complex type is read-only on the external list and can be rendered as a string, scenarios in which a complex type can be converted to a string, BCS supports the notion of a format string on the type descriptor for a complex type to render it in an external list or item form. Here’s an example based on the Customer type mentioned above:

SharePoint Compatible Service Layer

The cleanest approach, architecturally, is to create a service layer with a flat parameter structure that exists within the limitations of the presentation layers. To continue with the example from above, if the web service were to return all fields representing the customers at the same level, SharePoint could render and edit customer items without any further modification.

This approach comes with the cost of writing, hosting, and maintaining custom code that has to be traded off against the cost of handling complex types in the presentation layers for each application using a complex service. In many enterprise deployments a service layer already exists. Especially for business entities that have broad use in many applications, e.g. Customer or Product, the cost of adding methods for use with SharePoint may be far lower than the cost of handling the type complexity in each application.

.NET Connectivity Assemblies

If adding or extending a service layer is not an option, SharePoint 2010 provides a way to package the external content type definition with a class that encapsulates the business logic behind an external content type operation. You can define the flattened interface of the external content type using the Business Data Connectivity Designer in Visual Studio 2010 and implement any aggregation and type conversion logic needed in the code stubs generated by the tool. Once the implementation is complete, Visual Studio can generate a WSP package containing the external content type definition and the assembly which can be imported to SharePoint. In order to import this type of package, farm administrator privileges are required because the code behind the external content type runs in full trust mode. For more details on the creation of .NET connector assemblies see Boris Scholl’s overview of the Business Data Connectivity Designer in Visual Studio 2010.

Since modifying the behavior of backend services is not always an option, SharePoint 2010 also provides several ways to extend the presentation layer to handle with complex types.

Extending the External List Type System

The list infrastructure supports a set of generic data types out of the box. Those are sufficient to address the most common data entry scenarios. In order to represent complex business data items like addresses or consistently for data viewing and editing, SharePoint provides a way to define custom field types which can be made available across all sites on a SharePoint farm. The custom field type definition file is at the center of a custom field type. It defines how a field is to be rendered on the list and item views. It also defines any variable properties and a reference to the assembly implementing the field type. The field class inherits from SPField and represents particular fields based on the custom field type. It defines the capabilities of a column based on the custom field type, e.g. sorting, as well as the behavior of a cell within a list and the content database. It may also implement custom validation logic.

Most complex custom field types also require a rendering control class, a rendering template or a custom value class. More details about custom field types and all the concepts mentioned see the MSDN article about custom field types and the walkthrough for creating a custom field type.

Once the custom type is defined it can be referenced by type descriptors in the external content type definition like in this example:

<TypeDescriptor TypeName="Customer.Address” Name="CustomerAddress" DefaultDisplayName="Customer Address">
   <Properties>
       <Property Name="SPCustomFieldType" Type="System.String">Customer Address</Property>
   </Properties>
</TypeDescriptor>

With that addition, an external list based on the external content type understands that type descriptor “CustomerAddress” refers to a custom field type and renders it appropriately.

Customizing SharePoint List Forms

When you create a new external list, SharePoint will automatically generate forms to enable the creation, editing and viewing of list items. SharePoint forms are based on .NET Framework controls and share their extensibility capabilities which can be used to render complex types appropriately. Custom field types described above is the extensibility mechanism most often used for complex types. For simpler cases it may be possible to extend a native SharePoint Foundation field. For more details see the MSDN article on list form customization.

Customizing InfoPath Forms

SharePoint 2010 also supports the use of InfoPath forms for viewing and editing list items. InfoPath provides a number of features that make it easy for non-developers to create attractive forms with powerful validation and conditional formatting. Another advantage InfoPath forms have over standard list forms is their ability to function inside the browser as well as standalone or in rich clients. E.g. InfoPath forms are the forms technology used to work with external list data offline in SharePoint Workspaces. In SharePoint 2010 InfoPath forms for external lists cannot be created from scratch in the InfoPath Designer. They can be generated in SharePoint Designer either from the external list or external content type designers. Once generated, they can be customized in InfoPath Designer.

For external content types containing complex types, the forms generator will generate a blank form due to the difficulties in automatically generating a visually appealing layout. However InfoPath does support complex types in the designer. After the form is generated it can be opened in InfoPath Designer from the external list designer in SharePoint Designer. The custom types are available in the field list and can be laid out manually.

Figure 1 - Field List in InfoPath Designer

Once the customizations are complete the updated form has to be published back to the external list using the Quick Publish available in the File menu.

Figure 2 - Quick Publish Button in InfoPath Designer

I hope this post provided guidance on how to overcome the incompatibility issues between the complex world of external systems and the presentation layers in SharePoint and Office.

-Thomas Mechelke, Program Manager on the SharePoint Business Connectivity Services team

Updated 06/14/2010

Comments

Anonymous
February 10, 2010
You mention that it's possible to extends the type system of external lists using SPFields.Can you provide an example of how this would work.I know how to create custom SPFields but how can I configure the External List to use my SPField.
Anonymous
February 15, 2010
Hi Steven,I added a sample to section "Extending the External List Type System" that shows how to reference the custom field type in the external content type definition. External lists created based on the external content type will pick up the type reference and render it accordingly.Add another comment here if you have additional questions.Thanks,-Thomas
Anonymous
February 16, 2010
The SPCustomFieldType is a great help thanks.I was able to get a string version of my complex type rendered by using this fragment.<Property Name="SPCustomFieldType" Type="System.String">MyCustomField</Property>Where MyCustomField is the field name of my custom SPField as registered in my fldtypes.xmlIs this the correct way to use the SPCustomFieldType property properly?In the debugger I saw MyCustomField was constructed and GetValueAsHtml() was called with the ToString() of my Complex Type.In order to render properly I need access to the real complex type object not the ToStringed representation.Is there some method in the SPField I can override that will give me access to the real object?
Anonymous
February 24, 2010
Hi Steven,You are using SPCustomFieldType correctly. From how you are describing your issue, you are missing the Field Rendering Control and the Field Rendering Template for your complex type. Ultimately, the complex type has to be rendered in HTML. The Field Rendering Control and the Field Rendering Template define the shape of this HTML.Take a look at the sections about Creating a Field Rendering Control and Field Rendering Template in the walk through at http://msdn.microsoft.com/en-us/library/bb861799(office.14).aspx.I hope this helps.Thanks,-Thomas
Anonymous
March 11, 2010
hm I still don't get it. Let's say for an example that I would like to display a SPFieldUrl, would it looks like:<TypeDescriptor TypeName="System.String" Name="Url" DefaultDisplayName="Url Test"> <Properties> <Property Name="SPCustomFieldType" Type="System.String">Microsoft.SharePoint.SPFieldUrl</Property> </Properties></TypeDescriptor>and then a field of type "String" with the name "Url" must be created? I guess that string would have to be in the format "description; url"But this approach doesn't seem to work.
Anonymous
March 14, 2010
The comment has been removed

Share via