InfoPath Data Connections: Part 1

InfoPath Data Connections: Part 1

This article is part of a series of documents explaining the Data concept in Microsoft Office InfoPath. This particular article is an overview of how Data Connections work in InfoPath. The intended audience is those who are new to InfoPath and those who use InfoPath, but have not built up knowledge in the way Data Connections work. For more exhaustive information regarding specific types of data connection, see the Links section at the end of this article.

Terminology

Definitions of certain terminology in this paper are as follows:

InfoPath client – the software program named "Microsoft Office InfoPath"

Designer – The person who is using the InfoPath client to create/design a form template

Form template – the XSN file that is created with InfoPath client and used to enter data by the form filler

Filler – the person who is putting data into the form

Form – a form that is either filled out or in the process of being filled out by the filler. Also known as the InfoPath XML file.

Document Object Model (DOM) – XML structures in the form. In InfoPath, there is a Main DOM on which the form is based and Secondary DOMs which hold data received from Receive Data Connections.

Background

The purpose of using an InfoPath form is to collect structured data. This data structure is represented by a schema (XSD) which defines the data fields and the hierarchical structure of the data.

When using InfoPath client, data is accessible through data connections. These data connections are defined in the form template and used at runtime. During design of the form, the form designer will create the data connection. The designer must choose to either create new settings for a data connection or search for existing connections previously defined by an admin on a SharePoint server. In general, the data connection paradigm in InfoPath 2003 and 2007 is such that data connections are grouped into one of two types – submit connection type or receive (query) connection type. Receive connection types have a one-to-one relationship with secondary DOMs.

Submit Data Connections

Consider this scenario. The form filler places data into all of the fields on the form, presses a button labeled “Submit my data” and the data is pushed to some listener where it can be stored or processed.

As simple as this scenario is, it must be configured by the form designer through the Submit type of data connection. After creating a submit data connection, the designer can apply the data connection to a user action – in this case, the button labeled “Submit my data”. When the filler executes that action, data in the main DOM of the form is transmitted to the target of the data connection as a submission.

Submit data connections can currently bind to the following destinations:

Web Service – Using a data connection bound to the Web Services adapter, data is submitted according to the specifications of the web service.For web services that expose the type System.Dataset, InfoPath will track changes as the user edits the data. When the data is submitted to the web service, the changes are sent using ADO.NET Diffgrams.

SharePoint document library – Data is submitted to a SharePoint document library by using a data connection bound to the SharePoint Document Library adapter.

E-mail – Using a data connection bound to the SMTP E-Mail adapter, this enables the submission to an e-mail address. The settings of the data connection can specify whether to submit the entire contents of the form as an attachment or just the current view as the body of the e-mail message.

The form hosting environment, such as an ASP.NET page or a hosting application (2007 ONLY) – Using a data connection bound to the “Submit to host” adapter, this raises a submit event to the host. The host is then responsible for handling that event and retrieving the data from the form. See http://msdn2.microsoft.com/en-us/library/ms778201.aspx for more info.

Web Server (HTTP) – This is one exception where the submission is not bound to an adapter as a data connection, rather, the settings of the submit options on a control specify an HTTP URL. HTTP Post is used to submit the data in the form to that URL.

Database – Lastly, if the form is initially designed from a SQL Server or Access database, a submit action can be created to push the data back into that database. Note: the database option has some caveats in that large binary types cannot be submitted from InfoPath directly; these require a web service in front of the database.

Receive Data Connections

One of the major problems in filling out forms is accuracy of data. For instance, if someone was replying to a survey regarding what their favorite programming language is, they could have a text box to type it in. Now imagine that there are 50 respondents to the survey, each trying to explain in a text string what their favorite language is. Consider for a moment that it is C#. They could respond with any of the following and be just a correct or valid: C#, C-Sharp, Csharp, C, C# .NET. Obviously, allowing this type of data entry will create problems when the survey is interpreted.

This type of scenario is a good reason to use a Receive data connection. What if the form designer was to pre-populate a drop-down control with specific choices which the filler could choose as their favorite programming language? The designer could create a list such as: VB, C++, VB.NET, C# and would thus ensure that the filler would be able to be successful in filling out the form with valid data that is consistent across all of those who respond. The person who aggregates the responses from the form will also be able to ensure that they have interpreted the data correctly and completely.

To configure this scenario, the form designer now needs to create a Receive connection type. A receive data connection allows a query to be made to get data from an external source and return it to the form. External data is placed into the form as a secondary data source (represented as another DOM). Controls can then be configured to use these fields. Also, the designer can view the additional data sources by viewing the Data Source task pane and using the drop down to switch between data sources. If the dropdown is grey, then there is only one data source in the form.

Receive data connections can currently connect to the following sources - all of these are supported in InfoPath 2003 and 2007:

XML Document – This includes any URL that would return an XML document (For instance, an RSS feed)

There is no way to do this without writing code. The two methods of doing this with code are:

1. set up the connection as a Web service call to the lists web service on SharePoint. Use custom code in your form template to set the URL of the Web service at runtime based on the URL the form was opened from, and then execute the Web service connection.

2. When the form is hosted in the XmlFormView control, either in a client application or on MOSS, you can set up your data connections to specify that a portion of the serviceURL of the Web service can be dynamically replaced at runtime. This requires adding an element to the extensions section of manifest.xsf:

The xsf:relativeQuery element specifies a substring of the ServiceURL which will be replaced at runtime by the value of the DataConnectionBaseUrl property on the XmlFormView control. Your hosting page or hosting application needs to set the DataConnectionBaseUrl property before the form is loaded.

This is difficult to do for a SharePoint list, since we use the GUID of the list, rather than the URL, to identify the list to query. This may change in a future version.

Are you looking for the ability to query relative to the SharePoint site, or the SharePoint site collection? Knowing this would help us understand how best to implement the relative URL functionality if we add it in a future release.

Hi I have set up a access database on a doc library and a infopath form based on this db published to a nearby folder. When I go to fill out the form, and it opens, I hit submit. But then it gives me a JET DB initilization form with login of admin and a blank pass. When I hit ok, it gives me a error saying

infopath The Microsoft Jet database engine cannot open the file '\\207.245.47.37\sites\Safety_and_Training\Experimental\db1.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

For some reason, once or twice its randomly been able to submit succcessfully but I havnt been able to reproduce the situation. Are there files that i am missing? any permissions that I need to check? The access database only has a admin and no password account. PLEASE HELP ME ive been working on this for ages! If you know a solid workaround with little programming let me know.

I need to make a form that should work in the browser so i lost many control options and I need to get records from the DB according to the logged user. How to be able to bind non repeating controls to datasource ???? Any Guidance would be appreciated. Thanks in advance.

New to this blog. I have an infopath form tied to an access database. Only thing is, any time I add a control, it forces me to bind it to the database. Most times, this is ok. other times, I am trying to create the optional sections.

We have a MOSS site set up for a client, it has a sub site for each of their clients and then another site below client for each project. At the project level there is an infopath form which has data connections to lists at both the other levels. Our client needs to be able to set up new projects and clients at these levels, we are allowing this using templates. This does however mean the data connections on the infopath form need to be relative. Could you elaborate on what you have written above as to the best wayt this could be achieved please ?

I am using InfoPath 2007. When I set up a data connection to receive data from a SharePoint list, I have run into a restriction that I've not seen documented anywhere, and so I wonder if there is a way to do what I need.

The SharePoint list I am wanting to use has a single column, type people or grep, which has the "allow multiple selections" property set. This is so that the column can contain 1 to many people selected for that particular row, without having to create a limiting number of columns.

When I try to create the data connection in InfoPath 2007, the column is not displayed as one that can be returned.

Once I turn off the "multiple selection" property, the column becomes available.

Is there some alternative method to accessing the data?

If not, I sure hope this is in the works for a near future patch release...

I have a Infopath form designed and have a data connection to the sharepoint. the form consist of a feilds of user information which submits data to a data library in sharepoint.

Now I want to know how can i store multiple copies of same form but with of different users and with that form name as of the name feild in the form entered by the user.

so if I enter my details in form and press submit then the form should store on sharepoint library with my name(I have connection established).

please let me know if any can help me, thanks in advance.

Maureen

22 Sep 2010 10:13 AM

I am trying to connect to a SQLsever view, and Infopath crashes repeatedly!

Is this a BUG in Infopath? Pretty severe limitation.

I can connect to this same view, database connection, from Excel.

Michael Cartwright

29 Dec 2010 11:22 AM

Patrick, great information. I have a 2007 InfoPath that links (submit button)to an 2007 Access database and works well for users with Office 2007. However, users with Office 2010 are not able to "Submit" and receive an error that that the form cannot connect to the datebase. I read that Infopath 2010 was supposed to be able use 2007 Submit buttons, but its not working here. Any suggestions?