Filter with startsWith

[RMIC]

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.

[AlexZver]

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]

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

[Nikita Kurguzov]

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!

[RMIC]

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

[Nikita Kurguzov]

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

[RMIC]

It works! Thanks!

Best regards.
RMIC