Pnpjs: How to use multiple Filters in one call while using pnpjs

[harrychen1]

Category

• [ ] Enhancement
• [ ] Bug
• [X ] Question
• [ ] Documentation gap/issue

We have a project that will need to query two list fields in order to return the correct list items. I've tried one filter is working fine with the following syntax:

sp.web.lists.getByTitle("Journals").items.select( "Title", "ISDNNumber").filter("substringof('" + searchString + "',Title)").top(100).get()

My question is how to add another filter in the query? The following syntax gave error in the console.

sp.web.lists.getByTitle("Journals").items.select("Title", "ISDNNumber").filter("substringof('" + searchString + "',Title)" or "substringof('" + searchString + "',ISDNNumber)").top(100).get()

Your suggestion is highly appreciated.

[koltyakov]

Hi @harrychen1,

Filter condition which is passed to .filter() modifier and ends up a value in REST's $filter= property should stand for a valid URI string component.

In your case, you just missed concatination in the string statement:

I believe that's it. In the case of modern JavaScript template strings and editor validations would have helped:

sp.web.lists
  .getByTitle('Journals').items.select('Title,ISDNNumber')
  .filter(`substringof('${searchString}',Title) or substringof('${searchString}',ISDNNumber)`)
  .top(100).get()
  .then(console.log)
  .catch(console.warn)

[harrychen1]

Thanks for the quick response. Tried with different options and still no luck.

filter("substringof('" + searchString + "',Title)" + "or" + "substringof('" + searchString + "',ISDNNumber)")

parsers.ts:13 Uncaught (in promise) Error: Error making HttpClient request in queryable [400] ::> {"odata.error":{"code":"-1, Microsoft.SharePoint.Client.InvalidClientQueryException","message":{"lang":"en-US","value":"The $filter expression \"\"substringof('\" searchString \"',Title)\" or \"substringof('\" searchString \"',ISDNNumber)\"\" is not valid."}}}
at new HttpRequestError (parsers.ts:13)
at parsers.ts:19

filter("substringof('" + searchString + "',Title)" + 'or' + "substringof('" + searchString + "',ISDNNumber)")
parsers.ts:13 Uncaught (in promise) Error: Error making HttpClient request in queryable [400] ::> {"odata.error":{"code":"-1, Microsoft.SharePoint.Client.InvalidClientQueryException","message":{"lang":"en-US","value":"The expression \"substringof('699',Title)orsubstringof('699',ISDNNumber)\" is not valid."}}}
at new HttpRequestError (parsers.ts:13)
at parsers.ts:19

Also tried to use character ` in the filter but still error out.

Any help would highly appreciated.

Thanks!!!

[koltyakov]

You keep no spaces around or.

[harrychen1]

I tried and it will think or as variable. The error is "Cannot find name 'or'".

I also tried the following different ways.
+or+

• or +
• "or" +
• 'or' +
  Also use character ` .

I run out of ideas. Thanks!!!

[koltyakov]

It's not about quotes (single or double work in JS, backqoutes also work modern JS/TS) but the string you end up passing as a filter:

• substringof('699',Title)orsubstringof('699',ISDNNumber) - incorrect
• substringof('699',Title) or substringof('699',ISDNNumber) - correct

So for vanilla JS style:

• .filter("substringof('" + searchString + "',Title) or substringof('" + searchString + "',ISDNNumber)")

[harrychen1]

I believe your suggestion is correct.

.filter("substringof('" + searchString + "',Title) or substringof('" + searchString + "',ISDNNumber)")

Another issue came out that might not related to this. The list I query against has 22000 items that exceeded view threshold.

Interesting observation is there is no error when apply one one filter (either filter). However and query with "or" condition, the following error come up.

Query is:
sp.web.lists.getByTitle("Journals").items.select("ID", "Title", "ISDNNumber", "Abbreviation", "URL").filter("substringof('" + searchString + "',Title) or substringof('" + searchString + "',ISDNNumber)").top(100).get()

The error is below.
parsers.ts:13 Uncaught (in promise) Error: Error making HttpClient request in queryable [500] ::> {"odata.error":{"code":"-2147024860, Microsoft.SharePoint.SPQueryThrottledException","message":{"lang":"en-US","value":"The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator."}}}
at new HttpRequestError (parsers.ts:13)
at parsers.ts:19

At this point, the query is correct formatted and I'll find a different way to perform the two filters.

Thanks for all the effort and help!!!

[koltyakov]

With threshold or won't work, that's true.
Cheers, closing this one.

[harrychen1]

Thanks again for the great effort! Please close this multiple filter format question that has been answered.

The below comments are for future reference.

Since our list has 22000 items that exceed the view threshold, "or" filter will not work. I have to query to get all items first using getAll() to overcome the threshold issue. Then loop through each item and compare the string match. This is not idea and will see if there is better logic or method.

Thanks again for the help and really appreciated!!!

[cjnanamurthy]

How to filter list Items with more than one Lookup field values??

Like.. Folder/SubFolder/Property eq Value

sp.web.lists.getByTitle('ALL_Files').items.select("*, Folder/Folders/Folders/Name").expand("Folder/Folders/Folders").filter('Folder/Folders/Folders/Name eq AD')