Wednesday, October 31, 2012

Filtering large SharePointlist data in InfoPath

I have a site which consists of a large (30,000+) list of customer records.  In another list, I want to create an InfoPath 2010 form that contains a lookup to that customer list.  Of course, with such a large number of customer records, that means the performance is extremely poor at times, and forcing the user to browse through 30,000 customers is also extremely poor.  So I'd like to filter that lookup list.

The most obvious option is to filter client-side.  This would resolve the usability factor, but would still cause poor performance.  So the next option is to do something server-side.  However, using the InfoPath data connection wizard, there doesn't appear to be a way to filter a SharePoint list.  It's all or nothing.

I did a bit of digging around and remembered ListData.svc.  It's a byproduct of ADO.NET data services, so I made sure that was activated on my SharePoint server (it was), and then used this forum post as a guide - InfoPathDev: Query Sharepoint List - Partial Match.

Since my customer list is on a subsite, I had to use common sense to locate the actual list:

http://sharepoint/sitename/_vti_bin/ListData.svc/Customer

Shoving this into my browser showed the XML data (I'm using XML Tree, a Google Chrome extension), but of course, it's the full 30,000+ records and took a few seconds to display.  So I continued reading, and found the filter system query options on OData.org, so came up with this:

http://sharepoint/sitename/_vti_bin/ListData.svc/Customer?$filter=substringof(tolower('SearchQuery'),tolower(ColumnName)) eq true

I'm amazed at how well this works.  I've used tolower() to surround my search query and the column name - this forces a case insensitive search and doesn't seem to affect performance.

My next challenge is to make this work in InfoPath.  I have a text box, named CustomerSearchField, where the user can enter the search query, and when a Search button is hit, the REST URL for the data connection is updated.  The first rule for the button is "Change REST URL".  On the "Rule Details" screen, select the correct Data connection that you would have created earlier, and click the "fx" button next to REST Web Service URL.  In here, I used this:

concat("http://sharepoint/sitename/_vti_bin/ListData.svc/Customer?$filter=substringof('", CustomerSearchField, "',tolower(LongName)) eq true")

Don't forget the concat().  This caused me a lot of stress trying to get this working.

Finally, add another rule to the Search button - "Query for data", and then choose the same Data connection defined above.  There, working.

No comments:

Post a Comment