Using InfoPath to validate SharePoint list data against an external SQL Server database

Andyscott — Tue, 28 Oct 2014 14:58:15 GMT

My company's business is to service equipment on customers' sites across the country. We service some 30000 devices across some 15000 sites for some 500 customers. Our core systems for these are in the process of being updated - a long project - and we are using SharePoint to address some important business issues in the interim where we could gain some "quick wins".

As part of this I've set up a SharePoint list to track specific issues when the servicing doesn't go to plan. As records get added to this list we need to validate them: we need to validate who has been assigned to track the issue and we need to validate that the device location details have been entered correctly. For the first of these, I have set up a second SharePoint list with just the details of the 100-or-so people who can "own" issues. For the second, I need to validate against a 30000-row table of valid locations held on SQL Server, and which will change from day to day.

To accomplish this, I have customised the "standard" list entry form using InfoPath (InfoPath 2013). I've added a second data connection to the list of issue owners and I can set up the query string and return the owners name in order to validate against that source. I've then tired two methods of validating against the external database but I can get neither to work.

The first method was to set up a data connection direct to the table. But there is no way I can see to set the query string to just return one row: instead I have to open the connection to receive all rows and then filter to the row I need. This appears to work in "preview" mode but not when the form is published - I think becasue of the data connection file. I have uploaded the data connection file to SharePoints "data connections" library, and approved it, but it still doesn't work. (I'm pretty sure this is due to a problem with the stored credentials, and there doesn't appear to be an option to use the Secure Store Service in the same way as you would for, say, and Excel workbook data connection.)

The second method was to create an External List based on the table. I can query this by setting the query string, and just one row will be returned which is exactly what I need. However, the row count from my table (at 30000) significantly exceeds the 2000 row limit for external lists. So it can work in test but not in production.

I'm sure that there must be a way of overcoming either of these limitations - but I just can't find it. Does anyone have any suggestions/pointers?

Thanks

SharePoint Integration

Using InfoPath to validate SharePoint list data against an external SQL Server database