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

Discussions about Cross-site Lookup
Locked
kevyn
Posts: 25
Joined: Wed Aug 26, 2015

15 Feb 2016

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>'

}

User avatar
rostislav
Moderator
Posts: 364
Joined: Mon Oct 19, 2015

15 Feb 2016

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
Posts: 25
Joined: Wed Aug 26, 2015

16 Feb 2016

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

}

User avatar
rostislav
Moderator
Posts: 364
Joined: Mon Oct 19, 2015

16 Feb 2016

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
Posts: 25
Joined: Wed Aug 26, 2015

16 Feb 2016

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>

User avatar
rostislav
Moderator
Posts: 364
Joined: Mon Oct 19, 2015

16 Feb 2016

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
Posts: 25
Joined: Wed Aug 26, 2015

19 Feb 2016

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

User avatar
rostislav
Moderator
Posts: 364
Joined: Mon Oct 19, 2015

19 Feb 2016

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.

Locked
  • Information
  • Who is online

    Users browsing this forum: No registered users and 4 guests