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.

Redirecting after Workflow Task page

In SharePoint Designer, I created a workflow that contains multiple approval workflows.  In the emails that are sent out by the approval workflows, a link is contained that sends the user to a URL that allows the user to approve, reject, etc.  (I know this functionality is included in Outlook 2010, but this is not what the key users wanted)

The URL is called using the variable [%Current Task:Form_URN%], which works fine, but when Approve or Reject is clicked, it takes the user to the Workflow Tasks list, not the main site landing page as is required.

The solution I found here is to simply add a Source query string parameter to the address in the email.  For example:

[%Current Task:Form_URN%]&Source=http://sharepoint/sitename

This creates a URL similar to the below:

http://sharepoint/sitename/_layouts/WrkTaskIP.aspx?List=%7Bd5cdf49b%2Da735%2D4669%2D8ce3%2Dc26d17666941%7D&ID=83&Source=http://sharepoint/sitename

Clicking on Approve or Reject (or, indeed, any other options you've added to the task) will then take the user back to the Source page instead, enhancing the user experience.  In theory, adding &Source to any SharePoint URL will have the same effect, which is extremely useful.