Pulling CRM Data into InfoPath 2007 Browser Forms

Article
05/05/2014

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary: Learn how to pull data from a CRM database and display that data in a Microsoft Office InfoPath 2007 browser form. You’ll learn how to create and publish a UDC file that defines the properties of the data, and then how to add a data connection that references the UDC file, to your form template.

Office Visual How To

Applies to: 2007 Microsoft Office System, Microsoft Office InfoPath 2007

David Gerhardt, 3Sharp

May 2007

Microsoft Office InfoPath 2007 allows you to pull data from different back-end sources (for example, a CRM database) into form files without custom code. Within the form designer in Microsoft Office InfoPath 2007, you can use the Data Connection Wizard to accept data from an XML document, a Microsoft SQL Server database, a Web service, a SharePoint library, a SharePoint list, or a Universal Data Connection (UDC) file.

A UDC file defines properties for an existing data connection. You can store UDC files in a data connection library of the corresponding SharePoint site collection or in a central forms library in Office InfoPath 2007 Forms Services. You can use connection files stored in a SharePoint library with any InfoPath 2007 form templates. This enables you to make modifications to external data sources without updating and redeploying form templates.

You can download a UDC File Authoring Tool that was published on the Microsoft InfoPath team blog. With the authoring tool, you can create a UDC file that retrieves customer information from the AdventureWorksDW SQL Server 2005 database. Upload the file to a data connection library and include it as a data connection in the status report form template that comes with Office InfoPath 2007.

Creating the UDC File

After you download UDC File Creator.xsn from the InfoPath team blog, start it in design mode and republish the form template to a new location. Then, double-click the new form template to define the data connection properties for the DimCustomer table in the AdventureWorksDW database.

To create the UDC file

In the Name box, type Customers.
In the Description box, type Customers in the AdventureWorksDW database.
Click the Type list, and then click Database.
In the Connection String box, type Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=Web_Application_Data_Source;Use Procedure for Prepare=1;Auto Translate=True;Workstation ID=Name_of_Database_Server;Initial Catalog=AdventureWorksDW.

Note

Replace Web_Application_Data_Source with the Web application data source and Name_of_Database_Server with the database server name.
In the Query box, type SELECT CustomerKey,GeographyKey FROM "AdventureWorksDW"."dbo"."DimCustomer" WHERE CustomerKey LIKE '1101%'.
Click the Save icon on the toolbar.
In the Save As dialog box, navigate to a save location on your local drive, type Customers in the File name box, and click Save.

The following example shows the XML that is created with the UDC file authoring tool.

<udc:DataSource MajorVersion="2" MinorVersion="0" xmlns:xsi=
   "http://www.w3.org/2001/XMLSchema-instance" xmlns:udc=
   "http://schemas.microsoft.com/office/infopath/2006/udc" xmlns:my=
   "http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-03-23T08:18:13" xmlns:xd=
   "http://schemas.microsoft.com/office/infopath/2003">
    <udc:Name>Customers</udc:Name>
    <udc:Description>Customers in the AdventureWorksDW database</udc:Description>
    <udc:Type MajorVersion="2" MinorVersion="0" Type="Database">
        <udc:SubType MajorVersion="0" MinorVersion="0" Type=""></udc:SubType>
    </udc:Type>
    <udc:ConnectionInfo Purpose="ReadOnly" AltDataSource="">
        <udc:WsdlUrl>http://</udc:WsdlUrl>
        <udc:SelectCommand>
            <udc:ListId></udc:ListId>
            <udc:WebUrl>http://</udc:WebUrl>
            <udc:ConnectionString>Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=
               moss.litwareinc.com;Use Procedure for Prepare=1;Auto Translate=True;Workstation ID=
               MOSS;Initial Catalog=AdventureWorksDW</udc:ConnectionString>
            <udc:ServiceUrl UseFormsServiceProxy="false"></udc:ServiceUrl>
            <udc:SoapAction></udc:SoapAction>
            <udc:Query>SELECT CustomerKey,GeographyKey FROM "AdventureWorksDW"."dbo"."DimCustomer" 
              WHERE CustomerKey LIKE '1101%'</udc:Query>
        </udc:SelectCommand>
        <udc:UpdateCommand>
            <udc:ServiceUrl UseFormsServiceProxy="false"></udc:ServiceUrl>
            <udc:SoapAction></udc:SoapAction>
            <udc:Submit></udc:Submit>
            <udc:FileName></udc:FileName>
            <udc:FolderName AllowOverwrite="0">http://</udc:FolderName>
        </udc:UpdateCommand>
    </udc:ConnectionInfo>
</udc:DataSource>

Uploading the UDC File to a SharePoint Site

From a data connection library in a SharePoint site collection that corresponds with your form template, upload the UDC file.

To upload the UDC file

From your data connection library, click Upload, and then click Upload Document.
On the Upload Document: Data Connections page, click Browse.
In the Choose file dialog box, navigate to the location of Customers.xml (the UDC file you created in the previous section) and double-click it.
Click OK.
On the Data Connections: Customers page, review the UDC file properties and click OK.

Adding a Data Connection in the Form Template

Start Office InfoPath 2007 and save the status report sample form template locally. The projectName node in this form template is bound to a text box, labeled Project. To change that text box to a drop-down list box, right-click the control, select Change To, and then click Drop-Down List Box. Then, add a Customers data connection, which you will use to pull values for the drop-down list box.

To add a data connection in the form template

Double-click the Project drop-down list box.
In the Drop-Down List Box Properties dialog box, click Look up values from an external data source.
Click Add.
In the first page of the Data Connection Wizard, click Search for connections on a Microsoft Office SharePoint Server and click Next.
In the next page of the Data Connection Wizard, click Manage Sites.
In the Manage Sites dialog box, click Add.
In the Site Details dialog box, in the URL box, type the URL of the SharePoint site where the data connection library is located and click OK.
In the Manage Sites dialog box, click Close.
In the Data Connection Wizard, click the Site list and click the URL you just added.
Expand the name of your data connection library and click Customers.xml.

Figure 1. Selecting the UDC file
Click Connection Options.
In the Connection Options dialog box, note how you can retrieve connection settings from a local data connection library or centrally managed connection library and click Cancel.
In the Data Connection Wizard, click Next.
In the next page of the Data Connection Wizard, click Next.
In the last page of the Data Connection Wizard, click Finish.

Changing the Project Drop-Down List to Use the New Data Connection

Modify the Project drop-down list box so that it looks up values from the new Customers data connection.

To change the project drop-down list to use the new data connection

In the Drop-Down List Box Properties dialog box, click the Select XPath button, located to the right of the Entries box.
In the Select a Field or Group dialog box, double-click d:DimCustomer.

The Project drop-down list box will now display the values of the CustomerKey column in the DimCustomer database table.

Setting the Department Box Value Based on the Project List Value

You can use rules to set other form fields based on the Project drop-down list box value. Here, you set the Department text box value to the corresponding GeographyKey column value in the DimCustomer table.

To set the department box value based on the project list value

In the Drop-Down List Box Properties dialog box, click Rules.
In the Rules dialog box, click Add.
In the Rule dialog box, click Add Action.
In the Action dialog box, click Select XPath.
In the Select a Field or Group dialog box, double-click departmentName.
In the Action dialog box, click Insert Formula.
In the Insert Formula dialog box, click Insert Field or Group.
In the Select a Field or Group dialog box, click the Data source list, and then click Customers (Secondary).
Expand dataFields and d:DimCustomer, click :GeographyKey, and click Filter Data.
In the Filter Data dialog box, click Add.
In the Specify Filter Conditions dialog box, change the first list value to CustomerKey.
Click the third list and click Select a field or group.
In the Select a Field or Group dialog box, click Data source, and then click Main.
Double-click projectName.

Figure 2. Specifying a filter condition
Click OK until all dialog boxes are closed.

Publishing the Form Template to a SharePoint Site

Because there is no code in this status report form template that is compatible with InfoPath Forms Services, you can publish directly to a SharePoint site.

To publish the form template to a SharePoint site

Click File, and then click Publish.
In the first page of the Publishing Wizard, click To a SharePoint server with or without InfoPath Forms Services, and then click Next.
In the next page of the Publishing Wizard, type the URL for the SharePoint site in the Enter the location of your SharePoint or InfoPath Forms Services site box, and then click Next.
In the next page of the Publishing Wizard, note that the Enable this form to be filled out by using a browser option is selected and click Next.
In the next page of the Publishing Wizard, click Create a new document library and then click Next.
In the next page of the Publishing Wizard, type a document library name in the Name box, and then click Next.
In the next page of the Publishing Wizard, note the promoted columns and click Next.
In the next page of the Publishing Wizard, click Publish.

Starting a Browser Form to See Customer Data

After you publish the status report form template to the SharePoint site, test your changes by starting the form in the browser.

To start a browser form to see customer data

On the last page of the Publishing Wizard, click Open this form in the browser.
Click the Project list and click 11010. Note the value in the Department box.
Click the Project list again and click 11018. Note the updated value in the Department box.

InfoPath 2007 allows you to build forms that pull data from external sources (for example, a CRM database) without custom code. With a UDC file, you can store data connection information in the data connection library of the corresponding SharePoint site collection or in a centrally managed connection library. You can then use UDC files stored in a SharePoint library with the InfoPath form templates. To use UDC files in your browser form solutions:

Use the authoring tool published by the InfoPath team to create a UDC file. After the file is created, upload it to a data connection library in your SharePoint site collection or to the Manage Data Connection Files page in Central Administration, whichever is applicable.
Add a data connection for the UDC file to your InfoPath form template. Use the Connection Options dialog box in the Data Connection Wizard to choose the way your form links to the UDC file.
Publish the form template to the SharePoint site collection. If the UDC file is in a centrally managed connection library, you need to have administrator rights to deploy your form template.

Watch the Video

Video Length: 00:9:28

File Size: 06.07 MB WMV