3

I have a list with the columns Room Number, Temperature, and a column Temperature Change.

What I would like, is for Temperature Change to be a calculated like so:

When the user fills out a new form, they enter the Room Number and Temperature. Then there's a lookup that determines the last item submitted in the list with that same Room Number. It then gets that previously submitted list item and calculates Temperature Change from the difference between the current item's Temperature and the last item's temperature.

Resulting in something that looks like this:

Room Number Temperature Temperature Change  Date Submitted
A           20          blank               1/1/2015
B           22          blank               1/1/2015
A           25          5                   2/1/2015
B           24          2                   2/1/2015

Is there anyway to do this using Lookup and Calculated columns?

10
+50

Note:

in June 2017, Microsoft disabled the use of JavaScript in a Calculated Column

That means given answers may not apply for newer SharePoint versions

For long explanation and work arounds see:
June 13th 2017 Microsoft blocked handling HTML markup in SharePoint calculated fields - how to get the same functionality back


Original answer:

If you want the delta values displayed in a View like this:

  • You must create a View that Groups by Room and sorts by Date

  • A Calculated Column 'ChangeMonitor' with Formula
    set to datatype=Number to output HTML/JS
    can then use JavaScript code to calculate, display and color the deltas

Copy/paste this Formula to your Calculated Column Formula:

    ="<img src=/_layouts/images/blank.gif onload=""{_spYield(function(){"
    &" var delta=0,TR=GetAncestor(this,'TR'),"
    &" ID=TR.id.split(',')[1],"
    &" ctxRows=ctx.ListData.Row,"
    &" n=getItemIdxByID(ctxRows,ID);"
    &" if(GetAncestor(TR,'TABLE').rows[TR.rowIndex-1].hasAttribute('id')){"
    &"  delta=ctxRows[n].Temperature - ctxRows[n-1].Temperature;"
    &"  this.parentNode.innerHTML=(delta>0?'+':'')+delta;"
    &"  TR.style.backgroundColor=delta>0?'pink':'lightblue';"
    &"  }"
    &"}.bind(this),20);}"">"

Because this code is executed inline and not all SharePoint libraries have loaded yet, the execution is (brute force for 20 cycles) delayed with the (SharePoint) _spYield function.
Once SharePoints core.js is loaded we can use all those goodie functions SharePoint gives you for free.
Because the View is grouped and GroupHeaders are also TR tablerows some checking is required to determine if a Row is not the first row in a Group.

I haven't tested this but I think all this works in SP2010 as well

In SP2013 you can do it in CSR - Client Side Rendering, but requires about 20+ lines of boilerplate code, separate CSR file and JSlink for every View you want to use it in.

Recording Change

I have not used the Change column, the View Displays the Delta, it does not record it.

If you need to record the delta you need to add JSOM/REST code to update the Change field

ICC

With 2013 CSR - Client Side Rendering

With CSR you have easier control over the display of fields and can achieve:

I can not share the CSR code, client will sue me to bits

You have to loop though all items upfront, record deltas
Then display a bar for each item

J1 J5 ICSR

  • Thanks for your response, that screenshot looks great. I'm a little unclear on how to implement your solution though. Where does the JavaScript go? Do I have to buy this viewmaster365 thing? – Charles Clayton Nov 30 '15 at 17:19
  • Create the Calculated Column as described and paste the whole Formula in there... that's it – Danny '365CSI' Engelman Nov 30 '15 at 18:04
  • @crclayton you do not need to buy anything. You should add a new column to your list - a Calculated Column. When creating the calculated column, make sure the return data type is set to "Number", and then all that Javascript goes into the "Formula". Keep in mind that inorder for it to work, you will need to set up the view as Danny Engelman advises: grouped by Room and sorted by Date. This is because the script is relying on the HTML structure to figure out what the "previous" date was, not actually looking at date or room values. – Dylan Cristy Nov 30 '15 at 18:05
  • Oh thank you! Sorry, I misinterpreted "datatype=Number to output HTML/JS" as some sort of HTML/JS column type that wasn't a sharepoint feature and needed to be installed, I see what you're saying now. This worked. Only change was I had to add the code .replace(/(\d+),(?=\d{3}(\D|$))/g, '$1'); to make it interpret it as a number though. – Charles Clayton Nov 30 '15 at 23:23
  • cool to use a regular expression (which I still struggle with).. parseFloat() might be a bit easier to read. – Danny '365CSI' Engelman Dec 1 '15 at 8:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.