Unable to get List Items with CAML Query

hello Support Team,
I’d need help on how to configure code to retrieve data with Caml Query please.
I have a list (PurchaseOrder) with Forms. When I choose a Client, in that form, I would need to get all items in a second list (ClientsAndProviders) with a CAML Query, filtering by two different fields.
Unfortunately I’m not getting any results.
I’m working on Sharepoint 365 and Plumsail Forms 1.4.4.
This is the code I’m using:

retrieveListItems();

function retrieveListItems() {
	var SiteUrl = 'https://development01.sharepoint.com/';
    var clientContext = new SP.ClientContext(siteUrl);
    var oList = clientContext.get_web().get_lists().getByTitle('ClientsAndProviders');
        
    var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml('<View><Query><Where><And><Eq><FieldRef Name='DittaTxt' /><Value Type='Text'>Top Single Service srl</Value></Eq><Eq><FieldRef Name='ClienteTxt' /><Value Type='Text'>2T srl</Value></Eq></And></Where></Query><RowLimit>10</RowLimit></View>');
    this.collListItem = oList.getItems(camlQuery);
        
    clientContext.load(collListItem);
        
    clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));        
        
}

function onQuerySucceeded(sender, args) {
    var listItemInfo = '';

    var listItemEnumerator = collListItem.getEnumerator();
        
    while (listItemEnumerator.moveNext()) {
        var oListItem = listItemEnumerator.get_current();
        listItemInfo += '\nID: ' + oListItem.get_id() + 
        '\nClienteTxt: ' + oListItem.get_item('ClienteTxt');
    }

    alert(listItemInfo.toString());
}

function onQueryFailed(sender, args) {

    alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
}

At the end, I would need an example on how to get Items from a list via Caml Query. Thanks

Hello @AleStendardo!

You can get items with pnpjs. Please see the example with filtering below and also have a look at this article

pnp.sp.web.lists.getByTitle("Test").items.select("Title,ID").filter("Title eq 'Test' and ID eq '10'").getAll().then(function(allItems){
console.log(allItems);
});.

hello @mnikitina, thank you for your support. I tried it and it works fine.

Cheers

1 Like

Hello,

I use this example to filter a large list on a user / people picker field:
pnp.sp.web.lists.getByTitle("Test").items.select("Title,ID,User/EMail").expand("User").filter("User/EMail eq '[email protected]'").getAll().then(function(allItems){
console.log(allItems);
});.

This gives an error: Microsoft.SharePoint.SPQueryThrottledException","message":{"lang":"en-US","value":"The attempted operation is prohibited because it exceeds the list view threshold

Can I use paging for this? Can you help me with an example?

Thank you,
Daniël

Hello @danieljr,

How many items are in the test? Have you indexed the User field?

5383 items now, more to come
& yes it's indexed ...

I can use the other fields as filter in this list, but not the user field :frowning:

I've tried getPaged() but haven't got it working...

=> List Items - PnP/PnPjs

Hello @danieljr,

The PnPjs request has its limitations, like list views/requests threshold. When working with large lists (more than 5000 items in a view) you must meet certain criteria not to be throttled. See this post for more information.

Probably the request returns more items than a number of view throttling limit.

As a workaround you can filter by two columns. For instance, filter by Created field in the first condition and then apply filter to the resulting subset by Person field.

Hello Nikitina,

Based on the post and an interesting article, I use now RenderListDataAsStream

But I think my code is not yet optimised for Plumsail Forms. And what when my list has more than 10.000 items? Can someone help me to optimize my code? Maybe it's also helpfull for others :wink:

Thank you,

Daniël

(async () => {

    var strViewXML = "<View><Query><OrderBy><FieldRef Name='ID' Ascending='FALSE'/></OrderBy><Where><Contains><FieldRef Name='Userfield' /><Value Type='User'>" + strUsername + "</Value></Contains></Where></Query><ViewFields><FieldRef Name='ID'/><FieldRef Name='Title'/></ViewFields><RowLimit Paged='TRUE'>5000</RowLimit></View>";

    let firstPageResult = await sp.web.lists.getById("8f552e22-cd79-4ced-81f6-e325ee4bf71b").renderListDataAsStream({
        ViewXml: strViewXML
    });
    console.log("firstPageResult =>", firstPageResult);
    ResultsToTable(firstPageResult);

    let nextPageResult = await sp.web.lists.getById("8f552e22-cd79-4ced-81f6-e325ee4bf71b").renderListDataAsStream({
        ViewXml: strViewXML,
        Paging: firstPageResult.NextHref.substring(1)
    });
    console.log("nextPageResult =>", nextPageResult);
    ResultsToTable(nextPageResult);

})().catch(console.log);

Dear @danieljr,
What's the use case for using the code within forms? Maybe it's something we can help with. Though we won't be able to help optimize the code as it's a little beyond the scope of the product.

Hello @Nikita

We have more than 5000 users in our organization and we use Plumsail Forms for many things (To ask vacation, when someone is sick, to order new a new asset, keep track of our assets, ...)

Normally I use pnpjs, but has it's limitations. RenderListDataAsStream is faster and better. But I was wondering how you would integrate it in a Plumsail form ...

Also is there a limitation: With pnpjs I used LookupId='True' for filtering on UserID and Plumsail fields. This is not working with RenderListDataAsStream, or I hope you can tell me how to get is working ...

I hope you can help me and everyone who must deal with large lists, so we can use RenderListDataAsStream in Plumsail forms.

Thank you x1000,

Daniël

Dear @danieljr,
The easiest way to understand how RenderListDataAsStream works and how it can be used is to open browser's console > Network tab and trace all requests from regular SharePoint UI or from our Forms to RenderListDataAsStream, as we're also using it, pretty much the same way as SharePoint UI does it.

OK, thank you

And how would you sugget to handle the NextHref? Some day we will have more than 10.000 items in our lists?

Hello @danieljr,

You can test it on the list with +5K items and trace requests via Network tab of the browser console.