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>")
Robert Lindgren
  • 24,520
  • 12
  • 53
  • 79
southskies
  • 5
  • 4
  • why create a new ID field, when there's is the internal, OOB, ID field for the same purpose? – MdMazzotti May 12 '15 at 07: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 07: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 07: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 08: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 08:17
  • Nope, you can't. see http://sharepoint.stackexchange.com/questions/22977/how-to-use-calculated-field-to-retrieve-the-id-of-the-item-in-the-sharepoint-200 – MdMazzotti May 12 '15 at 08:20
  • (just tried on my SP2013. As soon as you add a new item, the ID in the calculated column will evalute to 0) – MdMazzotti May 12 '15 at 08:20
  • Why don't you just create an GUID as a unique ID , and use that instead? – MdMazzotti May 12 '15 at 08:23
  • If you add a new column to your existing view and add in =[Id], you will see that it does display the value. – southskies May 12 '15 at 08:23
  • Yes, it does for existing rows. Try adding a new one, and you'll see you'll get a 0 – MdMazzotti May 12 '15 at 08:24
  • Yes, if you add an item, it will come up as 0 initially as you have stated, but you need to post processing to update the value. So, how to I create the GUID? – southskies May 12 '15 at 08:24
  • sorry to insist, but even if you edit the item, the ID still remains 0 (see http://postimg.org/image/t62k97td1/). As for the GUID creation, you need to resort to injecting some javascript in your NewForm (through a script web part) – MdMazzotti May 12 '15 at 08:29
  • like this: https://github.com/broofa/node-uuid – MdMazzotti May 12 '15 at 08:31
  • I suspect that it remains 0 as there is nothing to make it refresh. Adding a column will, which is why it works then. I'm not sure if the GUID will return me a sequenced number. And unfortunately, direct javascript in a NewForm has been disallowed too. – southskies May 12 '15 at 08:34
  • That Github link does not work. Can you check this? – southskies May 13 '15 at 01:14

1 Answers1

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 :-(

Community
  • 1
Danny '365CSI' Engelman
  • 21,176
  • 7
  • 35
  • 79