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

[kevyn]

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]

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

Code: Select all

 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]

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]

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]

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/we ... 5636431470 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/we ... 5636431470) 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/0 ... s/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]

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]

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]

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.