none
Javascript sample for counting occurrences in a Sharepoint column RRS feed

  • Question

  • I am just starting to try to incorporate JS in my Sharepoint environment, but am a newbie to JS.

    I am looking for sample code that will count/total up the values in a single Sharepoint column. Specifically, I created a column that designates a "1" if the value in a neighboring column (in the last) is blank and a "0" if that column contains data. What I seeks is a dynamic counter that will total up the number on 1s in column, showing a number of records that still need to be worked.

    Any help would be greatly appreciated. Remember I am new to JS coding.

    Thanks

    Friday, September 19, 2014 12:54 PM

Answers

  • Hi,

    You need to use the internal name of the column and the list name is fine.

    Here is another code example for your reference:

    <script type="text/javascript">
    
     var myItems;
    
    ExecuteOrDelayUntilScriptLoaded(GetCount, 'sp.js'); 
    
    function GetCount() {
     
     var queryListItem = '<View><Query><Where><Eq><FieldRef Name="Asset_x0020_ID" /><Value Type="Text">1</Value></Eq></Where></Query></View>';//change the column name here, and it the column type is different, you still need to change the type.
     
     var tContext = new SP.ClientContext.get_current();
       var tSite = tContext.get_site();
        var tWeb = tSite.get_rootWeb();
        var tList = tWeb.get_lists().getByTitle('Asset Child');//change the list name here
     
    var myquery = new SP.CamlQuery();
     myquery.set_viewXml(queryListItem);
     myItems = tList.getItems(myquery);
     
    tContext.load(myItems);
     
    tContext.executeQueryAsync(
     Function.createDelegate(this, this.onQuerySucceeded),
     Function.createDelegate(this, this.onQueryFailed)
     );
    }
     
    function onQuerySucceeded(sender, args) {
     
    alert(myItems.get_count());
    }
     
    function onQueryFailed(sender, args) {
     
     alert('Request failed. ' + args.get_message() +'\n' + args.get_stackTrace());
    }
     </script> 

    Best regards.

    Thanks


    Victoria Xia
    TechNet Community Support


    Wednesday, September 24, 2014 2:44 AM
    Moderator
  • When you create a column with spaces like this "Mgr SID Check" internal name of the column becomes "Mgr_x0020_SID_x0020_Check", so you have to change the below section of your code from

    var queryListItem = '<View><Query><Where><Eq><FieldRef Name="Mgr SID Check" /><Value Type="Text">1</Value></Eq></Where></Query></View>';

    "to"

    var queryListItem = '<View><Query><Where><Eq><FieldRef Name="Mgr_x0020_SID_x0020_Check" /><Value Type="Text">1</Value></Eq></Where></Query></View>';





    My Blog- http://www.sharepoint-journey.com|
    If a post answers your question, please click Mark As Answer on that post and Vote as Helpful

    • Marked as answer by JasonGuo Monday, September 29, 2014 12:49 PM
    Wednesday, September 24, 2014 11:43 AM
  • I inserted this code and received a error message from webpart "327"

    <script type="text/javascript">

      var myItems;

    ExecuteOrDelayUntilScriptLoaded(GetCount, 'sp.js');

    function GetCount() {
     
     var queryListItem = '<View><Query><Where><Eq><FieldRef Name="Mgr_x0020_SID_x0020_Check" /><Value Type="Text">1</Value></Eq></Where></Query></View>';//change the column name here, and it the column type is different, you still need to change the type.
     
     var tContext = new SP.ClientContext.get_current();
       var tSite = tContext.get_site();
        var tWeb = tSite.get_rootWeb();
        var tList = tWeb.get_lists().getByTitle('UW Scorecard 2014');//change the column name here
     
    var myquery = new SP.CamlQuery();
     myquery.set_viewXml(queryListItem);
     myItems = tList.getItems(myquery);
     
    tContext.load(myItems);
     
    tContext.executeQueryAsync(
     Function.createDelegate(this, this.onQuerySucceeded),
     Function.createDelegate(this, this.onQueryFailed)
     );
    }
     
    function onQuerySucceeded(sender, args) {
     
    alert(myItems.get_count());
    }
     
    function onQueryFailed(sender, args) {
     
     alert('Request failed. ' + args.get_message() +'\n' + args.get_stackTrace());
    }
     </script>

    • Marked as answer by JasonGuo Monday, September 29, 2014 12:50 PM
    Wednesday, September 24, 2014 12:05 PM

All replies

  • Hi,

    Based on your description, my understanding is that you want to use JavaScript to count the number of the values the specific column when the value is set to be 1.

    Please use the code below(remember to change the list name and column name in the code):

    <script src="http://code.jquery.com/jquery-1.10.2.min.js" type="text/javascript"></script>
    <script type="text/javascript">
    allItems = null;
    var tcount=0;
    $(document).ready(function () {
      ExecuteOrDelayUntilScriptLoaded(Count, 'sp.js');  
    });
    
    function Count(){
            var tContext = new SP.ClientContext.get_current();
            var tSite = tContext.get_site();
            var tWeb = tSite.get_rootWeb();
            var tList = tWeb.get_lists().getByTitle('custom list');// change custom list to your list name
            var tQuery = SP.CamlQuery.createAllItemsQuery();
            this.allItems = tList.getItems(tQuery);
            tContext.load(this.allItems);
            tContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySuccess), Function.createDelegate(this, this.onQueryFailed));
    }
    
    function onQuerySuccess(){
        var tContext1 = new SP.ClientContext.get_current();
        var listEnum = allItems.getEnumerator();
        while(listEnum.moveNext()){
            var item = listEnum.get_current();
            var field = item.get_item("Title");// change Title to your column name
            if(field=="1"){
                 tcount++;
             }
    }
     tContext1.executeQueryAsync(Function.createDelegate(this, this.ColumnCountSuccess), Function.createDelegate(this, this.ColumnCountFailed));
    
       }
     
    function onQueryFailed (){
        alert('Query List Failed ');
    }
    
    function ColumnCountSuccess(){
        alert('Success');
        alert(tcount);
    }
     
    function ColumnCountFailed(){
        alert('Failed');
    } 
    </script>
    

    Best regards.

    Thanks


    Victoria Xia
    TechNet Community Support

    Tuesday, September 23, 2014 6:22 AM
    Moderator
  • Thanks for the response. I entered the code into a Content Editor Web Part on the list page. The code is not display a number, which is what I was expecting.

    Do you know if the Custom List and Column Name must be "1 word" for it to work?

    Here's my alteration to your code:

    <script src="http://code.jquery.com/jquery-1.10.2.min.js" type="text/javascript"></script><script type="text/javascript">


    allItems = null;
    var tcount=0;
    $(document).ready(function () {
      ExecuteOrDelayUntilScriptLoaded(Count, 'sp.js'); 
    });

    function Count(){
            var tContext = new SP.ClientContext.get_current();
            var tSite = tContext.get_site();
            var tWeb = tSite.get_rootWeb();
            var tList = tWeb.get_lists().getByTitle('UW Scorecard 2014');// change custom list to your list name
            var tQuery = SP.CamlQuery.createAllItemsQuery();
            this.allItems = tList.getItems(tQuery);
            tContext.load(this.allItems);
            tContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySuccess), Function.createDelegate(this, this.onQueryFailed));
    }

    function onQuerySuccess(){
        var tContext1 = new SP.ClientContext.get_current();
        var listEnum = allItems.getEnumerator();
        while(listEnum.moveNext()){
            var item = listEnum.get_current();
            var field = item.get_item("Mgr SID Check");// change Title to your column name
            if(field=="1"){
                 tcount++;
             }
    }
     tContext1.executeQueryAsync(Function.createDelegate(this, this.ColumnCountSuccess), Function.createDelegate(this, this.ColumnCountFailed));

       }
     
    function onQueryFailed (){
        alert('Query List Failed ');
    }

    function ColumnCountSuccess(){
        alert('Success');
        alert(tcount);
    }
     
    function ColumnCountFailed(){
        alert('Failed');
    } </script>

    Tuesday, September 23, 2014 10:57 AM
  • Hi

    another approach

    You can implement workflow, which will save counts in separate list.

    create a separate list NEW_LIST, with a new column COUNT_COL which will store the count value

    attach a workflow to your parent list. Start the workflow when an item is added or change

    and based on specific value in your column, increment/decrement  NEW_LIST's column's COUNT_COL value


    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    Tuesday, September 23, 2014 11:30 AM
  • Hi,

    You need to use the internal name of the column and the list name is fine.

    Here is another code example for your reference:

    <script type="text/javascript">
    
     var myItems;
    
    ExecuteOrDelayUntilScriptLoaded(GetCount, 'sp.js'); 
    
    function GetCount() {
     
     var queryListItem = '<View><Query><Where><Eq><FieldRef Name="Asset_x0020_ID" /><Value Type="Text">1</Value></Eq></Where></Query></View>';//change the column name here, and it the column type is different, you still need to change the type.
     
     var tContext = new SP.ClientContext.get_current();
       var tSite = tContext.get_site();
        var tWeb = tSite.get_rootWeb();
        var tList = tWeb.get_lists().getByTitle('Asset Child');//change the list name here
     
    var myquery = new SP.CamlQuery();
     myquery.set_viewXml(queryListItem);
     myItems = tList.getItems(myquery);
     
    tContext.load(myItems);
     
    tContext.executeQueryAsync(
     Function.createDelegate(this, this.onQuerySucceeded),
     Function.createDelegate(this, this.onQueryFailed)
     );
    }
     
    function onQuerySucceeded(sender, args) {
     
    alert(myItems.get_count());
    }
     
    function onQueryFailed(sender, args) {
     
     alert('Request failed. ' + args.get_message() +'\n' + args.get_stackTrace());
    }
     </script> 

    Best regards.

    Thanks


    Victoria Xia
    TechNet Community Support


    Wednesday, September 24, 2014 2:44 AM
    Moderator
  • Thanks again, but it didnt work either.

    My list name = UW Scorecard 2014

    My Column name = Mgr SID Check

    Type= Calculated Column

    What am I missing/doing wrong. Thanks in advance

    So, here what I added to the CEWP:

    <script type="text/javascript">
     var myItems;
    ExecuteOrDelayUntilScriptLoaded(GetCount, 'sp.js'); 
    function GetCount() {
     
     var queryListItem = '<View><Query><Where><Eq><FieldRef Name="Mgr SID Check" /><Value Type="Text">1</Value></Eq></Where></Query></View>';//change the column name here, and it the column type is different, you still need to change the type.
     
     var tContext = new SP.ClientContext.get_current();
       var tSite = tContext.get_site();
        var tWeb = tSite.get_rootWeb();
        var tList = tWeb.get_lists().getByTitle('UW Scorecard 2014');//change the column name here
     NOTE: I also changed it to Mgr SID Check; and it didnt work either
    var myquery = new SP.CamlQuery();
     myquery.set_viewXml(queryListItem);
     myItems = tList.getItems(myquery);
     
    tContext.load(myItems);
     
    tContext.executeQueryAsync(
     Function.createDelegate(this, this.onQuerySucceeded),
     Function.createDelegate(this, this.onQueryFailed)
     );
    }
     
    function onQuerySucceeded(sender, args) {
     
    alert(myItems.get_count());
    }
     
    function onQueryFailed(sender, args) {
     
     alert('Request failed. ' + args.get_message() +'\n' + args.get_stackTrace());
    }
     </script> 
    Wednesday, September 24, 2014 11:21 AM
  • When you create a column with spaces like this "Mgr SID Check" internal name of the column becomes "Mgr_x0020_SID_x0020_Check", so you have to change the below section of your code from

    var queryListItem = '<View><Query><Where><Eq><FieldRef Name="Mgr SID Check" /><Value Type="Text">1</Value></Eq></Where></Query></View>';

    "to"

    var queryListItem = '<View><Query><Where><Eq><FieldRef Name="Mgr_x0020_SID_x0020_Check" /><Value Type="Text">1</Value></Eq></Where></Query></View>';





    My Blog- http://www.sharepoint-journey.com|
    If a post answers your question, please click Mark As Answer on that post and Vote as Helpful

    • Marked as answer by JasonGuo Monday, September 29, 2014 12:49 PM
    Wednesday, September 24, 2014 11:43 AM
  • I inserted this code and received a error message from webpart "327"

    <script type="text/javascript">

      var myItems;

    ExecuteOrDelayUntilScriptLoaded(GetCount, 'sp.js');

    function GetCount() {
     
     var queryListItem = '<View><Query><Where><Eq><FieldRef Name="Mgr_x0020_SID_x0020_Check" /><Value Type="Text">1</Value></Eq></Where></Query></View>';//change the column name here, and it the column type is different, you still need to change the type.
     
     var tContext = new SP.ClientContext.get_current();
       var tSite = tContext.get_site();
        var tWeb = tSite.get_rootWeb();
        var tList = tWeb.get_lists().getByTitle('UW Scorecard 2014');//change the column name here
     
    var myquery = new SP.CamlQuery();
     myquery.set_viewXml(queryListItem);
     myItems = tList.getItems(myquery);
     
    tContext.load(myItems);
     
    tContext.executeQueryAsync(
     Function.createDelegate(this, this.onQuerySucceeded),
     Function.createDelegate(this, this.onQueryFailed)
     );
    }
     
    function onQuerySucceeded(sender, args) {
     
    alert(myItems.get_count());
    }
     
    function onQueryFailed(sender, args) {
     
     alert('Request failed. ' + args.get_message() +'\n' + args.get_stackTrace());
    }
     </script>

    • Marked as answer by JasonGuo Monday, September 29, 2014 12:50 PM
    Wednesday, September 24, 2014 12:05 PM
  • I just got it to work. Thanks to ALL
    Wednesday, September 24, 2014 12:45 PM