Add a data connection to a SharePoint document library or list

You can add one or more secondary data connections to your form template that can either query data from a document library or list, or submit data to a document library on a server running Microsoft Windows SharePoint Services.

In this article


Overview

A secondary data connection is any data connection to an external data source that you add to a form template. This data connection is different from the main data connection that is created when you design a form template based on a database, Web service, or settings in a connection library. You add a secondary data connection only if you cannot query or send data through the main data connection.

When you add a secondary data connection to your form template that queries data, Microsoft Office InfoPath creates a secondary data source (data source: The collection of fields and groups that define and store the data for an InfoPath form. Controls in the form are bound to the fields and groups in the data source.) with data fields (data field: A field that contains the result of a query to an external data source.) and groups (group: An element in the data source that can contain fields and other groups. Controls that contain other controls, such as repeating tables and sections, are bound to groups.) that corresponds to the way that the data is stored in the document library or list.The relationship between the external data source and the InfoPath data source.

Because the data structure in the secondary data source must match the way that data is stored in the document library or list, you cannot modify existing fields or groups in the secondary data source.

A data connection that queries data from a SharePoint document library or list automatically returns all of the items in that document library or list. If you are using a query data connection to populate a list box, drop-down list box, or combo box control, and the control contains too many items or you want to display only items that meet a specified criteria, you can use a filter in the control to limit the number of items. Find links to more information about filtering the data displayed in a control in the See Also section.

You can also configure each query data connection to save its results so that users have access to the data when their form is not connected to a network. Depending on the nature of the data, you may want to display the query results only when users are connected to a network.

Security  If you are using a secondary data connection to retrieve sensitive data from an external data source, you may want to disable this feature to help protect the data from unauthorized use in case the computer is lost or stolen. If you disable this feature, the data will be available only if the user is connected to the network.

When you add a query data connection to a form template, by default the forms that are based on this form template use the data connection when they are opened by a user. You can also configure your form template to use the query data connection in one of the following ways:

  • Add a rule    You can configure a rule to use the query data connection whenever the condition in the rule occurs.
  • Add a button    You can add a button to the form template that users can click to get data by using the query data connection.
  • Use custom code    If you cannot add a rule or button, you can use custom code to get data by using the query data connection. Using custom code requires a developer to create the custom code.

When you add a submit data connection to your form template, you can configure the form template to allow users to submit their form data, and you can configure the submit options for forms that are based on your form template.

Top of Page Top of Page

Before you begin

Before you add a secondary data connection to your form template, you need the following information from your site administrator:

  • The location of the SharePoint site and the necessary permissions to access it
  • Verification that the site is configured so that your users can get data from the document library or list
  • Verification that you can store the data from the document library or list on your users' computers for offline use

Top of Page Top of Page

Add a query data connection

To allow users to query data from a document library or list on a site that is located on a server running Microsoft Windows SharePoint Services, you need to do the following in the form template:

  1. Add a secondary data connection.
  2. Configure the form template to use the secondary data connection.

Step 1: Add a secondary data connection

  1. On the Tools menu, click Data Connections.
  1. In the Data Connections dialog box, click Add.
  1. In the Data Connection Wizard, click Create a new connection to, click Receive data, and then click Next.
  1. On the next page of the wizard, click SharePoint library or list, and then click Next.

Page in the Data Connection Wizard to specify a SharePoint site as the data source.

  1. On the next page of the wizard, type the URL of the SharePoint site with the document library or list, and then click Next. For example, to use a document list on a SharePoint site with the URL http://www.adventureworks.com/EmployeeList, you type http://www.adventureworks.com/EmployeeList.
  2. On the next page of the wizard, in the Select a list or library list, click the list or library that you want to use, and then click Next.
  3. On the next page of the wizard, select the check boxes next to the values that you want to receive. If the form template will be published to the document library, and you want forms that are based on this form template to get metadata about the form, select the Include data for the active form only check box.
  4. Click Next.
  5. If you want to store the results of the query with the form template, select the Store a copy of the data in the form template check box. Selecting this check box stores the query results in the form template. Because the data is stored in the form template, the data is available in the forms that users fill out, even if their computers are not connected to a network.

Security  Selecting this check box stores the query results in the form template. Because the data is stored in the form template, it is available in the forms that users fill out, even if their computers are not connected to a network. If you are getting sensitive data from this data connection, you may want to disable this feature to help protect the data in case the computer is lost or stolen.

  1. Click Next.
  2. On the next page of the wizard, in the Enter a name for this data connection box, type a descriptive name for this secondary data connection. This is the name that appears in the Data source list in the Data source task pane. If you want this query to run automatically whenever the form is opened by a user, select the Automatically retrieve data when form is opened check box.

Step 2: Configure the form template to use the secondary data connection

If you want forms that are based on your form template to use this data connection after the user opens the form, you can add a rule to the form template that uses this data connection under a certain condition, or you can add a button to the form template that users can click to use this data connection.

Add a rule

You can add a rule to the form template to get data from the query data connection whenever the condition for the rule is met. The following procedure assumes that you have created a query data connection for your form template, and that you have configured a control on your form template to display the data from that data connection.

  1. If the form template has multiple views, click View name on the View menu to go to the view with the control where you want to display the data from the secondary data source.
  1. Double-click the control that you want to display the query results.
  2. Click the Data tab.
  3. Under Validation and Rules, click Rules.
  4. In the Rules dialog box, click Add.
  5. In the Name box, type a name for the rule.
  6. To specify a condition when the rule should run, click Set Condition, and then enter the condition. The rule will run when the condition occurs. If you do not set a condition, the rule will run whenever the user changes the value in the control, and then moves his or her cursor away from that control.
  1. Click Add Action.
  2. In the Action list, click Query using a data connection.
  3. In the Data connection list, click the query data connection that you want to use, and then click OK to close each open dialog box.
  4. To test your changes, click Preview on the Standard toolbar, or press CTRL+SHIFT+B.

Show me Add rule dialog box

                             Video created by Office Online staff writers

Assistance Problems watching the video? Try our troubleshooting tips.


Add a button

You can add a button control to your form template that your users can click to use this query data connection. The following procedure assumes that you have created a query data connection for your form template.

  1. If the form template has multiple views, click View name on the View menu to go to the view with the control where you want to display the data from the secondary data source.
  1. If the Controls task pane is not visible, click More Controls on the Insert menu, or press ALT+I, C.
  1. Drag a button control onto your form template.
  2. Double-click the button control that you just added to your form template.
  3. Click the General tab.
  4. In the Action list, click Refresh.
  5. In the Label box, type the name that you want to appear on the button on your form template.
  6. Click Settings.
  7. In the Refresh dialog box, click One secondary data source.
  8. In the Choose the secondary data source list, click the secondary data source that is associated with the query data connection, and then click OK to close each open dialog box.
  9. To test your changes, click Preview on the Standard toolbar, or press CTRL+SHIFT+B.

Show me Add a button dialog box

                             Video created by Office Online staff writers

Assistance Problems watching the video? Try our troubleshooting tips.


Top of Page Top of Page

Add a submit data connection

To allow users to submit forms that are based on your form template to a document library, you need to do the following:

  1. Add a secondary data connection that submits data to your form template.
  2. Configure the form template to allow your users to submit their form data to the document library.

Step 1: Add a secondary data connection

  1. On the Tools menu, click Data Connections.
  1. In the Data Connections dialog box, click Add.
  1. In the Data Connection Wizard, click Create a new connection to, click Submit data, and then click Next.
  2. Under How do you want to submit your data, click To a document library on a SharePoint site, and then click Next.
  3. In the Data Connection Wizard, in the Document library box, type the URL of the SharePoint document library.
  4. In the File name box, type a name that will be used to identify the form in the document library.

 Tip   You can also use a formula to ensure that each form name is unique.

ShowHow?

  1. Click Insert Formula Button image.
  1. In the Insert Formula dialog box, do one of the following:
    • To use a field in the formula, click Insert Field or Group, click the field that you want to use in the Select a Field or Group dialog box, and then click OK.
    • To use a function in the formula, click Insert Function, select the function that you want to use in the Insert Function dialog box, and then click OK.

 Tip   If the function requires parameters, select the function in the Insert Function dialog box, click OK and then, in the Formula box in the Insert Formula dialog box, double-click the function that you added, and click a field or group. Find links to more information about functions in the See Also section.

  • To insert a value or mathematical operator into the formula, type the value or the symbol for the mathematical operation in the Formula box.
Operation Symbol
Add +
Subtract -
Multiply *
Divide /
  •  Note   If your formula uses the division (/) operator, make sure that there is a space before and after the division operator. If the division operator does not have a space before and after it, InfoPath may interpret "/" as a separator for XPath location steps rather than as a division operator.

  1. To check the formula for the correct syntax, in the Insert Formula dialog box, click Verify Formula.

Find links to more information about formulas in the See Also section.

  1. To overwrite any existing forms in the document library with the same name, select the Allow overwrite if file exists check box.
  2. Click Next.
  3. On the next page of the wizard, in the Enter a name for this data connection box, type a descriptive name for this submit data connection.

After you add the secondary submit data connection, you need to configure your form template to allow users to submit their form data by using this submit data connection.

Step 2: Enable submit on the form template

When you configure the form template to allow users to submit their form data, InfoPath adds a Submit button on the Standard toolbar and a Submit command on the File menu on the form. You can also customize the submit options for your form template in the following ways:

  • Change the text that appears on the Submit button.
  • Change the keyboard shortcut for the Submit button.
  • Create custom messages to display to your users when they submit their forms.
  • Specify whether to leave the form open, close the form, or open another blank form after the form is submitted.
  1. On the Tools menu, click Submit Options.
  1. In the Submit Options dialog box, select the Allow users to submit this form check box, click Send form data to a single destination and then, in the list, click SharePoint document library.
  2. In the Choose a data connection for submit list, click the name of the submit data connection to the SharePoint library.

To change the name of the Submit button that appears on the Standard toolbar and the Submit command that appears on the File menu when users fill out the form, type the new name in the Caption box in the Submit Options dialog box.

 Tip   If you want to assign a keyboard shortcut to this button and command, type an ampersand (&) before the character that you want to use as a keyboard shortcut. For example, to assign ALT+B as the keyboard shortcut for the Submit button and command, type Su&bmit.

  1. If you do not want people to use a Submit command or the Submit button on the Standard toolbar when they fill out your form, clear the Show the Submit menu item and the Submit toolbar button check box.

By default, after users submit a form, InfoPath keeps the form open and displays a message to indicate if the form was successfully submitted. To change this default behavior, click Advanced, and then do one of the following:

  • To close the form or create a new blank form after the user submits a completed form, click the option that you want in the After submit list.
  • To create a custom message to indicate if the form was successfully submitted, select the Use custom messages check box, and then type your messages in the On success and On failure boxes.

 Tip   Use a message in the On failure box to tell users what to do if they cannot submit their form. For example, you can suggest that users save their form and contact someone for further instructions.

  • If you do not want to display a message after the user submits a form, clear the Show success and failure messages check box.

Top of Page Top of Page

 
 
Applies to:
InfoPath 2007