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
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.
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
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.
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!
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.
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
- 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])
have a look eg.on this examples how to use CSR to customize list view styles
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"
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:
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:
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:
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.
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.