Freelance Project Requests info@CodeDocu.de Software Development in C# WPF Asp.Net Core Vba Excel Word SQL-Server EF Linq, UWP Net
#

 

 

Query Sum in SharePoint with a 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 >

 

 

Then you have to determine the Lise on which you want to create a CamlQuery query

 

    // get the list by Id

    let subWeb = Web("https://comlinesetest.sharepoint.com/sites/Teamwebsite");

    let list=subWeb.lists.getByTitle("Liste01");

 

 

To query server-side in SharePoint (online and on premise) you must create an XML string that groups and sums the list

 

//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>"    ;

 

 

Query CamlQuery to SharePoint

The correct query only works in 2020 with list.renderListDataAsService(caml)

 

 // build the caml query object 

    const camlICamlQuery = {      

      ViewXml: sXML      

    };

  

    // // get list items

    let items_query = await list.renderListDataAsStream(caml);

 

 

After that you can havethe list, series results output in the console or render in the WebPart

 

    // log resulting array to console

    console.log(items_query);

    console.log(items_query.Row[0]["w3yl.SUM"]);

    //console.log(items_query["w3yl"]);

 

    let htmlTablestring = '';

    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 listContainerElement = this.domElement.querySelector('#placeholder_SumTable');

    listContainer.innerHTML = htmlTable;

 

 

In the debug browser and WebPart you can see the array as 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 camlICamlQuery = {      

      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 htmlTablestring = '';

    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>

      `;

 

Mobile
»
Handle german number strings in English numbers
»
SharePoint CamlQuery Sum
»
Code Sample to Hello World SharePoint WebParts and Lists
»
SharePoint Code: Get ListItem by Code
»
Debugging with the Microsoft Browser in VS Code
»
Debugging von SharePoint in Visual Studio Code
»
Publish Install SharePoint WebPart on Server
»
Enable JS-Link for WebParts in SharePoint

.

Contact for Jobs, Project Requests: raimund.popp@microsoft-programmierer.de