InfoPath Dev

I have a form that uses a data connection to a sql server. That data connection calls a query that is used to populate the display/value properties of every drop down menu I have on the form through filtering the results based on the drop down type returned.

For simplicity lets say the form has 10 drop downs that use that data connection. I have a user that opens the form and on occasion 2 of the drop downs have no values listed in the drop downs but the other 8 are populated just fine. This issue is unique to this single user and I can't reproduce it and every time this user shares their desktop with me, opens the form, it opens correctly and the drop downs have data. The user reports that when it happens, they do not receive an error message while opening the form.

I'm assuming the data connections work in one of the following ways:
1) When the form is opened, it executes the query in the data connection 1 time and then each of the drop down menus filter that result set based on the settings for each drop down menu.
2) Each drop down menu calls that data connection individually when the form is opened so the query in the data connection is executed for each drop down instance on the form (ie 10 times in this example)

If it's the first, I'm wondering what would cause those drop down menus to be blank when they are using the same dataset returned as the other drop downs that have values just fine?

If it's the second, that means the data connection is failing for some of the drop downs on load. Is there a way to have a notification if a data connection fails? Is there a way to retry the data connection if it fails?

Right now I'm having the user that is having the issue, scroll down to the problematic section every time the form is opened to make sure the data in the drop downs have data prior to filling out the form and if they do proceed as normal, if they don't, close and reopen the form.

This form has been in use for over a year now and this has never been a problem before so any information you can provide would be much appreciated.

Thanks in advance for your help!

Hello,

Do all data connections point to the same server? I'm guessing that the user has no permissions to the data connections that are being pulled in the 2 dropdowns. You might want to check the user's permissions (SQL/SharePoint) and compare it with yours or others who can access it, see if there's any difference.

Jo-Anne

I have 1 data connection that is used by all of my drop downs.

My data connection has a single query. Without getting too detailed, the query looks something like this:

Select Col1, Col2, Col3
From Table

Then my drop down properties uses the result set and filters the result set based on col1 = "some text", and sets Display = col2 and Value = col3.

I store the credentials to the database with the form.

Being that they all use the same data connection, my assumption is that if 1 drop down is filled in, they all should be filled in.

I was trying to understand a bit more how data connections work when a form is opened in a more general sense. I was wondering if I have 10 drop downs that point a single data connection, is that query executed once when the form is opened, and then the result set is filtered for all of my drop downs, or is the query ran for each of the 10 drop downs?

Hello,

It depends - if you've set the data connection to query on load (that is, if you've checked "Automatically retrieve data when form is opened" when creating the data connection), then it is going to load everything at once when the form is opened. But since you said you have filtered dropdowns, I assume it's cascading. If that's true, then the query only happens depending on certain selections in the fields. Which one are you doing?