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
- Select the button that will execute the query
- In the Rules panel, click New -> Action
- For "Run these actions", click Add -> "Set a field's value"
- In the Rules Details dialog, click the Field Picker (??) button next to the Field textbox
- Select the Secondary data connection
- Drill down to the appropriate field (the field that will be compared when filtering) under the dataFields group and select it, then click OK.
- Click the fx button next to Value textbox
- Click Insert Field or Group button
- Select the Main data connection
- 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.
- Click OK, then OK again.
- 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.)