Introduction to external data

There’s a lot of data inside your SharePoint sites, but a lot more data outside those sites. Wouldn’t it be nice to work with that external data as if it was inside your SharePoint sites, as well as your Office products? By using Business Connectivity Services, you can make this happen.

Overview of external data

For more information, see Find content about external data.

In this article


Understand the big picture

There are many reasons to use external data. And a small team can do big things when it works together to create a business solution based on external data. Here’s how it’s often done and the rewards it can yield.

Why use external data?

As you use SharePoint products to help streamline your business activities, you may need to incorporate external data, such as databases, Line-of-Business applications, and Web services. But this external data is often like a secluded island that is restricted to privileged folks, or just plain hard to get to. Is there a way to get this external data smoothly into SharePoint products, as well as Office products, so that you can leverage a familiar interface and further enhance your business?

That’s what Business Connectivity Services is all about. It enables secure, efficient, read/write access to a wide variety of external data. This is done through a comprehensive framework that provides standard user and programming interfaces. Furthermore, you can create a wide range of business solutions, both no-code, SharePoint Composite solutions for simple to intermediate activities and code-based solutions for advanced needs. For example, you can:

  • Create a SharePoint external list of employee personnel data stored in a back-end database that users can read and write, similar to a native SharePoint list.
  • Connect up-to-date customer data in your organization’s Enterprise Resource Planning (ERP) system as a set of Outlook contacts and even provide offline access.
  • Create an event planning solution that links to a controlled Line-of-Business application, but uses a SharePoint calendar for all to see.
  • Create a Microsoft Silverlight application with a cool visual user interface that works with external data from the Web.

In short, Business Connectivity Services lets you connect to, interact with, and unlock the value of external data.

Top of Page Top of Page

Three essential scenarios

Want to hear more? Here are three examples of essential solutions that are based on Business Connectivity Services.

Help desk    A large enterprise has a help desk, which provides internal technical support. Support requests and the technical support knowledge base are stored in an external database, but are fully integrated in a SharePoint site by using external lists and the Business Data Web Parts. Information workers can also view their current requests in Microsoft Outlook 2013 online. Tech support specialists can view requests assigned to them in Outlook 2013 online and offline access. Workflows automatically manage support issues through each stage: Opened, Assigned, Escalated, Resolved, and Closed. Managers on the technical support team, with appropriate permission, can view dashboards that display help desk reports indicating the number of support issues assigned to each support specialist, the most current or critical issues, and the number of support incidents that are handled by each support specialist during a given time period. Managers can also export the data to Microsoft Excel 2013 for further detailed analysis using conditional formatting and charts.

Artist tracker    A talent agency integrates its database of artists into its internal SharePoint team site, which includes a complete list of artists, their contact information, and performance schedules. Recording contracts in the form of Word document templates are generated and automatically filled with the latest artist information. A workflow guides each contract through its various stages. Agents can add new artists to an external list by using an InfoPath form. By using this solution, talent agents always have the information that they need nearby, and they can perform many key tasks by using familiar Office products.

Sales dashboard    A sales dashboard application helps sales associates quickly find sales orders and customer information managed in a Customer Relations Management (CRM) system. Depending on their roles and respective permissions, team members can view sales analytics information, individual team members’ sales performance data, sales leads, and a customer’s contact information and orders. Sales professionals can view their daily calendars, view tasks assigned to them by their managers, collaborate with team members, and read industry news. A Visio diagram of sales territories maps sales by each month and contains live data from the back-end database that is easily refreshed. By using a Word document, managers can quickly author monthly status reports that automatically includes data from the external systems.

Top of Page Top of Page

Getting there is half the fun

Business Connectivity Services is like a big umbrella that encompasses many SharePoint and Office features. But it doesn’t work “out-of-the-box” because every external data source is unique and each organization has specific needs. Once you lay the groundwork, on-premise or in the cloud, you can surface the external data inside SharePoint products and Office products. When working with external data, you typically go through three distinct phases:

The groundwork phase    During this phase, you plan, prepare, and set up how to access the external data. This usually means working closely with an administrator and sometimes a developer to understand what needs to happen. Important goals are identifying business and technical requirements, preparing the external data source, ensuring secure access to the data by using the Secure Store Service, and scoping the overall solution. A pivotal goal is creating an external content type, which is a detailed package of connections, metadata, and other information used to retrieve the external data.

The SharePoint phase    Once the groundwork has been laid, you can now surface the external data in SharePoint products in a variety of ways, including external lists, external data columns, Business Data Web Parts, workflows, search results, profile pages, actions, Apps for SharePoint, and code-based SharePoint components or solutions.

The Office phase    Once you have created access to the external data in SharePoint products, you can now surface the data in a variety of Office products (including Excel, Word, Outlook, Visio, Access, and InfoPath), Apps for Office, and code-based Office components, add-ins, or solutions.

The three phases of development

You’ll find more details about each of these features later in this article.

Top of Page Top of Page

Which hat do you wear?

Developing a solution based on external data is usually a team-effort and the way you work depends upon your role. The following diagram shows a high-level view of the roles and solutions that are involved in an overall development life cycle.

Development lifecycle roles and tasks

Users    Users assemble simple solutions by using the basic capabilities of SharePoint products and Office products. Examples include:

  • Creating an external list, different views of the list, and adding various filters and sorts.
  • Exporting the external list to Excel 2013 for further data analysis.
  • Connecting an external list to Outlook 2013 to work with the external data online and offline.

Power Users    Power users create simple to intermediate, no-code business solutions and often require business analyst skills to understand the database systems and the overall business processes in the organization. They also communicate the requirements to administrators and developers, as well as business managers and perhaps site designers. Examples include:

  • Creating an external content type by using Microsoft SharePoint Designer 2013.
  • Creating external data columns in libraries and linking the columns to content controls in a customized Word template, such as a contract or invoice.
  • Customizing InfoPath forms to replace the default SharePoint list forms.
  • Designing a Web page that connects Business Data Web Parts in a useful way to create an information workspace.
  • Creating an Outlook-based solution that provides customized forms, views, ribbon commands, actions, and task panes.
  • Creating a workflow that uses external list data.

Administrators    Administrators manage user permissions and security for several different SharePoint components including the external content type and the external database. A critical activity involves setting up Secure Store Service target applications, mapping of credentials, and determining the best authentication methods. Other examples of operations include:

  • Managing and setting up the external data access, permissions, and queries.
  • Importing and exporting external content types from the Business Data Connectivity metadata store.
  • Setting up custom profile pages, actions, and search results.

Developers    Developers create a variety of custom solutions that range from targeted, reusable components to advanced code-based solutions. Examples include:

  • Advanced external content types developed in Microsoft Visual Studio, such as custom field types that use complex hierarchical data.
  • Aggregated data from multiple services presented as a single BDC model.
  • Reusable components, custom Web Parts, custom workflow activities, and code-behind InfoPath forms.
  • Custom external data parts for Outlook solutions.
  • Microsoft Silverlight applications and Office add-ins.
  • Custom alerts by using the Event Framework.
  • Custom connections or solutions using managed code.

Top of Page Top of Page

Laying the groundwork

Whatever external data solution you decide to create, you need to create a strong foundation, and that means: getting and setting up the right software, choosing and preparing an external data source, turning on SharePoint services and accounts, and creating an external content type.

Know your SharePoint environment

When you use or create an external data solution, there are differences in features and functionality that you should be aware of based on your SharePoint environment:

  • On-premise    If you create a solution in which your users and your solution are entirely on-premise (behind the firewall), then all Business Connectivity Services features are supported.
  • Cloud    If you are using SharePoint Online (Plan 2, E3 and E4) in Office 365, then a subset of features are available. For example, external lists, Business Data Web Parts, and the Event framework are supported, but profile pages, actions, and the Connector framework are not. Furthermore, you can only connect to these data sources: WCF Web Services, OData, and SQL Server Azure.
  • Hybrid    Your organization may already have significant investments in on-premise data sources, such as Line-of-Business applications and ERP systems. But your organization may also be in a hybrid SharePoint environment, in which some employees are on-premise and some are in the cloud. In a Hybrid environment, you can also create a hybrid external data solution so that users in the cloud can access an on-premise data source. For example, a user can complete an expense report while on a business trip by using SharePoint Online, and the charges can be immediately reconciled with an on-premise SAP system. You can connect to any supported on-premise data source, but you must use OData Services and extensive development and configuration may be required.

Get the right software

The features of Business Connectivity Services (BCS) are distributed across different SharePoint editions. Use the following table to help you make the right choice.

Business Connectivity Services Feature SharePoint Foundation 2013
SharePoint Server 2013 (Standard)
SharePoint Server 2013 (Enterprise) SharePoint Online (SPO)
Plan 2
(E3, E4)
Comments
External List
External data column
Business Data Connectivity Services SPO only supports WCF and SQL Server Azure Connectors
Secure Store Service SPO only supports group credentials
External Data Search
Profile Pages
Actions
Business Data Web Parts
Office product support
Rich Client Extensions *
App-scoped BCS
Connector framework
Event framework
OData Connector
BCS OM APIs
Hybrid (SPO to On-premise) Only by using OData Connector

* Includes form controls to display external data and layouts to display data in an Outlook task pane and form region. Available only in Microsoft Office Professional Plus 2013.

Top of Page Top of Page

Setup the software correctly

Once you have installed the right software, make sure you set it up correctly. In particular:

Prepare the external data source    An administrator may need to create an account and provide permissions to the data source to ensure that the right people have access to the data and that the data does not end up in the wrong hands. In an external database, the administrator may also want to create specific tables, views, queries, and so on to limit the results to just what is needed and to help improve performance.

Configure SharePoint services    An administrator must activate Business Data Connectivity Services, Secure Store Service, and Visio Services.

Configure the Secure Store service    An administrator must determine the best access mode for the external data source, create a target application, and set the credentials for the target application.

Configure Business Data Connectivity Services    An administrator must make sure that the user who creates the external content type has permission to the Business Data Connectivity (BDC) metadata store and that appropriate users have access to the external content type on which the external list is based.

Make sure Office products are ready to use    To synchronize external data with Office products, you must have Windows 7 or later and the following free software products, SQL Server Compact 4.0, .NET Framework 4, and WCF Data Services 5.0 for OData V3 on each client computer (If necessary, you are automatically prompted to download the software). Also, make sure the Office installation option, Business Connectivity Services is enabled (This is the default). This option installs the Business Connectivity Services Client Runtime which does the following: caches and synchronizes with external data, maps business data to external content types, displays the external item picker in Office products, and runs custom solutions inside Office products.

Top of Page Top of Page

Choose your external data wisely

You can work with a wide variety of external data sources, including databases, Enterprise Resource Planning (ERP) Systems, OData, and advanced connectors.

Databases    Organizations and companies of all shapes and sizes depend on databases for their daily operations. Databases contain a wide variety of data types including text, numbers, images, and documents, and have many purposes including financial, manufacturing, insurance, and data-driven company Web sites. Common supported databases include: Microsoft SQL Server, Oracle, IBM DB2, and any database that complies with ODBC or OLE DB interface standards.

ERP systems    ERP systems integrate Line-of-Business data and processes across an entire organization and perform many of the fundamental activities for any business including:

Business Function Business Process
Finance and Accounting General ledger, payables and receivables, cash management, fixed assets, and budgeting
Human resources Payroll, training, benefits, retirement, recruiting, and diversity management.
Customer relationship management (CRM) Sales and marketing, commissions, service, customer contact, and call center support.
Manufacturing Engineering, bill of materials, work orders, scheduling, workflow management, quality control, and product lifecycle management.
Supply chain Inventory, order entry, purchasing, planning, inspection, and claim processing.
Project management Costing, billing, time and expense, performance units, and activity management.

Common supported ERP systems include SAP, Duet, Microsoft Dynamics, Siebel, and JD Edwards.

OData    The Open Data protocol (OData) provides a REST-based standard for Create (Post), Read (Get), Update (Put), and Delete (Delete) operations that access Web Services. Think of OData as the web-based equivalent of ODBC and OLE DB. Data is returned either in XML or JavaScript Object Notation (JSON) format. Common OData sources include familiar Web sites (such as Amazon, E-bay, Azure Data Market, and Netflix), databases, and even other SharePoint sites.

Advanced connectors    For other data sources, a developer can provide advanced external data access by:

  • Using ADO.NET, which enables access to any data source with an ADO.NET provider.
  • Using WCF and Web services, which enables access to Web services and Windows Communication Foundation (WCF).
  • Using .NET Connectivity Assemblies, which enables access to data sources when an ADO.NET provider or Web service is unavailable.
  • Converting unsupported proprietary data types to supported .NET data types.
  • Creating custom solutions for larger-scale data sources with frequently changing data structures that require custom data connections, transfer methods, and connector installation.

Top of Page Top of Page

Consider performance

Any business solution must take performance into consideration, and external data is no exception. Fortunately, Business Connectivity Services has been designed to alleviate many performance bottlenecks and concerns. In general, data retrieval, paging, filtering, and sorting is offloaded to the external data source to help reduce memory and processing resources needed by SharePoint products.

Also, Business Connectivity Services has built-in caches on both server (The Business Connectivity Services cache) and client computers (The Business Connectivity Services Client Runtime cache) and may not display the most recent values from the external data system right away. However, you can adjust the refresh intervals based on the needs of your users and the solutions you create. Not all users need to see up-to-date data right away, but if they do, they can, for example, refresh the external list or Outlook contacts folder.

The ability to work offline on a client computer also means that users can do extended work without being connected to the external data, but know that when they come back online, the data gets updated and reconciled correctly.

Overall, these performance features help speed up the display, refresh, and data operations of the external data on a SharePoint Web page and in an Office product.

Top of Page Top of Page

Practice safety first

It’s especially vital that an external data solution be secure because the data is often critical to a business and must not be viewed or changed by the wrong users. Also, there are many ways to secure data and you want your solution to be simple as well as safe. Fortunately, there are ways to minimize complexity, primarily by using the Secure Store Service.

In a nutshell, securing your external data requires answering two basic questions:

Who are you?    This is also called authentication and it is the process of verifying your identity by obtaining your credentials (your username and password).

What are you allowed to do?    This is called authorization and it is the process of determining what tasks you can do on which items of data by obtaining the list of permissions you have been granted.

First, an administrator helps determine the best access mode for the external data source. Note that it is very common for business solutions to authenticate access to external data for which the current user has a different account. But logging in under different credentials can be complicated and cumbersome. The Secure Store Service comes to the rescue by providing storage and mapping of credentials. This makes it easier to use an external data source by logging in just once.

For example, if you are logged into your SharePoint site with one set of credentials, but want to access an external list based on a Siebel application that uses another set of credentials, an administrator can set up the Secure Store Service to automatically map the two sets of credentials so that you don’t have to log in again.

The Secure Store Service also helps manage the infamous "double hop" issue, where each authentication requires a “hop” between two computers, which can be compromised, unless steps are taken to prevent it.

How the double hop problem is solved

Next, an administrator creates several layers of security based on permissions, much like several lines of defense around a fort, to ensure appropriate authorization and access to:

  • The data in the external database or system.
  • The external content type in the Business Data Connectivity metadata store.
  • The external list or external data column.

Finally, an administrator can add an extra layer of protection through encryption. if your external data solution also uses Office applications, an administrator can protect the Business Connectivity Services Client Runtime cache by using the Windows Encrypting File System. Some external data sources also provide ways to encrypt the data.

Top of Page Top of Page

Set the cornerstone: external content types

External content types are at the heart of any external data solution. They are like a broker between you and the external data source. A typical external content type is an organized package of information that contains the following:

  • Connection information to the data source, such as the location, name, and authenticate modes.
  • A definition of the table, fields, primary and foreign keys, validations, and data types (also called metadata).
  • The data operations you can do, such as Create, Read, Update, Delete, and Query (also called CRUDQ).
  • Other information and behaviors, such as mapped Office data types or the enabling of offline synchronization.
  • Various filters, which query the data and help isolate the results, and limits to improve performance.
  • The identity field and display columns for an external content picker used to retrieve external data throughout the user interface.
  • An association based on primary and foreign keys between two related tables, such as Orders and Customers.

One stalwart user defines the external content type and therefore shields other users from the inherent complexity of working with external data. Furthermore all this information is saved in a special BDCM file (.bdcm), in XML format, and stored in the Business Data Connectivity metadata store. This approach makes it easy to maintain, update, secure, and reuse external content types in different sites. For example, you can easily export and import the file to deploy it from a development to a production environment, or share with other site collections.

By the way, your stalwart user doesn’t need to be an expert in XML. To help create an external content type, there are two tools of choice:

  • Microsoft SharePoint Designer 2013    You can define and update the external content type in a declarative way and then publish it to the Business Data Connectivity metadata store.
  • Microsoft Visual Studio 2012    If your external data is complex or is not supported by SharePoint Designer 2013, you can use the XML Editor in Visual Studio (which provides Intellisense) to create your XML model, or use the Visual Studio External Content Type Designer to create a .NET Connectivity Assembly that provides custom back-end integration logic through the .NET Framework.

Top of Page Top of Page

Surface external data in SharePoint products

Now that you have created a solid and safe foundation for your external data, here’s what you can do with it in SharePoint products.

Read and write data in external lists

An external list is a key building block for SharePoint solutions based on external data. You can read and write external data with the familiar experience of using a SharePoint list. An external list looks and behaves a lot like a native list, but there are some differences in behavior. For example, you can create views and use calculated values with formulas, but not attach files or track versions.

You can also create an external list by using SharePoint Designer 2013.

Top of Page Top of Page

Add external data columns to lists and libraries

You can combine external data columns with other columns in a native list or library. Because an external data column is added to a native list or library, all related list or library features work, including versioning, approvals, check-in and check-out, and file attachments.

Let’s say you have created a picture library that contains photos of the products your organization sells. You can add an external data column to the library that accesses the product name, product code, and current price from an external database. Because your company rules and processes require any changes to product information only be made through the database system, you specify that the external data is read-only when you create the external content type. But now more people in your organization can easily see all the common product information together in one library view.

Top of Page Top of Page

Create solutions with Business Data Web Parts

The Business Data Web Parts are designed to work with data from an external list and you can combine these Web Parts in useful ways to create a site workspace or dashboard. For example, you can connect the List and Item Web Parts so that when you select an expense report item in the List Web Part, such as Thursday’s charges, the item details are displayed in the Item Web Part, such as the hotel, travel, and meal charges.

The following table briefly summarizes each Business Data Web Part.

Web Part Description
Business Data List Displays a list of items from an external list.
Business Data Item Displays one or more items from an external list.
Business Data Actions Displays an action from an external list.
Business Data Connectivity Filter Filters the contents of connected Business Data Web Part by using a list of values from an external list.
Business Data Item Builder Creates a Business Data item from parameters in a URL query string and provides it to other Business Data Web Parts.
Business Data Related List Displays a list of child items related to a parent item from an external list.

Top of Page Top of Page

Use external data in a profile page, action, or search

Profile pages, actions, and search are closely-related features.

Profile pages

By default, Business Connectivity Services automatically creates a profile page based on the external content type. A profile page is a Web page that displays information from a single row of external data, based on an ID field, such as all the individual fields about a specific customer.

The default profile page is actually a Web page that contains the Business Data Item Builder and a Business Data Item Web Parts connected together. The Item Builder Web Part analyzes the URL query string parameter of the profile page, for example, http://contoso/Customer.aspx?CustID=345, and passes the CustID value to the Data Item Web Part, which then displays all the corresponding customer fields.

You can create your own profile page to show custom information, or an administrator can customize the profile page for you.

Actions

You can even perform an action based on the profile page to do something useful. For example, you can create an action that combines address information about the customer with Bing maps to view the customer’s location or get directions. You can create an action either based on the URL query string parameter of the profile page or through custom code. Or, a developer can write custom code that enables you to update employee information in a back-end database.

By default, you use an action when you click the View Profile action command in a Business Data Actions or Business Data List Web Part. When using an external data column, you can also run the action from a menu command.

Search

Finally if you want, an administrator can enable search of external data. The external data can be crawled and indexed just like internal SharePoint data, but with security trimming to ensure that only the appropriate users can click and see the search results, displayed by using a profile page.

By integrating SharePoint search into external data systems, users can find data they otherwise would not be able to locate. For example, if you know the company a customer works for, but have forgotten their name, you can search by the company name to help you find the customer’s profile page.

Top of Page Top of Page

Use external data in workflows

Workflows provide a powerful tool for modeling and automating business processes based on a SharePoint list, library, or site. These business processes can be as simple as a document approval process with a single approver, or as complex as any structured business process. Although you cannot directly associate a workflow with an external list, you can create the following:

Native list workflows    A workflow associated with a native list that reads or updates data from an external list by using the Create List Item, Update List Item, and Delete List Item actions. For example, you can update an external list item whenever a native list or library item changes. You can use other actions with external lists, such as the Set Workflow Variable to modify an external list field. You can also use an external list item as a destination for a task process, but the link to the task does not display a title for the external list item.

Site workflows    Site workflows do not require an event trigger to start and can be manually started. To prevent unauthorized access, workflows that access external lists always run in a service account, even with an authentication mode that uses impersonation.

Events    A developer can create events that trigger in the external data source and users can create alerts that respond to these events. For example, you can use a SharePoint workflow to send an email message to an employee when a customer account is assigned to that employee in the external data source.

For performance reasons, it’s good practice to only read a small number of columns in the external list, or create, update or delete a small number of items in the external list.

Top of Page Top of Page

Surface external data in Office products

Now that you have surfaced the data in SharePoint products, here’s what you can do with it in various Office products.

Analyze external data in Excel

You can export data from an external list to Excel 2013 and keep the data synchronized. For example you may want to maintain a product inventory in an external list so that it’s easy to share and update, but also do periodic inventory analysis in Excel 2013. The external data that is exported to Excel 2013 is determined by the current external list view, the filters defined on the external content type and in the external list, and the language settings for the SharePoint site.h

When you export an external list, an Excel Table is created with a data connection to the external list based on a Web query file. To see further changes made to the SharePoint list in the Excel Table, you can refresh the data. Note that you cannot make changes to the source of the external data and that an Excel refresh operation would overwrite any changes you have made in the Excel Table.

Top of Page Top of Page

Add external data to Word documents

By adding external data columns and a Word template with content controls to a document library, you can also make it easy and efficient to enter accurate, up-to-date data in a Microsoft Word 2013 structured document.

Let’s say you have a document library that contains customer contracts. However, all your customer information is maintained in a Line-of-Business application, such as SAP. You can track the customer’s contracts in the document library, but ensure that related customer information is up-to-date and not unnecessarily duplicated. Here’s how:

  • First, add external data columns to the library to associate the appropriate customer name, company name, and telephone number with each contract stored in the Contract library.
  • Then, create a Word template that contains the customer contract boilerplate text and content controls to enter the customer name, company name, and telephone number. Word 2013 will use the external item picker to help you enter or update the correct information by automatically displaying and retrieving the correct customer information.
  • Finally, each time you create a contract, enter customer data, and save the contract back to the library, the customer data values are promoted to the corresponding external data columns.

Top of Page Top of Page

Synchronize external data in Outlook

If an external list contains customers, tasks, appointments, or posts, you can connect the list with Outlook 2013. Once connected, you can view, edit, and print the data using the familiar Outlook user interface. If the data is contact information, such as customers, you can even call them, send them e-mail messages, and make meeting requests.

The synchronization of the data is two-way or bi-directional. Changes made in Outlook 2013 are automatically synchronized with the external data source. Changes made to the external data source are synchronized with the items in Outlook 2013, although a refresh operation may be needed.

By default, the data automatically refreshes every six hours, but this value can be changed. To make sure you are seeing current data from the external data source, right-click the folder and then click Sync DB changes.

When you create the external content type, you do the following:

  • If you want offline access, enable the Offline Sync support option.
  • Set the Office Item Type field to one of the following: Contact, Appointment, Task, or Post.
  • Map the individual fields in the external content type, such as Customer First Name, Customer Last Name, Customer Address, and Customer Phone, to their respective Outlook Item Type fields, such as a contact’s FirstName, LastName, BusinessAddress, and BusinessPhone.

Additionally, with some code written by a developer, you can create an Outlook solution that does the following:

  • Show native or custom data parts in custom task panes to present external data to users.
  • Include custom ribbon commands to perform custom actions.
  • Create a custom view definition to display specific columns of external data.
  • Design custom form regions to present external data.

Top of Page Top of Page

Enhance external data access with InfoPath forms

There are a number of reasons why Microsoft InfoPath 2013 is useful to use with external data:

Working with complex data types    If a field of external data is hierarchical, such as an address field that contains Street, City, and Postal code as a subfields, then you cannot work with this field in the SharePoint form for an external list, and in fact it is not even created. However, you can convert the SharePoint form to an InfoPath form to create and update the fields by using InfoPath 2013.

Embedding an External Item Picker Control    You can drag and drop the External Item Picker control onto an InfoPath form, link it to the external list as a secondary data source, and make it easy for a user to select an item based on a unique field, such as a Customer Name or Product ID.

Furthermore, if you set up an association for external content types, such as customers and orders, and the InfoPath form for the external list contains a foreign key, such as Customer ID, the External Item Picker control is even automatically added to the form. This enables users to easily find the customer they want when working with orders.

Creating Multiple Views of a Form    Sometimes, you may want forms to have multiple views of your external data, especially if there are many fields and an external list or SharePoint form is not effective. Multiple views are also useful in write-once scenarios where the external system does not allow updating a field after it is created. In such cases, you can create two views, one for the create operation and the other for the update operation.

Adding Rules or code-behind to forms    You can add declarative rules or code-behind forms to perform additional business logic, such as validation and calculations. For example, you can ensure a country/region code is valid by using a rule, or U.S. postal code is valid by using a Web Service.

Top of Page Top of Page

Add external data to published Visio diagrams

Using Microsoft Microsoft Visio 2013, you can link data from an external list to a diagram and it’s shapes. You can then publish the diagram to a SharePoint document library as an interactive Web drawing so users can display the diagram and view the associated external data. There are three ways to link rows of data to shapes in your drawing. You can link rows to existing shapes one at a time, you can link the rows to shapes automatically, or you can create shapes from the data.

Top of Page Top of Page

Import and refresh external data in Access

Microsoft Access 2013 is a landing pad for all types of data and external data is no exception. You can import an external list to your Access database on your client computer and periodically refresh the data. The data is read-only.

The basic steps to follow are:

  1. Export the external content type from SharePoint Designer 2013 or directly from the Business Data Connectivity metadata store as a BDCM file.
  2. In Access 2013, create a Web Service data connection by using the BDCM file.
  3. In Access 2013, create a linked table based on the Web Service data connection.

Note that in this case, the Business Connectivity Services Client Runtime cache is not used to synchronize with the external list when you refresh the data.

Top of Page Top of Page

 
 
Applies to:
SharePoint Foundation 2013, SharePoint Online Enterprise (E3 & E4), SharePoint Online operated by 21Vianet - Enterprise (E3 & E4), SharePoint Server 2013 Enterprise