InfoPath Dev

Although I have been playing around with InfoPath for a few years I have not dealt with creating secondary data connections using SharePoint or Access. My form currently uses an xml file as a secondary data source which is included in the template.  I am dealing with asset records from Peregrine Asset Center and will be shortly migrating to HP Asset Manager 5.x  The overall database will have well over 100,000 records but I am using a 12000 records I uploaded as a sharepoint list to test.  Initially, I used an Access database as the secondary data source with the same 12000 records and the size of it was approximately 14 MB and I stored it on a shared network drive. 

 What is the best method for connecting such as large dataset?

Secondly, with the SharePoint list acting as a secondary data source I am able to retrieve the data enter the necessary updates and submit the form to a sharepoint form library, however when I open the form from the Sharepoint form library none of the fields are populated with the data from the secondary data source. 

Could you point me in the direction to correct this issue as well.

Currently, using InfoPath 2003 and organization is currently in the slow process of upgrading to InfoPath 2010.  We are concurrently in the process of upgrading to Sharepoint 2010, however InfoPath Form Services will not be available, if that is any help.

Any advice that can be provided would be appreciated.

Hi,

I think you will run into performance problems with a huge record set like that if you use the InfoPath data connections to connect to a SharePoint list.  InfoPath will bring back the entire list and any filtering you do will happen in the form.  The larger the list the more time it will take. 

The best way to do this is to use a database or SharePoint and then use a web service to query the data from your form (on load or user initiated).  This way the queries can be parameterized and thus filtered on the server side which will limit the amount of data you bring back into the form.  We have a tool called "DBXL" which allows you do this, it's a generic web service and can work with either SharePoint lists or a SQL backend.

http://www.qdabra.com/en/products/DBXL.aspx

We also have a tool called qRules which lets you query for SharePoint data as well:

http://www.qdabra.com/en/products/qRules_new.aspx

Regarding your issue with the form not showing the data when you open from SharePoint - are you copying in the data from the secondary data source in to the main form data source?  Secondary Data does not persist in the form upon save, only data saved in the main data source is saved.  Perhpas your issue?

Ted

Ted, thanks for the reply. I will look into DBXL and QRules as a solution.  I will not have Office 2010 avialable until October 2011 at the earliest.  I will see if I can convince my employer to make the purchase at that time.