Filter with startsWith

Discussions about Cross-site Lookup
Locked
RMIC
Posts: 96
Joined: Sun May 10, 2015

12 Nov 2018

Hello dear support team,

How can I filter a cross-site lookup column with the startsWith parameter? REST is used in the cross-site lookup? So I used the source https://blog.trigent.com/introduction-t ... oint-2013/.

Only the elements that start with a specific value should be displayed in the cross-site lookup column. This particular value is in a column.

I have the following code, but it does not work:

function (term, page) {

var FirmenKuerzel = fd.field('FirmenKuerzel').value();
alert("CSL - FirmenKuerzel: "+FirmenKuerzel);


if (!term || term.length == 0) {
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby={LookupField}&$filter={LookupField} startsWith '" + FirmenKuerzel + "' &$top=10";
}
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby={LookupField}&$filter=startswith({LookupField}, '" + encodeURIComponent(term) + "') and {LookupField} startsWith '" + FirmenKuerzel + "' &$top=10";
}


Thanks for your help.

User avatar
AlexZver
Posts: 232
Joined: Mon Aug 27, 2018

13 Nov 2018

Hi!

I've slightly changed your code, it should work:

Code: Select all

if (!term || term.length == 0) {
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby={LookupField}&$filter=startswith({LookupField}, '"+ FirmenKuerzel + "')&$top=10";
}
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby={LookupField}&$filter=startswith({LookupField}, '" + encodeURIComponent(term) + "') and startswith({LookupField}, '"+ FirmenKuerzel + "')&$top=10";
}
You can read more about filtering configuration here

RMIC
Posts: 96
Joined: Sun May 10, 2015

28 Nov 2018

Thanks, that works great.

How can I add an additional filter, for example by status (see below: Status eq 'aktiv' or Status eq 'angelegt' or Status eq 'erstellen')?
I tested the following, but it does not work yet:

Code: Select all

if (!term || term.length == 0) 
	{
		return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,Status,{LookupField}&$orderby={LookupField}&$filter=startswith({LookupField}, '"+ FirmenKuerzel + "')&$top=30";
  	}
  		return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,Status,{LookupField}&$orderby={LookupField}&$filter=(substringof('" + encodeURIComponent(term) + "',{LookupField})) and (Status eq 'aktiv' or Status eq 'angelegt' or Status eq 'erstellen' ) and startswith({LookupField}, '"+ FirmenKuerzel + "') &$top=30";
 }
  
Thanks for your help.

Best regards.
RMIC

User avatar
Nikita Kurguzov
Posts: 889
Joined: Mon Jul 03, 2017

29 Nov 2018

Dear RMIC,
The code looks more or less correct, but keep in mind that the second return statement only applies when user starts entering text. If no text is entered - first return statement is used. So, this might be the reason why you don't see any results, simply change it to this:

Code: Select all

if (!term || term.length == 0) 
	{
		return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,Status,{LookupField}&$orderby={LookupField}&$filter=startswith({LookupField}, '"+ FirmenKuerzel + "') and (Status eq 'aktiv' or Status eq 'angelegt' or Status eq 'erstellen' ) $top=30";
  	}
  		return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,Status,{LookupField}&$orderby={LookupField}&$filter=(substringof('" + encodeURIComponent(term) + "',{LookupField})) and (Status eq 'aktiv' or Status eq 'angelegt' or Status eq 'erstellen' ) and startswith({LookupField}, '"+ FirmenKuerzel + "') &$top=30";
 }
Alternatively, you might be using incorrect Internal Name or the field is of different type, like Lookup field, and it doesn't store data as text. It could also just be a typo.

I hope this information helps - let me know how it goes!
Cheers

RMIC
Posts: 96
Joined: Sun May 10, 2015

30 Nov 2018

Hello Nikita,

Thank you for your answer. But unfortunately it does not work completely.
The second return (for search / text input) works correctly.

Unfortunately, the first return does not work. It will show "Loading failed".
Where is the mistake?

The internal name "Status" is correct.

Best regards.
RMIC

User avatar
Nikita Kurguzov
Posts: 889
Joined: Mon Jul 03, 2017

30 Nov 2018

Dear RMIC,
My bad, I've accidentally removed the & sign after the filter expression. This should work:

Code: Select all

if (!term || term.length == 0) 
	{
		return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,Status,{LookupField}&$orderby={LookupField}&$filter=startswith({LookupField}, '"+ FirmenKuerzel + "') and (Status eq 'aktiv' or Status eq 'angelegt' or Status eq 'erstellen' ) &$top=30";
  	}
  		return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,Status,{LookupField}&$orderby={LookupField}&$filter=(substringof('" + encodeURIComponent(term) + "',{LookupField})) and (Status eq 'aktiv' or Status eq 'angelegt' or Status eq 'erstellen' ) and startswith({LookupField}, '"+ FirmenKuerzel + "') &$top=30";
 }
Cheers

RMIC
Posts: 96
Joined: Sun May 10, 2015

03 Dec 2018

It works! Thanks!

Best regards.
RMIC

Locked
  • Information
  • Who is online

    Users browsing this forum: No registered users and 8 guests