Filtering Secondary SQL data connection with value from Main data connection

Question

I am trying to develop a form in InfoPath 2010 for displaying a list (Repeating Table) of data from a SQL table. My goal is to display not only the included table columns but values from Foreign Keys. For example, I do not want to be displaying ProductId in my table of Order Details but I want the ProductName.

I initially used the Main data connection to retrieve all the data but I found the performance just way too slow when it came to reconciling the FK values. (Normally you would do this using a JOIN in your query, but using SQL Profiler I see that InfoPath issues separate SELECT queries (as part of a batch) and then does the "joins" all on the client.)

My next thought was to leave just the one table for the Main connection (to be used for Inserts and Updates) but populate the Repeating Table using a query-only Secondary data connection where I can use a view that has my JOINS. Adding a new "Run Query" button with a "Query using a data connection" action works as expected.

With the default Database Template and Main data connection, the data source is defined as having queryFields and dataFields. The queryFields can be used by the user to set filters on the Main data connection which is then shown via the dataFields. (Please correct me if I have any of this wrong or am using incorrect terms.)

My problem comes with wanting to define filter values for the Secondary data connection using the queryFields from the Main data connection. Several postings I have read stated that the filter can be set using a Rule. The instructions are as follows

1. Select the button that will execute the query
2. In the Rules panel, click New -> Action
3. For "Run these actions", click Add -> "Set a field's value"
4. In the Rules Details dialog, click the Field Picker (??) button next to the Field textbox
5. Select the Secondary data connection
6. Drill down to the appropriate field (the field that will be compared when filtering) under the dataFields group and select it, then click OK.
7. Click the fx button next to Value textbox
8. Click Insert Field or Group button
9. Select the Main data connection
10. Drill down to the appropriate field (the field that holds the value to be filtered on) under the queryFields group and select it, then click OK.
11. Click OK, then OK again.
12. Go Preview the form.

However, when I preview the form and click the query button I get the following generic and unhelpful error

Some rules were not applied.
The parameter is incorrect.

For step 10 I also tried using the corresponding field in the dataField group but I get the same error. I also read one posting that mentions clicking the Filter Data button when selecting the field in step 10 but that does not work either.

Can anybody tell me what I am doing wrong? Thanks.

Note: It is my understanding that direct SQL data connections are bad (although I am not sure why) and that creating a Web Service is a better way to go. Right now I am trying to test this (InfoPath) as a proof of concept to see if we can even use it. I will probably go the Web Service path in the long run but I would guess that my dilemma would be the same. (Updates and Inserts on the main data connection and the list of data (with FK values included) on the Secondary with a filter being passed to the Web Service.)

Answer 1

After speaking with Microsoft technical support it turns out that in order to have filter values sent back to the data source, the specific data source needs to support queryFields. It also turns out that even if a specific data source (like SQL server) supports queryFields when used as the Main data connection it does not necessarily generate those fields when used as a Secondary data connection.

The solution was to use Web Services which provides the queryFields for any type of connection.

Answer 2

Go for VSTA tool and do some coding. You can select the value which you are using to filter and after that run the query with "where" parameter being value inside your data field.

Sample code for your reference :

XPathNavigator myNavID =
                CreateNavigator().SelectSingleNode("/dfs:myFields/my:TempID",
                NamespaceManager);
                string ID = myNavID.InnerXml;
                AdoQueryConnection myAdoQueryConnection =
               (AdoQueryConnection)(this.DataConnections["Main connection"]);
                myAdoQueryConnection.Command =
               "select POCData,ID from dbo.POCtable as POCtable where POCData = " + "'" + ID + "'";
                myAdoQueryConnection.Execute();