Filtering Cross Side Lookup with Contains or Substringof?

T_H · December 17, 2021, 10:28am

Hello

I would like to filter the Dropdownlist based on a column in the initial list. The Columns in List have this Format.

ID Title Websites
1 Text-1 all
2 Text-2 aaa,yyy
3 Text-3 yyy,xxx
4 Text-4 bbb

This code works but does not show all entries.
function (term, page) {
if (!term || term.length == 0) {
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$filter=startswith(Websites,'all') or startswith(Websites,'yyy')&$orderby=Title&$top=25";
}
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby={LookupField}&$filter=startswith({LookupField}, '" + encodeURIComponent(term) + "')&$top=10";
}

The Function 'substringof' or 'contains' view an error

Can someone help me)
Tom

Nikita_Kurguzov · December 20, 2021, 1:56pm

Dear @T_H,
It is substringof, but the position of the field you want to search switches, like this for searching multiple fields, for example:
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Title,Area,Description&$orderby={LookupField}&$filter=substringof('" + encodeURIComponent(term) +"',{LookupField}) or substringof('" + encodeURIComponent(term) +"',Area) or substringof('" + encodeURIComponent(term) +"',Description)&$top=10";

T_H · December 21, 2021, 7:03am

Hallo Nikita,

thanks for helping. Now it worked.
Is there a list somewhere where you can read the query syntax?

LG
Tom

Nikita_Kurguzov · December 21, 2021, 11:06am

Dear @T_H,
You can look at available options from Microsoft, the queries are based on OData, but somewhat limited, more about it here - Use OData query operations in SharePoint REST requests | Microsoft Docs