(migrated post from November 21st 2006) 

There are many data sources that can be used in Infopath. One of the most interesting is definitely to load and save the data of a form to a webservice in form of an ADO.NET Recordset.This gives you the greates freedom in processing the data on both requests. I am not going into details on how to load and save data from/to a webservice, this is documentated already enough (see here f.e.: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_ip2003_tr/html/odc_INF_Lab_09.asp).

However, have you ever wondered how to work with a highly relational database schema as the datasource of your infopath form? Example: You want to gather project data. So you have (amongst others) a Projects-Table and some child tables (in this case: ProjectLocalSAPNumbers and ProjectBenefits) where you have a one-to-many relationship defined.
So, you create a new dataset, add the three (or more) tables and build relationships. Then you import the datasource to infopath.

Problem: In InfoPath you will get three nodes in the XMLSchema-Tree on the same level:

·         T_Project

·         T_ProjectLocalSAPNumber

·         T_ProjectBenefits

However, this is not at all what you’ve expected. Try now to create a form that respects the relationas in the database, meaning that the goals are a child entity of the project. It is simply not possible. You would have to place all project properties first and then all goals etc.

Solution

There are two relatively simple things you have to do on your dataset to enable infopath to interprete the schema correctly:

·         Set the attribute “IsNested” on the relations

·         Take the child element and drag it onto the parent table (Project). Now the child element is associated directly with the project element (see screenshot)

When you import your dataset from the webservice now to infopath, you’ll get what you want!