Using HTML to recalculate calculated column

Question

I wish to create an auto incremented "TaskId" list field. I had intended to create a new value that padded out the [Id], but obviously [Id] cannot be used as it is not available until after the item is created.

Our Sharepoint 2013 site is clamped down - we have no access to designer, workflows, scripts etc

The only thing that seems to have slipped through is the View Calculated Columns using HTML. (along the lines of

=<button onclick="alert('Hello World!')">Click Me</button>

How can I use this coding method to refresh a field?

I.e.

Display a button, and if the botton is clicked then the ID is correctly populated back into the row.

<button onclick="<Refresh [Id] with correct value. Refresh the column
 with the updated composite value of "Task"||[Id] ;Save this value
 >Id Updated</button>")

Answer 1

On vacation at Land's End (UK) I had some spare time to work on working code.

First of all, the tested and tried SP Workflow method of writing the ID value to another Column is the preferred method, as it runs server side and thus garanteed to execute.

As OP states he can not use Designer or any other type of CSR coding it is possible to do it with JavaScript code in a Calculated Column.

This runs Client side, so you are loosing some garantees.

It also depends on a View displayed, so should be fine in most cases because the New Form returns to the default View.. But.. this also can be overriden with the Source parameter in a NewForm.aspx url

All in all a method that works.. but certainly not preferred.

Here goes:

Create a new Column: TaskID

single line of text and defaul value: 0

Create a Calculated Column: setTaskIDfunction

this will contain JSOM code which does exactly the same as the normal Workflow trick.

It will set the TaskID column to the correct ID value; then all Calculated Column referencing this TaskID will be update (by SharePoint)

setTaskIDfunction:

="function setTaskID(TR){"
&"  var CTX=new SP.ClientContext.get_current();"
&"  var list=CTX.get_web().get_lists().getById(SP.ListOperation.Selection.getSelectedList());"
&"  var ID=TR.id.split(String.fromCharCode(44))[1];"
&"  var item=list.getItemById( ID );"
&"  CTX.load(item);"
&"  item.set_item("
&CHAR(39)
&"TaskID"
&CHAR(39)
&"  , ID);"
&"  item.update();"
&"    CTX.executeQueryAsync("
&"     Function.createDelegate(this, function(){"
&"        AJAXRefreshView({currentCtx:ctx,csrAjaxRefresh:true},1);"
&"     }));"
&"}"

The fromCharCode and CHAR functions are some workingarounds for dealing with single quotes, the (personal use only) Calculated Column Editor I use can't deal with them because they mess up JSON strings.

you can replace those parts in your code

fromCharCode is the same as ',' and CHAR(39) is only one single quote

I put this in its own Calculated Column because we'll use this in two executing functions. So you can leave this formula as Text, no need for the Number trick to execute it as HTML/JS in SharePoint and no need to display it in the View either, its just a building block for the next functions

Netx Calculated Column we do need to display in the View and set to Number:

Name: set

Formula:

=IF(INT(TaskID),TaskID,"<button onclick=""{"
&  setTaskIDfunction
&" var TR=this.parentNode.parentNode.parentNode;"
&" setTaskID(TR);"
&"}"">"
&"Set TaskID"
&TaskID
&"</button>")

The button will only be added to the View when taskID is still 0, otherwise it will display its (ID) value.

We have to pass the TR reference because inside the setTaskID function 'this' refers to the function itself, not the DOM button object. (yes, you could use call() or bind(), but for the average javascript coders this [sic] is much simpler)

This should do the job, users can now updated the TaskID column with one click.

Next step: automatic update

You can't execute the code above automatically on page load because the SP object used in the function is not loaded yet. Simplest approach it to use a timeout function, feel free to work in other (regular SharePoint) methods: Run javascript after display template loaded

Create a Calculated Column: trigger, display in the default View as Number:

Formula:

=IF(INT(TaskID),"","<img  src=""/_layouts/images/blank.gif""  onload=""{"
&setTaskIDfunction
&"var TR=this.parentNode.parentNode.parentNode;"
&"setTimeout(setTaskID(TR),1000);"
&"}"">")

Once the blank.gif is loaded it execute the onload event, which waits 1000 milliseconds (so the page gets time to load) then updates the TaskID column.

I haven't tested edge cases.

You can now use TaskID in other Calculated Columns.

Note: ID will still be 0 used in other Columns.

Columns will be displayed on View Forms so merge the Formulas into one Column. Here you would normally use SharePoint Designer to hide the Column... but OP does not have access to Designer. Theoretically you could create the Columns not in the SharePoint UI but with JSOM code; that way you can set the Column is Hidden parameter on creation... actually thats what my Editor does, but I can't share that because it can do more harm than good :-(