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);
});.
1 Like
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 'john.doe@mail.com'").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 
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 
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.