Cross-site lookup wildcard search not working with spaces in the field

rss

Posted by kevyn - Feb 15 ’16 at 06:18

I have a cross site lookup which pulls information from 2 columns of a list. The wildcard search does not function properly when the contents contain a space

 

eg. searching for the word 'holiday' will return '001 - holiday' but not '002 - bank holiday' (where 001/002 are coumn 1, and holiday/bank holiday are column 2)

 

Is there anyway to fix this?

 

Request Items:

function (term, page) {

if (!term || term.length == 0) {

return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby=Created asc&$top=10";

}


return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby=Title&" +

"$filter=startswith(Doc_x0020_Category, '" + encodeURIComponent(term) + "') or " +

"startswith(Doc_x0020_Type, '" + encodeURIComponent(term) + "')&$top=10";

 

}

 

Item format:

function(item) {

return '<span class="csl-option">' + item["{LookupField}"] + '</span>'

}

  • rostislav
    rostislav
    Moderator
    Member for: 6 years 8 months 14 days
    #1 by rostislav Feb 15 ’16 at 12:54

    startswith searches for strings that start with some string. "bank holiday" doesn't start with "holiday". So, what you need is the substringof operator. Your very same example, but using colA and colB for internal names (notice the reversed order of parameters):

     function (term, page) {
    
      if (!term || term.length == 0) {
    
        return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby=Created desc&$top=10";
    
      }
    
      return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},colA,colB&$orderby={LookupField}&$filter=substringof('" + encodeURIComponent(term) + "',colA) or " + "substringof('" + encodeURIComponent(term) + "',colB)&$top=10";
    
    }

     

  • kevyn
    Member
    Member for: 6 years 4 months 26 days
    #2 by kevyn Feb 16 ’16 at 08:26

    [#1]: Thanks, but using your example and replacing colA and colB with my column names (Doc_x0020_Category and Doc_x0020_Type) gives me a 'loading failed' error when entering text

     

    Code:

    function (term, page) {

    if (!term || term.length == 0) {

    return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby=Created desc&$top=10";

    }

     

    return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Doc_x0020_Category,Doc_x0020_Type&$orderby={LookupField}&$filter=substringof('" + encodeURIComponent(term) + "',Doc_x0020_Category) or " + "substringof('" + encodeURIComponent(term) + "',Doc_x0020_Type)&$top=10";

    }

  • rostislav
    rostislav
    Moderator
    Member for: 6 years 8 months 14 days
    #3 by rostislav Feb 16 ’16 at 09:45

    [#2]:

    1. When you're on the form with the lookup field open your browser's console (F12)

    2. Type something into the lookup field to get some results back

    3. The lookup field displays the error. Observe what the console outputs. There should be an error message with a link.

    4. Copy-paste that link into the address bar of the browser and navigate to the address.

    What we will need to see:

    a. The URL from the error message

    b. the error displayed on the page you navigated to

    c. the field types of Doc_x0020_Category and Doc_x0020_Type fields

  • kevyn
    Member
    Member for: 6 years 4 months 26 days
    #4 by kevyn Feb 16 ’16 at 10:32

    [#3]: Thank you - please find my results below (changed the actual domain to 'mydomain')

    This is what the console displays:

    GET https://mydomain.sharepoint.com/_api/web/lists('%7Bdda46c6e-77…tegory)%20or%20substringof(%27d%27,Doc_x0020_Type)&$top=10&_=1455636431470 400 (Bad Request)send @ plumsail.csl.jquery.js:5m.extend.ajax @ plumsail.csl.jquery.js:5(anonymous function) @ plumsail.csl.select2.js:22

     

    This is what happens when I paste the URL (https://mydomain.sharepoint.com/_api/web/lists('%7Bdda46c6e-77…tegory)%20or%20substringof(%27d%27,Doc_x0020_Type)&$top=10&_=1455636431470) into browser:

    This XML file does not appear to have any style information associated with it. The document tree is shown below.

    <m:error xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">

    <script id="tinyhippos-injected"/>

    <m:code>

    -1, Microsoft.SharePoint.Client.InvalidClientQueryException

    </m:code>

    <m:message xml:lang="en-US">

    The expression "web/lists('{dda46c6e-77…tegory) or substringof('d',Doc_x0020_Type)&$top=10&_=1455636431470" is not valid.

    </m:message>

    </m:error>

  • rostislav
    rostislav
    Moderator
    Member for: 6 years 8 months 14 days
    #5 by rostislav Feb 16 ’16 at 12:26

    [#4]:

    What you have done is pasted a shortened version of the URL (if you take a look at it you'll see "..." instead of a part of it). What you need to do is copy the URL by right clicking the link in the error message and pasting this full URL into the address bar. So, please repeat with the correct URL from the error message.

  • kevyn
    Member
    Member for: 6 years 4 months 26 days
    #6 by kevyn Feb 19 ’16 at 09:15

    [#5]: Ok I see what I did, originally I had the title of the column incorrect, but after resolving that, I got this error:

    <m:message xml:lang="en-US"> The field 'Doc_x0020_Category_x0020_and_x00' of type 'Calculated' cannot be used in the query sort expression. </m:message> is it not possible to use wildcard on a calculated column? (starswith works ok, but is not what we want) Thanks
  • rostislav
    rostislav
    Moderator
    Member for: 6 years 8 months 14 days
    #7 by rostislav Feb 19 ’16 at 10:42

    [#6]:

    Unfortunatelly, you cannot use a calculated column with substringof. You'll have to, if you can, search on columns that you calculated field is based on.

Displaying 1 to 7 of 7 messages