Page 1 of 1
Filter with startsWith
Posted: 12 Nov 2018
by 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.
Re: Filter with startsWith
Posted: 13 Nov 2018
by 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
Re: Filter with startsWith
Posted: 28 Nov 2018
by 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
Re: Filter with startsWith
Posted: 29 Nov 2018
by 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!
Re: Filter with startsWith
Posted: 30 Nov 2018
by 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
Re: Filter with startsWith
Posted: 30 Nov 2018
by 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";
}
Re: Filter with startsWith
Posted: 03 Dec 2018
by RMIC
It works! Thanks!
Best regards.
RMIC