Using Javascript to Performa Calculation and display at the top of each column

Forums General discussion Using Javascript to Performa Calculation and display at the top of each column

Viewing 23 reply threads
  • Author
    Posts
    • #13778
      Iain Munro
      Participant

      Afternoon

      A few years ago, I received some code to perform some calculations and display the end result in the area where you add your Total Counts, Sums, Averages etc – You know, just above the Column heading in a SharePoint list – that was back in the nothingbutsharepoint days.

      I have been looking throughout the web trying to come across this, but to no avail.

      Does anything know the best way to achieve this ?

      Iain

      • This topic was modified 4 years, 5 months ago by Iain Munro.
    • #13809
      Alexander Bautz
      Keymaster

      Did you receive this code from me?

      This could be done by a script in a CEWP looking at all rows and updating the header based on the visible items in the list view. This method will only work for visible rows.

      Could you provide some more details about what you want to achieve?

      Alexander

    • #13814
      Iain Munro
      Participant

      Hi Alexander

      I cannot recall who I got the details from – it was back in 2008.

      The example I was using at the time was calculating TRIR (safety stat) – we had our count of injuries and total hours, so in order to do the calculation. The calculation in the background using I think javascript or jquery would perform the calculation as follows – injuries x 200000 divided by total hours – it would then put above the column – TRIR – 2.4 as an example. It was live and would change as data was added.

      So was just trying to see the best way to do this.

      Iain

    • #13844
      Alexander Bautz
      Keymaster

      If I understand correctly you need to perform this calculation on two values already summed up in the list header?

      If you post a screenshot of your list view headers I’ll sketch up a solution for you.

      Alexander

    • #13864
      Iain Munro
      Participant

      Hi Alexander

      Please see attached screenshot.

      In the example I am working on, I have a number of packages which I can do a count on. I also created a day count between the date received and date uploaded – as that is a calculated field, I cannot add an Avg to it.

      From there, what I want to be able to do is show the Average Days it takes to turn a package around.

      If this is not clear, please let me know.

      Appreciate the help

      Iain

      Screenshot

    • #13890
      Alexander Bautz
      Keymaster

      Hi,
      I though you would have both values in the list header, but with the “Days taken” not being summed up, I guess the best approach is to use a query that is not depending on the actual items in the view. Put this code in a Script editor web part in the page:

      <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
      <script type="text/javascript">
      setTimeout(function(){
      	(function(){
      		jQuery.ajax({       
      			"url": _spPageContextInfo.webServerRelativeUrl+"/_api/web/lists/GetById('"+_spPageContextInfo.pageListId.split(/{|}/).join("")+"')/items?$filter=MOCNumber ne null",   
      			"type": "GET",   
      			"contentType": "application/json;odata=verbose",
      			"headers":{ 
      				"Accept": "application/json;odata=verbose",
      				"X-RequestDigest" : jQuery("#__REQUESTDIGEST").val()
      			},   
      			"success": function(data){
      				var totItems = data.d.results.length, totDays = 0, avgDays = 0;
      				jQuery.each(data.d.results,function(i,item){
      					totDays += Number(item.DaysTaken);
      				});
      				avgDays = totDays / totItems;
      				jQuery("table.ms-listviewtable").before("<div style='color:green;font-size:22px;border:1px #cccccc solid;padding:3px;'>Average days: "+parseInt(avgDays,10)+"</div>")
      			},
      			"error":function(err){
      				console.log(err);
      			}
      		});
      	})();
      },1000);
      </script>

      The filter on the query is set in the “url” parameter behind the “filter=”. Change this to match the filter you need to use.

      You also need to ensure you have the correct FitldInternalName of your fields in the “MOCNumber” and “DaysTaken” columns.

      This code will show a count as shown in the screenshot.

      Let me know if you have any questions.

      Please note that this code snippet if for SharePoint 2013 – if you need it for SP2010 I have to rewrite it slightly.

      PS: None of the columns need to actually be in the view.

      Hope you can use this approach.

      Alexander

      Attachments:
    • #13893
      Iain Munro
      Participant

      Hi ALexander

      This is exactly what I was looking for – having an issues getting it to work. I am getting an error.

      Here is the code

      <script src=”https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js”></script><script type=”text/javascript”>
      setTimeout(function(){
      (function(){
      jQuery.ajax({
      “url”: _spPageContextInfo.webServerRelativeUrl+”/_api/web/lists/GetById(‘”+_spPageContextInfo.pageListId.split(/{|}/).join(“”)+”‘)/items?$filter=MOC_x0020_Number”,
      “type”: “GET”,
      “contentType”: “application/json;odata=verbose”,
      “headers”:{
      “Accept”: “application/json;odata=verbose”,
      “X-RequestDigest” : jQuery(“#__REQUESTDIGEST”).val()
      },
      “success”: function(data){
      var totItems = data.d.results.length, totDays = 0, avgDays = 0;
      jQuery.each(data.d.results,function(i,item){
      totDays += Number(item.Days_x0020_Taken);
      });
      avgDays = totDays / totItems;
      jQuery(“table.ms-listviewtable”).before(“<div style=’color:green;font-size:22px;border:1px #cccccc solid;padding:3px;’>Average days: “+parseInt(avgDays,10)+”</div>”)
      },
      “error”:function(err){
      console.log(err);
      }
      });
      })();
      },1000);
      </script>​<br/>​<br/>​<br/>

      I get the Average Days above with NaN

      Any ideas ?

      Iain

    • #13895
      Alexander Bautz
      Keymaster

      Hi,
      The filter field are missing the operator and filter value.

      Change:

      ?$filter=MOC_x0020_Number

      to

      ?$filter=MOC_x0020_Number ne null

      Alexander

    • #13900
      Iain Munro
      Participant

      Many thanks Alexander.

      I have added – now nothing shows up.

      I tired in both a Script Editor and Content Web Editor.

      Not sure why it is not showing up now – any ideas ?

      Iain

    • #13905
      Alexander Bautz
      Keymaster

      Are you using SharePoint 2013?

      Press F12 to bring up the developer console, and click “Console”. Post any error messages you see there when you load the page.

      Alexander

    • #13921
      Iain Munro
      Participant

      Hi Alexander

      Thanks for the reply.

      Yes, I am using SharePoint 2013.

      Found the error – it does not like the calculated column – see image.

      Iain

    • #13927
      Alexander Bautz
      Keymaster

      Hi,
      I have changed the code to calculate the diff from the dates directly (no need for the calculated column). You must change the “DateReceived” and “DateShipped” to match your fields.

      <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
      <script type="text/javascript">
      setTimeout(function(){
      	(function(){
      		jQuery.ajax({       
      			"url": _spPageContextInfo.webServerRelativeUrl+"/_api/web/lists/GetById('"+_spPageContextInfo.pageListId.split(/{|}/).join("")+"')/items?$top=5000",   
      			"type": "GET",   
      			"contentType": "application/json;odata=verbose",
      			"headers":{ 
      				"Accept": "application/json;odata=verbose",
      				"X-RequestDigest" : jQuery("#__REQUESTDIGEST").val()
      			},   
      			"success": function(data){
      				var from, to, totItems = 0, totDays = 0, avgDays = 0;
      				jQuery.each(data.d.results,function(i,item){
      					if(item.DateReceived !== null && item.DateShipped !== null){
      						from = new Date(item.DateReceived);
      						to = new Date(item.DateShipped);
      						totDays += (to-from) / 86400000;
      						totItems += 1;
      					}
      				});
      				avgDays = totDays / totItems;
      				jQuery("table.ms-listviewtable").before("<div style='color:green;font-size:22px;border:1px #cccccc solid;padding:3px;'>Average days: "+parseInt(avgDays,10)+"</div>");
      			},
      			"error":function(err){
      				console.log(err);
      			}
      		});
      	})();
      },1000);
      </script>

      Alexander

    • #13933
      Iain Munro
      Participant

      Hi Alexander

      That was perfect – this worked just great.

      Do you know if it is possible to have two or three measures like this side by side ?

      Many thanks for turning around a working solution.

      Speak to you soon.

      Iain

    • #13948
      Alexander Bautz
      Keymaster

      Yes, this is no problem – are all calculations from the same list?

      If you can tell which calculations to do, I can show an example.

      Alexander

    • #13964
      Iain Munro
      Participant

      Thanks Alexander – I will think of something where I can have two or more that I need to show.

      One last question – how can I make the Average Days number two decimal places ?

      Iain

    • #13972
      Alexander Bautz
      Keymaster

      Hi,
      To have two decimals, just change this:

      parseInt(avgDays,10)

      to this:

      avgDays.toFixed(2)

      Alexander

    • #13986
      Iain Munro
      Participant

      Hi Alexander

      Just to keep it simple, what about having the Avg Days Late plus the total count, so I can get rid of the SharePoint default and keep everything on one line – see image

      Iain

      • This reply was modified 4 years, 5 months ago by Iain Munro.
    • #14017
      Alexander Bautz
      Keymaster

      Just change this line:

      jQuery("table.ms-listviewtable").before("<div style='color:green;font-size:22px;border:1px #cccccc solid;padding:3px;'>Total number of items: "+totItems+" Average days late: "+parseInt(avgDays,10)+"</div>");

      Alexander

    • #14033
      Iain Munro
      Participant

      Hi Alexander

      That is an awesome fix and good to use for all my views.

      The issue with this is that it gives you the total items in the whole list, not by the sorted list I have in place – so I got 116 items, but there are only 335 in this current list as it is filtered to show those items without a certain date being completed – is there a way to do that ?

      Iain

    • #14060
      Alexander Bautz
      Keymaster

      Are you using a filter on the list view (set by editing the view) or a dynamic filter by using the filter on top of the list columns?

      If you use a static filter it can be built into the REST query, but if you use dynamic filter it needs some more code to pull the active filter from the URL and use it in the query.

      Alexander

    • #14069
      Iain Munro
      Participant

      Hi Alexander

      Thanks

      I am just using the static filter built into the list view – field is not equal to blank etc.

      Iain

    • #14081
      Alexander Bautz
      Keymaster

      OK, please post the filter criteria here, but please provide the internal name of your fields.

      Alexander

    • #14083
      Iain Munro
      Participant

      Hi Alexander

      Date Sent – Final
      is equal to
      <blank>

      Field name is Date_x0020_Sent_x0020__x002d__x00

      Iain

    • #14108
      Alexander Bautz
      Keymaster

      I see, you should be able to change this line:

      "url": _spPageContextInfo.webServerRelativeUrl+"/_api/web/lists/GetById('"+_spPageContextInfo.pageListId.split(/{|}/).join("")+"')/items?$top=5000",   

      like this:

      "url": _spPageContextInfo.webServerRelativeUrl+"/_api/web/lists/GetById('"+_spPageContextInfo.pageListId.split(/{|}/).join("")+"')/items?$filter=Date_x0020_Sent_x0020__x002d__x00 ne null&$top=5000",   

      complete code:

      <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
      <script type="text/javascript">
      setTimeout(function(){
      	(function(){
      		jQuery.ajax({       
      			"url": _spPageContextInfo.webServerRelativeUrl+"/_api/web/lists/GetById('"+_spPageContextInfo.pageListId.split(/{|}/).join("")+"')/items?$filter=Date_x0020_Sent_x0020__x002d__x00 ne null&$top=5000",   
      			"type": "GET",   
      			"contentType": "application/json;odata=verbose",
      			"headers":{ 
      				"Accept": "application/json;odata=verbose",
      				"X-RequestDigest" : jQuery("#__REQUESTDIGEST").val()
      			},   
      			"success": function(data){
      				var from, to, totItems = 0, totDays = 0, avgDays = 0;
      				jQuery.each(data.d.results,function(i,item){
      					if(item.DateReceived !== null && item.DateShipped !== null){
      						from = new Date(item.DateReceived);
      						to = new Date(item.DateShipped);
      						totDays += (to-from) / 86400000;
      						totItems += 1;
      					}
      				});
      				avgDays = totDays / totItems;
      				jQuery("table.ms-listviewtable").before("<div style='color:green;font-size:22px;border:1px #cccccc solid;padding:3px;'>Total number of items: "+totItems+" Average days late: "+parseInt(avgDays,10)+"</div>");
      			},
      			"error":function(err){
      				console.log(err);
      			}
      		});
      	})();
      },1000);
      </script>

      Please note that you must change the field names in my code snippet to match yours.

      Alexander

Viewing 23 reply threads
  • You must be logged in to reply to this topic.