0

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>")
  • why create a new ID field, when there's is the internal, OOB, ID field for the same purpose? – MdMazzotti May 12 '15 at 7:43
  • The problem is that the ID is not set at the time that the item is created. Hence the reason why I am trying to create a button that staff can click on that will update any column that references the ID AFTER the item has been created. – southskies May 12 '15 at 7:46
  • that's the other part of your question that I don't understand. You cannot have a calculated column refer the ID field. Can you try to better explain what you are trying to do exactly ? – MdMazzotti May 12 '15 at 7:51
  • What I am needing is for a field [TaskId] to be available upon creation of an item. This will be used as a Task reference number. I can base this value upon the inbuilt [Id] value, but the problem is that [Id] is not populated until AFTER the item is created. To perform a post-creation process, most sites suggest the use of workflows or designer to do this, but we don’t have access to these. I am hoping that we may be able to embed some code into the example above, so that when the button is clicked, then any references to [Id] within that item will be refreshed with the updated value – southskies May 12 '15 at 8:07
  • Also, you can have the [Id] in a calculated column. However the value in that column is not correct until after the item has been created. – southskies May 12 '15 at 8:17
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 :-(

  • A brilliant solution. Works very well! – southskies Jun 1 '15 at 4:08
0

The ID used in Calculated Column Formulas is 0 for newly created items (because it gets its value from the database after all Formulas are evaluated on new item creation)

[Update: regular SharePoint method is to have a workflow write the ID value to another Column; this can be done with Javascript code as well]

You mention you do not have access to SharePoint Designer. You could add a Content Editor Webpart with code; or if even that is locked down you can add code to Calculated Columns (needs List edit rights) For in depth examples see http://www.viewmaster365.com/#/Create/

Word of warning.. be aware of typos.. SharePoint is cruel on Calculated Column Formula developers... only one error message... built your code step by step and with multiple Calculated Columns like the ViewMaster365.com examples.

If you mix the ViewMaster365.com Sum and Change Createdate examples you can execute the update code when a View loads.. saves the users from having to update an item with a button click. add ID to your (javascript) Formula.. if its 0 you know the item needs an update. Again... updating is enough.. so update the Title field with the same value

  • Sounds a great idea, but I am still missing something - it is still not working. I created a calc'd column that has the contents =ID&[Email Contact]&" <img src= ""_layouts/images/blank.gif "" onload= ""console.log( ' Log Entry created'); ""> " For some reason the ID's do appear on some of the columns (perhaps after updating the view?), but for new entries that have been created, it still has 0 in the ID value. I've put this type of code in another calculated cell w/o any ref to ID, hoping that the column that has the ID value may be indirectly updated. This didnt work either – southskies May 15 '15 at 5:19
  • Just to clarify, I have modified the code to be =ID&"<img src=""h t t p://www.oursite.com/blank.gif "" onload= ""console.log( ' ViewMaster executed on: "&[Description ]&" '); ""> " (but still no luck. – southskies May 15 '15 at 7:22
  • This is de documented behaviour of using ID in the Formula, it will be 0 for New untouched items. Only way to get the correct ID is using Javascript to extract it from the HTML TR row; as demonstrated in multiple examples on the ViewMaster365 site – Danny '365CSI' Engelman May 15 '15 at 17:00
  • Still having issues with the latest code: Comes up blank in the column =" <img src=""h ttp://oursite/blank.gif"" onload=""{" &"var TR=this.parentNode.parentNode.parentNode," &" item=ctx.ListData.Row[TR.rowIndex-1]," &" Tst=item['Id.'];" &"this.parentNode.innerHTML=Tst;" &"}"">" – southskies May 18 '15 at 4:13

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.