InfoPath Dev

Scenario:

I use Infopath as a GUI for SQL Server 2005.
I have 2 tables: Customers and Sales.
Sales is a primary connection. The sales data from my form are saved in the table Sales, when i click "Submit".
Customer name must be available from a dropdown field. The content of this dropdown field I get from the Table Customers (but I don't need all Customers, I want to have a possibility to select them).

Is it possible?

I think you should be able to use concepts presented from this HowTo to achieve such a feat:

http://infopathdev.com/howto/tutorials/default.aspx?i=0bb37b4de44348408680a53a449f2353

Hi Matt,

in your article you have shown, how to select data using a primary connection. But I have two tables :-(. The second one is in the secondary connection. Is it possible to filter data in the secondary connection?

Too bad you never got an answer to the secondary data source filtering issue. I have the same question! So far it seems the only way to do this is by writing a T-SQL stored procedure that takes search criteria as parameters or design a Web service that does the same thing. I'm working on trying one or both of these approaches. (See:http://support.microsoft.com/Default.aspx?kbid=827007 for basics on calling a stored proc from an InfoPath form).

InfoPath is a strange product. Some tasks are amazingly simple. Then you expect something similar to be relatively easy and WHAM! you're on the slopes of Mt. Everest (steep for anyone not already experienced with XML, scripting and/or managed code).

I came across this problem when I was building my first form. The way I got around it was I created fields in my Main datasource that matched the fields in my secondary DS and connected them to the secondary DS. From there I was able to do all the filtering I wanted.

I'm new and I share your sentiments Mike - I hope this has helped.

Chris in Dallas

Wow, that was a world record quick response! Thanks Chris. I left out an important dimension to my case. The secondary data source connects to a SQL Server table with over 20,000 records. Using it to populate fields in the main data source is impractical for the user and brings InfoPath to it's knees performance wise. Without a custom script calling a stored proc or Web Service it seems InfoPath can only bring the entire table over before allowing you to use filtering criteria against the data set.

This would be so easy to solve if we could go to Edit SQL in the data connection and write a paramaterized query that connects to query fields on the form. If I'm wrong about this, I'll be happy to find out!

Sorry for the delayed response. The HowTo I posted demonstrates how to do all of this, the key piece is this:

var oQueryId = XDocument.DOM.selectSingleNode( "/dfs:myFields/dfs:queryFields/q:Customers/@CustomerID" );
oQueryId.text = eventObj.Source.selectSingleNode("@CustomerID").text;

Notice that I am setting the value of the CustomerID node in the queryFields group. When this is set and the Query() method is called it will only return one row!

You don't have to make nodes in your Main DOM to alter the nodes in the queryFields group. Just open the data source task pane, expand to the field you want to query on, right-click and drag it into the view. Viola!

MikeG, if you want to edit the actual SQL command that the adapter uses, try something like this:

ADOAdapterObject.Command = "SELECT * FROM MyTable WHERE id > 5 AND id < 1500";
ADOAdapterObject.Query();

Just be careful with that Command object - you're using JetSQL not the normal SQL.

Matt, as you can see I just started posting here and I like this forum already!

I understand a static query filter can be used in the adapter SQL, but we need dynamic query parameters. To be more clear, users will need to enter a customerID OR First and Last Name OR SSN in order to pull one record or just a couple of records at most from the secondary data source. Thanks, I didn't realize this is JetSQL!

Your How To Article looks promising for what we need. I'll have to get a cup of coffee (or two) and experiment with this before I'll understand how to convert and extend your solution for our application where the queryFields are from a different database. If that works I'll be digging around your fantastic forum to figure out how to populate certain Main dataFields from matching secondary queryFields.

I have the InfoPath XML schema reference, but it's all still a bit overwhelming at the moment. (Sorry, I should probably be posting in the newbie section.)

We decided to create a standalone form for querying patient records because it's a needed function on its own. Since I'm no longer using a secondary connection, I'll summarize and then move out of this thread.

I used a combination of Matt's How To "Create a Multi-View Master/Detail using a Query Filter" (very helpful!) and Microsoft's article on using stored procedures with an InfoPath data connection. Now queries quickly return a single record (key search) or a relatively small set of records (first and last name search) instead of choking on the entire 20,000+ record table.

Just two questions remain:
(1) How to gracefully detect and provide a msg when no records are returned. Currently all the controls disappear (until my New Search button is clicked) and there is no msg. I'd rather leave the text boxes, etc. intact.

(2) If possible, we'd like to populate fields (e.g. name and address) in other forms by calling this form and transferring it's query results into corresponding fields by pressing a "Copy" button. Is that even possible?

Thanks for any advice or direction on where to go for these issues.

Mike do you realy need to copy the data into another form, or could you get by with another View in the same form. If this would work for you, take a look at www.wssdemo.com:8080/Pages/infopath_lookup_of_list_data.aspx for an example showing how to use Rules to populate other fields from a dropdown.

quote:

---

Originally posted by Matt Faus

Sorry for the delayed response. The HowTo I posted demonstrates how to do all of this, the key piece is this:

var oQueryId = XDocument.DOM.selectSingleNode( "/dfs:myFields/dfs:queryFields/q:Customers/@CustomerID" );
oQueryId.text = eventObj.Source.selectSingleNode("@CustomerID").text;

Notice that I am setting the value of the CustomerID node in the queryFields group. When this is set and the Query() method is called it will only return one row!

You don't have to make nodes in your Main DOM to alter the nodes in the queryFields group. Just open the data source task pane, expand to the field you want to query on, right-click and drag it into the view. Viola!

MikeG, if you want to edit the actual SQL command that the adapter uses, try something like this:

ADOAdapterObject.Command = "SELECT * FROM MyTable WHERE id > 5 AND id < 1500";
ADOAdapterObject.Query();

Just be careful with that Command object - you're using JetSQL not the normal SQL.

---

quote:

---

Originally posted by Matt Faus
var oQueryId = XDocument.DOM.selectSingleNode( "/dfs:myFields/dfs:queryFields/q:Customers/@CustomerID" );
oQueryId.text = eventObj.Source.selectSingleNode("@CustomerID").text;

---

I'm trying to use code just like this. The problem is that I can use it just fine for grabbing the values and messing around with stuff in the query and data directories of my primary connection, but I can't get it for other stuff.

My set up is this:
Main
   myFields
      queryFields
      dataFields
      my:userChoices
         temp


Using code like you've posted, I've used fields in queryFields and dataFields just fine, but I've been unable to properly grab temp.


Any suggestions would be greatly appreciated.