InfoPath Dev

I'm creating a form to query an Access Database and I'm having trouble understanding the data connections. I cannot seem to be able to do anything with the "Main Connection". Let me explain.

I simply want to create a drop-down control to display choices and then submit the query. So I started from a template designed for this purpose. I set my main data connection (receives data only) and selected the tables in a specific order and assigned the relationships between fields. Then I added a control bound to my Query fields. Then in the control properties, I selected the the option "Look up values in the form's data source" and in "Entries", I selected the appropriate table and finished up by selecting a field for "Value" and "Display name". However, when I run the form, the drop down is empty.

So then I tried setting up another data connection and this time, selected just the table that has the data I want, instead of all tables like I did in the Main Connection. I finished up by using the option "Look up values from an external data source" instead, selected the fields for "Value" and "Display name" and this time it worked. So I'm quite confused as to what the main connection does. Why cannot I retrieve data from it? Am I doing the right thing with setting up specific data sources for my lookups or should I be able to retrieve data from my main connection? Thanks for any help/direction you might offer.

Hi --

The Main Data Connection doesn't retrieve data on load, so unless you have something else set up to run the query, your ddl with values from the main data source will be blank.

You can add a rule to the form to query using the Main Data Connection. You may or may not want to do this -- if your data table has a lot of columns, you'll be returning a lot of data you don't need by using your Main Data Connection for your drop down list. You might be happier with your form's performance using a secondary data connection to the same table that only returns the columns you need for the value and display for your dropdown.

Let me know if I haven't been clear enough or if you need more detail, okay?

So the main data connection is actually queried when you click on the Query button only? I guess that makes sense when it comes to launching the form or it could take too long to just bring it up on screen. Is there a general rule as to what the purpose of the Main Connection is as compared to the Secondary? Thanks again for your help.

Well, the Main Data Connection to SQL or Access is used when you want to create a form that can query from and submit to a database. InfoPath doesn't have any native support for creating secondary submit connections to a DB. You can, of course, build or buy a web service for submitting your form data to a DB if the form was not originally based on a DB.

Now, in your instance, you also have data that could be accessed from that Main Data Connection for your drop downs, but, liked we talked about, you'll probably have better performance if you create a secondary data connection to pull back the values just for the drop down, rather than return all the columns from your Main Data Connection.

This link to the InfoPath Team Blog has some additional information on data connections that may be of interest or help.

Thanks a lot for that link. Now I have enough reading to keep me busy this evening :)

Just as I begin to warm up to data connections, I get confused once again :(

When I started playing around with Infopath for the first time, I did so by creating a form to query an Access database. The database was my main connection and it showed query fields and data fields. So I created some combo boxes bound to these query fields and the data that the user was able to pick in each one was populated through secondary data connections (filtering based on the previous combo box value). Then I simply added a repeating section bound to the main data source (to the data fields) and when the Query button was clicked, this section populated with the results. The Query button was part of the template, but when I checked its properties, the Action was set to "Run Query".

In my "second phase" I decided to now place the database on Sharepoint. I actually rebuilt a new database with better structured tables and moved it to a Sharepoint site. The tables were made into lists and these were linked into a database that was placed also on the Sharepoint site. So now we have the flexibility of creating custom forms, reports and queries in Access, while still being able to edit the lists in Sharepoint. When I started a new form design, I found that the Access template cannot be used as the database does not contain any tables (only links to the lists). So I decided to start designing from a blank template.

I noticed that the main data source is bound to an XML schema and I no longer have "query fields" as in my first encounter. So the first thing I did was to create two Receive data connections (two related Sharepoint lists). Then I inserted a button control to submit the query, but that option does not exist. I tried several things which I knew deep down they wouldn't work (you know the feeling....but you try nonetheless!). I added a combo box and bound it to a field in the main data source (I'm almost sure this isn't right, but I have no idea what else to try since I don't have query fields available). Then I added a repeating section bound to the second secondary data source (doesn't refresh when form is opened as I want it to populate with the correct queried data once the button is pressed). But now I'm stuck what to do next, so the question is....how do you go about setting things up so that once the user selects something in the combo box and clicks a button, the repeating table populates with the queried/filtered information? I'm at a loss as this form template is different than the one I started fiddling with and there are no query fields available. Any direction would be greatly appreciated!

Thanks!

Hi, again:

SharePoint, for being web based, can be a little tough to work with as a data connection InfoPath. Really, when you create a data connection to a SharePoint list, it is an all or nothing proposition (unless you'd like to write or purchase a Web service to help you -- here is a bit more info on writing a wrapper). So, if your list is of a manageable size (doesn't club your form's performance over the head) you can use the receive connection, add a repeating table that is bound to it, and filter the table based on the combo box value. I'd suggest you take a look at the Travel Request sample form that ships with InfoPath 2007 for an example of that -- there is a view called "Merge & " something or another that has some filtering of a table based on a list selection. On the plus side, you'll be able to do away with your button. On the minus side.... well, like I said, pulling back an entire ShP list can smack you down for performance, as well as having the list limitations -- as I recall, if your list only shows 100 items, that's all you'll get back in your form (I could be wrong here... but that is what I remember....)

Thanks again. I'll consider filtering the repeating table instead and once the lists get too large, then we'll have to do it "the right way". Since this topic is starting to veer off in a different direction, I decided to post a new thread in the Sharepoint Integration forum.

http://www.infopathdev.com/forums/p/9259/32747.aspx#32747

I'm also beginning to rethink using sharepoint lists as I'm not gaining anything out of them. After all, we're still probably going to input data through Access forms, so we could easily put the database on sharepoint so anyone get get to it and make edits, and also any Infopath forms on the Sharepoint site can see the database and thus we could safely use it as our data connection/s.