InfoPath Dev

I have an active Sharepoint List that continues to grow and could become very large. I have looked into DBXL/MSSQL, but we are on Office 365 so at this point its not an option (although my tech guys are continuing to look into it). I am interested in using the FilterOwssvr command to open a data connection to this large list and only grab certain list items. Will using this command shorten the time to query the data as compared to just using the regular Data Connection? The load time is getting slower as this list becomes larger and I'm hoping I can manage it using this command. Thanks.

Are you using InfoPath 2010? Or 2007? I ask because if you are using 2010, you should have list query fields in your data connection to your list that you can use for equivalency queries.

I'm using 2010, but maybe I'm not understanding the command. I can currently choose the field(s) I want to load into the form, but I'd like to filter the list items that load, so if I had a list of 1000 items, I could filter it to only load say 300 that matched a certain criteria.

In InfoPath 2010, a SharePoint List data connection has query fields.

So, when you create the SharePoint List data connection (just a regular list data connection) in the last screen of the data connection wizard, you can deselect the checkbox for automatically loading data when the form opens - you don't want that.

Then, in your form, if you look at the data connection in the Fields task pane, you'll see that there are query fields. You can use rules to set query fields to the values you want to query for and then query your data connection.

So - let's say you have a list of Employees, with their Departments. You'd include the Department field when you create your list data connection. You can then set the Department query field to Human Resources and query the data connection to get back just the Employees in Human Resources - make sense?

There just isn't a need for an owssvr.dll data connection in IP 2010 / ShP 2010.

Thanks Hillary. That is a great explanation, and I understand what you are saying. I was hoping that there was a way to only load some of the list items into the Infopath form. When my list grows to 5,000 items, I was hoping to speed up the query process, as it keeps getting slower as the database grows. Waiting 60 seconds when someone chooses a dropdown box item (it takes that long to query the sharepoint list) isn't the end of the world, but faster is always better. It looks as though a DBXL/MS SQL solution may be the only one that will speed the whole thing up.

I must not be explaining correctly then - because what I am describing would be a method of only loading some items into your form.

I was misreading your explanation, for sure. So I could create multiple data connections to the same list, but use the query fields to limit what loads....that way I can have large lists that load only partially when called. I will try that. Thank you for clearing that up for me.

Or you could create a single query, and requery with different data as needed. Just make sure you don't check the box to return data on load and then give it a try - just drag the query and data fields onto your form and add a button that executes the query - then you can test it out a bit and get a feel for what I'm trying to describe. :)

I'm guessing that if you're using this large list to build your dropdown then you would still have an issue, right? Let's say my form has a dropdown for Customer Name, and I prepopulate that with all the customer names entered before. I would have to query the entire list to get the previously entered names, and if this list is over the limit then it would break. I can't think of a particular query field I could use to filter down the list without possibly hiding some of the entries I'm looking for. I'd almost have to use a separate list for the customer name entries rather than the form library (to workaround the issue). If anyone knows of a better way then I'd love to hear it.