Is it possible to implement a Status Indication like a \Traffic Light\" without Coding in a Sharepoint List?"

  • 21 August 2018
  • 5 replies
  • 72 views
T
Badge +1

Dear Community, 

currently I am working on a status indication column like a "traffic light" for a Sharepoint list that is able to display a green/yellow/red light depending on the fulfillment of a task. I've found very interesting blogs from users which are doing quite similar things via  

https://www.abelsolutions.com/creating-a-simple-kpi-column-in-sharepoint-2013-list-views-using-js-link/ 

Ampelfunktion für Aufgabenlisten hinzufügen – Nico Thiemer – SharePoint Enthusiast seit 2001 

My requirements for this status indication are a bit more demanding - the column logic has to check two things at the same time. First, how much time is left until a deadline (4months, 3months, ..., down to 1month). Second, what is the fulfillment status task considering the time left until deadline. This means that a task fulfillment of 10% and 4months left would i.e. result in a yellow light, whereas 10% and 3 months left would result in a red light. So it is basically a comparison of time until deadline and task fulfillment status. 

As I am not a coder, is there a way without using Javascript? Nevertheless I've already built something like this, trying to use the approaches from the colleagues of the blogposts above - could you please tell me if this way is fine/ i've forgotten anything?

218823_pastedImage_3.png

My reference variables are _SNR_Status, displaying the task fulfillment in % and _KPI_SoP, representing the Deadline in date format. The other variables lessone,.... lessfour represent the time until deadline (counting from _KPI_SoP backwards)

Thanks and best regards

Thomas

5 replies

E
Userlevel 5
Badge +14

you can create calculated list field which's output will be a HTML snippet.

HTML snippet might be simply a text highlighted in different colors, or it might be a link to images that present different statuses.

see an example - https://community.nintex.com/message/75953-re-bold-a-list-item?commentID=75953#comment-75953 

T
Badge +1

Hi Marian,

thank you for your support with this case. I've tried a few things in the meantime according to your example and other blogs but nothing seems to work. Could you please provide some more support? 

Regarding calculated fields, I've created the following formula:

IF( AND[BTE_Status]>="25,0 %", ([KPI_SoP]-[Today] <120); "<DIV style='background-color:red' </DIV>"; [SNR_Status])

The intention of this reduced formula is to set the background colour of the column to red, if the conditions are met that the information of a task fulfillment (in %) in the BTE_Status Column is equal or greater than 25 % AND the SoP Date, represented by KPI_SoP column is less than 120 days away. The 120 days shall be compared to todays date by subtracting a future SoP date from KPI_SoP from todays date.

Sharepoint throws an error after clicking "OK". 

As I have to implement quite some logic into this tiny column, is this feasable with the calculated field? Thank you very much in advance! 

The second thing i've tried is using JavaScript - also with a reduced aspect of the whole requirement:

218873_pastedImage_1.png

Unfortunately this also did not cause any effect. The intention here is to change the color of a whole row. Is it possible to adjust this to only change the color of a column by i.e. replacing "colorCodeRows" with "colorCodeColumns"? 

The code is intended to have the same functionality as the formula above. The difference here is that an additional variable, representing 4 months before SoP has been added.

Thank you in advance for your support. 

Regards

Thomas

E
Userlevel 5
Badge +14

Sharepoint throws an error after clicking "OK". 

there are several issues with the formula...

- you miss parenthesis for an AND function

- you have to compare percentage value like: [BTE_Status]>=0.25

- you used two type of expression separators - comma and semicolon. depending on your site setting only one of them can work

further notes:

- your HTML snippet is not a valid HTML code

- note if your output is a HTML snippet, it replaces HTML code of respective cell. so you cannot just set a style, you should provide a cell value as well. if you do not provide any value, you will not notice different style on an empty value

- [Today] placeholder is only evaluated on item change. so until you are not going to change item daily, it will not recalculate based on current date as you may expect.

try formula like

IF( AND([BTE_Status]>=0.25; ([KPI_SoP]-[Today] <120)); "<DIV style='background-color:red'>"& [SNR_Status] &"</DIV>"; [SNR_Status])‍

The second thing I've tried is using JavaScript

have a look eg.on this examples how to use CSR to customize list view styles

CSR Row Highlighting - assistance needed  

Is it possible to adjust this to only change the color of a column by i.e. replacing "colorCodeRows" with "colorCodeColumns"? 

simple change of function name doesn't change anything.

you have to change style of single cell instead of whole rows.

just off top of my head it might look like

row["KPI_SOP"].style.backgroundColor = "0x008000"‍‍
T
Badge +1

Dear Marian, 

thank you for your support with this issue and giving me the right direction - after spending quite some time on this task I do finally have a quite advanced prototype I can work with: 

First of all - I did not follow the JavaScript path because of missing JS skills... Instead I worked out the following formula for a calculated column  (Legend: "IF" equals "WENN", "UND" equals "AND"):

218918_pastedImage_2.png

As you see I've also switched from coloring the whole column to using Sharepoint Default Icons:

"<img src='/_layouts/images/KPIDefault-0.GIF'border='0'/>"   Green

"<img src='/_layouts/images/KPIDefault-1.GIF'border='0'/>"   Yellow

"<img src='/_layouts/images/KPIDefault-2.GIF'border='0'/>"   Red

To also get a day reference I've created a "Days_until_SoP" column which is displaying the time left until SoP by subtracting SoP Date and Creation Date of the task.

I had two main issues to cope with. First, Sharepoint does only allow 7 nested if statements within one column. This can be fixed by using an "&" - see also: 

office 365 - What is the workaround for limit in nested if statements in sharepoint calculated column? - SharePoint Stac… 

Second, there is a limit of 1024 characters for a calculated column. To work around this, I've splitted the code (Lines 1-7 and Lines 9-12) and created a calculated column for every part of the code (SNR_Indic01 and SNR_Indic02). The last step ist to again combine both columns with a third calculated column, using a concatenate/verketten formula:

=VERKETTEN(SNR_Indic01;SNR_Indic02)

Maybe somebody with more sophisticated coding skills than me can work a shorter version of this.. Hope this also helps somebody else with similar issues.

Regards

Thomas

E
Userlevel 5
Badge +14
Maybe somebody with more sophisticated coding skills than me can work a shorter version of this

could you post your formula(s) in a plain text format so one can format them in a readable&understandable format?

as well explain the logic you want to implement with the formulas.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings