InfoPath Dev

I recently purchased qrules and have run into a snag that I cannot seem to overcome.  I have a list of clients.  I need to search the list by last name to ensure a person is not a pre-existing client.  Sometimes the name is entered incorrectly.  So, I need to query last name for partial matches.  (ie., a search for "thom" should return thompson and thomas).

Furthermore, my list has over 20,000 entries - so filtering client side will not work.

I can do this easily with whole name matches by setting a list query field; however, I cannot figure out how to do this for partial matches.  Does qrules provide this type of functionality?

Hi Jeff,

qRules uses the owssvr.dll functionality in SharePoint to support filtering. I do not know if there is a "LIKE" or "CONTAINS" verb that is supported. I have forwarded your request to Qdabra support.

More soon,

Hi:

The qRules functionality leverage owssvr.dll and as such is bound by the limitations of owssvr.dll. To my knowledge, only exact matches are provided by the URL protocol.

Any chance you are using SharePoint 2010? If so, the ListData.svc (OData provider) may be an option for you.

I am using Infopath 2010 and SP 2010; however, I have no idea where to start.  I can follow directions very well, but I am an end user, so programming something, even simple stuff, pushes the boundaries of my capabilities.

Thanks for looking in to this for me.

Good news, no programming involved.

You'll need to see if the ADO.NET data services are installed on your SharePoint server. The link to the ListData.svc looks like this:

http://YOURSERVERNAME/_vti_bin/ListData.svc

With, of course, YOURSERVERNAME replaced with the SharePoint server you are using.

You should see XML returned on this page - that XML is a list of OData entities that you can access.

Here is more info on the OData URI conventions: http://www.odata.org/developers/protocols/uri-conventions

Basically, you'll create a REST data connection to your list, and don't run the query on load. The URL will look something like this:
http://YOURSERVERNAME/_vti_bin/ListData.svc/YOURLISTNAME()

Then, in your form, when the field you are using for "search" criteria changes, you can add a rule that changes the REST URI. Use a formula, so you can include the value from your form. The formula will look something like this:
concat("http://YOURSERVERNAME/_vti_bin/ListData.svc/YOURLISTNAME?filter=substringof('", yourfieldhere, "', NAMEOFCOLUMNTOFILTERON) eq true")

Then you run the query again in another rule action to return everything from that column that contains whatever your field had in it. You can also query for just items that start with the value in your field -- more filter info here: http://www.odata.org/developers/protocols/uri-conventions#FilterSystemQueryOption

Here is another link with a little walkthrough that may also help you:
http://arabic2000.com/blog/index.php/archives/tag/odata

That was a great help.  I figured that out; however, for some reason REST doesn't appear to work over SSL and half of my usage is over the internet.  Have you run into a problem like this?

I am using a work around - just opened up the site to non-html over the internet.  May be my only option since it appears that https won't work.

I have one other question that you probably know off the top of your head - here is my REST string:

concat("http://[my server]/_vti_bin/ListData.svc/Clients?$filter=startswith(LastName, '", Last Name, "') eq true")

 How can I change this to not be case sensitive.  For example, I have a client named A'Miracle.   Some will put A'miracle and others the actual spelling.  I don't think the translate function will work.  Any ideas?

URI protocol to the rescue again - change it to lower, using the tolower() function:

concat("http://[my server]/_vti_bin/ListData.svc/Clients?$filter=startswith(tolower(LastName), tolower('", Last Name, "')) eq true")

I appologize - I see "tolower" in the link now.  Not sure why it didn't jump out at me sooner.  You rock!  Thanks.

I did get everything working; however, I have run into another snag that perhaps you can point me in the right direction on.

 I am now implementing my solution on a production list; however, I have one list that I cannot seem to connect to with REST.

In the data connection wizard, I enter my connection string properly, just like I did on the test list.  I get the error "could not open "http:/..../_vti_bin/ListData.svc/CaseMaster" and then when I click ok i get a second message "InfoPath cannot open the XML document: http://..../_vti_bin/ListData.svc/CaseMaster Not enough storage is available to process the command."

I know this is a generic message.

To troubleshoot, I entered into my web brower http://..../_vti_bin/ListData.svc/CaseMaster and I get a 404 error.  When I do the same and enter any other production list, I get the first 1000 records of that list.  So something about this list is not working. (The list has 20,000 entries; however, I have made appropriate adjustments to resource throttling without solving the problem - I am able to connect to other lists with over 100,000 entries)

Here is a snippet from my raw ListData.svc xml

Can you point me in the right direction for troubleshooting? 

Any chance CaseMaster is an external list?

No.  In fact it is a normal list on the same server and site that I have been using exclusively up to this point.

Just in case you need to help others with this issue - I could not use REST with my CaseMaster list becuase I had a calculated Yes/No column.  This apparently breaks the REST service for a list. 

Thanks. This is going to give me more flexibility. But can you have multiple filters in a REST URI?

Most web searches on Infopath & wildcards filtering still deliver either code-based solutions via ADO & Database use or REST approch mentioned here.

Unless I am missing an important detail, I was able to do partial-match search on SP List & Form Libray Forms data with Infopath 2010.

Screenshot attached. If there are people still interested in the topic, please reply to this post and I will provide some details.