1

I have ten list items that work back from one date field. I want to enter a date in the 'Open Date' field in list item 1 and then set a date field in item 2 to be -7 days and then set a date field in item 3 to be -14 days and then so on for the remainder of the list items. I need this to be displayed in one view so I can't have 10 different calculated columns. Is there a way of doing this? Thanks

4

As Calculated Columns do not work across Items you have to use a Workflow to alter the other items.

But..

You have to know I have a fetish for Calculated Columns

The million dollar question is how you order your 10 items

You can achieve this:

With a Calculated Column with some JavaScript

=IF(ISBLANK( [OpenDate] )
    ,"<img src=/_layouts/images/blank.gif onload=""{"
        &"  var TR=this;while(TR.tagName!='TR'){TR=TR.parentNode}"
        &"  this.nextSibling.innerHTML=(TR.rowIndex-1)*-7;"
        &"}""><button onclick=""{alert(this.innerHTML);}""></button>"
    , [OpenDate]
   )

All you need to add is some JSOM update code

(update #1) Always in for a challenge...

Can be done with one Calculated Column set to datatype=Number and Formula:

=IF(OpenDate,"<span id='od'>"
&TEXT(OpenDate,"mm/dd/yyyy")
&"","<img src=/_layouts/images/blank.gif onload=""{"
&"var TR=this;while(TR.tagName!='TR'){TR=TR.parentNode}"
&"var ID=TR.id.split(',')[1];"
&"var d=document.getElementById('od').innerHTML.split('/');"
&"var newd=new Date( d[2] , d[0]-1 , ~~d[1]+(TR.rowIndex-1) * -3 );"
&"window.updateDate=function(me,setdate){"
&"    var TR=me;while(TR.tagName!='TR'){TR=TR.parentNode}"
&"    var ID=TR.id.split(',')[1];"
&"    var clientContext=new SP.ClientContext.get_current();" 
&"    var list=clientContext.get_web().get_lists().getById(SP.ListOperation.Selection.getSelectedList());" 
&"    var item=list.getItemById( ID );" 
&"    clientContext.load(item);" 
&"    item.set_item('OpenDate',new Date(setdate));" 
&"    item.update();" 
&"    clientContext.executeQueryAsync(" 
&"     Function.createDelegate(this, function(){" 
&"        AJAXRefreshView({currentCtx:ctx,csrAjaxRefresh:true},1);" 
&"     }));"
&"};"
&"this.nextSibling.innerHTML=newd;"
&"}"">"
&"<button style=""width:250px;cursor:pointer;"""
&" onclick=""{event.preventDefault();window.updateDate(this,this.innerHTML);}""></button>")

Notes:

  • the IMG load triggers the setting of the button label.
  • offset days is calculated in the Date function (~~ converts a string to an integer)
  • onclick the update function is called, we need to pass which button was called, and I have added the innerHTML as well so the function can be reused.
  • The function itself is created for every listitem on the window.. bit dirty.. but works
  • inside the function you have to get the TR and ID again... you could do a JavaScript bind() but then need a function array (one for each item)
  • you can not call update straight away because everything is loading inline and the SharePoint Context does not exist yet. Automation sure is possible but requires more code
5
  • Right ok, thanks for confirming. I found this post that explains how it can be done by workflow: sharepoint.stackexchange.com/questions/142475/…
    – JazzyN
    Aug 19 '15 at 10:26
  • I'd suppose this can be done using JSLink as well. Maybe on OnPostRender() we can loop through the rows and change the td? Not sure about this though. Can you confirm, @DannyEngelman?
    – Akhoy
    Aug 19 '15 at 10:36
  • It can be done with any type of coding that can change Items. First question is.. How do you determine the order of those 10 items? Aug 19 '15 at 13:05
  • could you not just use the view to order!!?
    – JazzyN
    Aug 20 '15 at 8:27
  • Yes, the whole ordering is done in the View.. With what is up to you.. I used reverse sort on OpenDate and Task Name as second sort. You could also add an extra Order column to force an order. Aug 20 '15 at 9:41

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.