InfoPath Dev

Hi again.

I have an info path form that is now configured to use QueryDB to pull some data from a SQL database and utilize it for drop-down options in the form. It works fantastically in info path, but this form is a browser form and I'm having issues getting it deployed.

I have configured all the DBXL web service data connections to be UDCX files and have deployed them to a data connection library on the same site as the form, where they are all approved in the library.

The form is able to be uploaded to the Forms Service without any issue, but opening the form in the browser causes the form to stop at Sending Data to Server without any errors or resolution of the query. The server also pegs 100% CPU while this is causing, again without any indication of the problem.

Can anyone help me on what's my missing piece?

Thanks.

Hi-

How much data is being pulled in on load?

Thanks.

Hey Ernesto,

It is a fair amount, around 8,000 records in 2 columns, but the same amount was being pulled successfully using external list connections before the form was converted to DBXL.

I had tried GetDropDownentries prior to Query DB but noticed that the result set was limited to a sub-set of records, even without specifying a limit on the query.

I was able to pull 16,000 results back from a database using QueryDB directly with just a javascript call with no issues

When pulling in that much data the browser's performance is going to degrade, especially if the data is used in the form's view on load.

When you were querying that same data set (before using QueryDB), was it also using a browser form? An InfoPath form (Filler) should be able to handle this scenario better than a browser form.

In our documentation and training (and in our consulting work) we avoid querying large data sets and instead recommend filtering to improve form performance.

http://www.infopathdev.com/files/folders/dbxl/entry78027.aspx

http://www.infopathdev.com/files/folders/sql_integration/entry65681.aspx

Hope this helps!

Yes, the existing solution is a browser form, and while slow it does work.

Unfortunately the business need for this depends on having this massive amount of data, and it be browser based. I'll just have to get creative I guess.

Is the query used to get data for validation, or populate some dropdowns?

For validation, you can do filtered queries based on other form input to reduce the data returned but still perform the validation.

And remember, with a browser form, all the data you retrieve is kept as part of the form state on the server.  Multiply this by the number of users accessing the form and your server may be overwhelmed if it doesn't have sufficient resources.

It's all for populating drop-downs.

The form allows users to submit expense vouchers for payment, so one of the drop-downs contains the vendor list (~8,000)

There are several large populations of data like this on the form

I recommend providing a cascading dropdown - give the user a parent dropdown that has categories, each of which queries a smaller set of sub-categories (from your list of 8000). This is a good option if the user does not know what they are looking to select because it guides them.

If the user knows what they need to select (and how to spell it), a faster option is to use a search field. The user types in 3 or more characters and you call QueryDB with that as the filter to reduce the amount of the dropdown.

Both techniques should make the form more user friendly because there won't be as much lag getting and displaying the data.

The issue with the browser is the number of items in a dropdown. The browser form must create a separate window control for each item. That's 8,000 window controls which is what's taking time. The query itself is no doubt super fast.  

Hi Patrick,

Unfortunately the cascade option is out. This specific need is a list of vendors, for which there are no categories, leaving only an alphabet category, but this is also unacceptable because vendors are often named according to their legal entity name, and not the name the user is used to dealing with, which causes confusion. There are also 4 other large populations of data on a repeating line section of the form, again with no way to filter them by a category or parent object.

I like the search idea, and is likely what I'm going to end up with, having a separate search view in the form, and a button beside each field to allow for the search which passes which field it is to the search view in order to pre-set what is to be searched with DBXL.

I was wanting to implement some form of type-ahead guessing of what the user is looking for, potentially leveraging SPServices on the client side to prevent the post-backs. If I were to implement a rule on the search field that triggered once there were 3+ chars in there, even though the query would be faster, I would foresee a lot of "Sending Data to Server" messages popping up. Would you agree?

Thanks for everyone's suggestions and input on this.

Querying the 16,000 elements is likely not the issue since you were able to do this quickly with javascript.

The issue is in displaying them since InfoPath creates a separate window control for every item in the dropdown.

However, to search the set requires code. The Web service is quick and provides search and has the advantage that not all data will be pulled down but you get the post backs.

I really think you should try to create a taxonomy for your data. Add a couple columns for location, or specialty or something. That way you can do dropdowns.

Other than that, you could search the 16,000 entries locally using a little code. qRules may also have a command to search a secondary data source for substring matches.