Query Sharepoint List - Partial Match - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Query Sharepoint List - Partial Match

Last post 07-13-2013 01:35 PM by Patrick Halstead. 20 replies.
Page 1 of 2 (21 items) 1 2 Next >
Sort Posts: Previous Next
  • 05-17-2011 09:00 PM

    Query Sharepoint List - Partial Match

    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?

    Filed under: ,
  • 05-18-2011 12:47 AM In reply to

    Re: Query Sharepoint List - Partial Match

    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,

    Patrick Halstead
    Project Manager at Qdabra
  • 05-18-2011 08:22 AM In reply to

    Re: Query Sharepoint List - Partial Match

    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.

    Hilary Stoupa

  • 05-18-2011 08:30 AM In reply to

    Re: Query Sharepoint List - Partial Match

    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.

  • 05-18-2011 09:04 AM In reply to

    Re: Query Sharepoint List - Partial Match

    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

    Hilary Stoupa

  • 05-18-2011 09:58 AM In reply to

    Re: Query Sharepoint List - Partial Match

    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?

  • 05-19-2011 12:27 PM In reply to

    Re: Query Sharepoint List - Partial Match

    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?

     

  • 05-19-2011 12:43 PM In reply to

    Re: Query Sharepoint List - Partial Match

    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")

    Hilary Stoupa

  • 05-19-2011 08:25 PM In reply to

    Re: Query Sharepoint List - Partial Match

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

  • 05-23-2011 06:08 AM In reply to

    Re: Query Sharepoint List - Partial Match

    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

    - <collection href="CaseMaster">
      <atom:title>CaseMaster</atom:title>
      </collection>
    - <collection href="CaseMasterArchived">
      <atom:title>CaseMasterArchived</atom:title>
      </collection>
    - <collection href="CaseMasterAssignedAttorney">
      <atom:title>CaseMasterAssignedAttorney</atom:title>

    Can you point me in the right direction for troubleshooting? 

  • 05-23-2011 08:10 AM In reply to

    Re: Query Sharepoint List - Partial Match

    Any chance CaseMaster is an external list?

     

    Hilary Stoupa

  • 05-23-2011 08:43 AM In reply to

    Re: Query Sharepoint List - Partial Match

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

  • 05-28-2011 08:08 AM In reply to

    Re: Query Sharepoint List - Partial Match

    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. 

  • 06-02-2011 10:12 AM In reply to

    Re: Query Sharepoint List - Partial Match

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

  • 03-05-2013 01:20 PM In reply to

    • Den12
    • Top 200 Contributor
    • Joined on 01-15-2013
    • Posts 57

    Re: Query Sharepoint List - Partial Match

    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.


Page 1 of 2 (21 items) 1 2 Next >
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.