Pulling CRM Data into InfoPath 2007 Browser Forms
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.
Applies to: 2007 Microsoft Office System, Microsoft Office InfoPath 2007
David Gerhardt, 3Sharp
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
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
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
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
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
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
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
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:
Video Length: 00:9:28
File Size: 06.07 MB WMV