Summe abfragen in SharePoint mit einer CamlQuery
In SharePointOnline mit pnp/sp, React WebParts
Import der pnp/sp
//< pnp import > import { sp,ICamlQuery,Web } from "@pnp/sp/presets/all"; import { Lists } from "@pnp/sp/lists" //</ pnp import > |
Dann muss man die Liste ermitteln, auf welche man eine CamlQuery Abfrage erstellen will
// get the list by Id let subWeb = Web("https://comlinesetest.sharepoint.com/sites/Teamwebsite"); let list=subWeb.lists.getByTitle("Liste01"); |
Zum serverseitigen Abfragen in SharePoint (online und on premise) muss man einen XML String erstellen, welcher die Liste Gruppiert und summiert
//let sXML ="<view><viewfields><fieldref name='w3yl'/></viewfields></view>" ; let sXML ="<view>" + "<Query>" + " <GroupBy Collapse='TRUE'>"+ " <FieldRef Name='Title' /> "+ " </GroupBy> " + "<Where>"+ " <Eq>"+ " <FieldRef Name='Title' />" + " <Value Type='Text'>Game 2</Value>"+ " </Eq>"+ "</Where>"+ "</Query>"+ "<ViewFields>"+ " <FieldRef Name='w3yl' />"+ "</ViewFields>"+
"<RowLimit>1000</RowLimit>"+
"<Aggregations Value='On'>"+ " <FieldRef Name='w3yl' Type='SUM'/>"+ "</Aggregations>"+
"</view>" ; |
CamlQuery an SharePoint abfragen
Die richtige Abfrage funktioniert in 2020 nur mit list.renderListDataAsService(caml)
// build the caml query object const caml: ICamlQuery = { ViewXml: sXML };
// // get list items let items_query = await list.renderListDataAsStream(caml); |
Danach kann man sich die Liste, Reihen Ergebnisse in der Console ausgeben lassen oder in dem WebPart rendern lassen
// log resulting array to console console.log(items_query); console.log(items_query.Row[0]["w3yl.SUM"]); //console.log(items_query["w3yl"]);
let htmlTable: string = ''; htmlTable += ` <table> <tr><td>Eintrag</td> <td>Punkte</td></tr> <tr><td>Game 1</td> <td>${items_query.Row[0]["w3yl.SUM"]}</td></tr> </table> `; const listContainer: Element = this.domElement.querySelector('#placeholder_SumTable'); listContainer.innerHTML = htmlTable; |
Im Debug-Browser und WebPart sieht man dann die Array als rows
private async _renderListAsync(): Promise<void> { //------< _renderListAsync() >------
//----< Get_Caml_List >---- // get the list by Id let subWeb = Web("https://comlinesetest.sharepoint.com/sites/Teamwebsite"); let list=subWeb.lists.getByTitle("Liste01");
//let sXML ="<view><viewfields><fieldref name='w3yl'/></viewfields></view>" ; let sXML ="<view>" + "<Query>" + " <GroupBy Collapse='TRUE'>"+ " <FieldRef Name='Title' /> "+ " </GroupBy> " + "<Where>"+ " <Eq>"+ " <FieldRef Name='Title' />" + " <Value Type='Text'>Game 2</Value>"+ " </Eq>"+ "</Where>"+ "</Query>"+ "<ViewFields>"+ " <FieldRef Name='w3yl' />"+ "</ViewFields>"+
"<RowLimit>1000</RowLimit>"+
"<Aggregations Value='On'>"+ " <FieldRef Name='w3yl' Type='SUM'/>"+ "</Aggregations>"+
"</view>" ;
// build the caml query object const caml: ICamlQuery = { ViewXml: sXML };
// // get list items let items_query = await list.renderListDataAsStream(caml); //let items_query = await list.getItemsByCAMLQuery(caml); //let items_query = await list.renderListData(sXML);
// log resulting array to console console.log(items_query); console.log(items_query.Row[0]["w3yl.SUM"]); //console.log(items_query["w3yl"]);
let htmlTable: string = ''; htmlTable += ` <table> <tr><td>Eintrag</td> <td>Punkte</td></tr> <tr><td>Game 1</td> <td>${items_query.Row[0]["w3yl.SUM"]}</td></tr> </table> `; |