Nov 05 2019 01:21 PM
Hello!
Need some help with formating columns based on what time they got, if its more or less, then I want it to change color. See pictures.
Sorry for my bad english, hope you guys can help me anyway :)
Nov 05 2019 08:29 PM
Nov 06 2019 06:55 AM
@Matti Paukkonen Hello! Those columns are "Calculated" Values
15min has formula:
=TEXT([Assigned technicians]-[case received];"h:mm:ss")
Response time has formula:
=TEXT(Started-[case received];"h:mm:ss")
Nov 06 2019 07:39 PM
I think it's not possible to do a date/time comparison to a field containing only time value.
You can use those date/time fields (case received, assigned technicians and started) for time comparison and apply column formattings to 15min and response time fields.
Nov 06 2019 07:58 PM - edited Nov 06 2019 08:01 PM
Here is an example for 15min column, just replace the field references with your fields and add $schema reference in the beginning (editor messes up the links :( )
{
"elmType": "div",
"attributes": {
"class": {"operator":"?",
"operands":[
{"operator":"<","operands":[
"[$assigned]",
{
"operator": "+",
"operands": [
"[$case]",
900000
]
}]},"sp-field-severity--good",""
]}
},
"children": [
{
"elmType": "span",
"style": {
"display": "inline-block",
"padding": "0 4px"
},
"attributes": {
"iconName": {"operator":"?",
"operands":[
{"operator":"<","operands":[
"[$assigned]",
{
"operator": "+",
"operands": [
"[$case]",
900000
]
}]},"CheckMark",""
]}
}
},
{
"elmType": "span",
"txtContent": "@currentField"
}
]
}
Nov 06 2019 11:46 PM - edited Nov 07 2019 12:14 AM
Thanks, I managed to get the color and checkmark, it looks good. But is there anyway to make <0:15:00 it will become green with a checkmark and when >0:15:00 it will become red with a warning symbol?
Nov 07 2019 12:30 AM
@288088, just modify formatting a little bit.
For background color change this:
"sp-field-severity--good",
""
to
"sp-field-severity--good",
"sp-field-severity--blocked"
And for icon
"CheckMark",
""
to
"CheckMark",
"ErrorBadge"
Those out-of-the-box css classes and icons can be found here: https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting#apply-co...
Nov 07 2019 02:15 AM
I'm sorry but i'm really new to this, what do you mean by "just modify formatting a little bit" I cant get it to become red or green depending if the minutes are above or below 0:15:00 :( What line should I format?
Nov 07 2019 02:23 AM - edited Nov 07 2019 02:24 AM
Here is a complete example
{
"elmType": "div",
"attributes": {
"class": {"operator":"?",
"operands":[
{"operator":"<","operands":[
"[$assigned]",
{
"operator": "+",
"operands": [
"[$case]",
900000
]
}]},"sp-field-severity--good","sp-field-severity--blocked"
]}
},
"children": [
{
"elmType": "span",
"style": {
"display": "inline-block",
"padding": "0 4px"
},
"attributes": {
"iconName": {"operator":"?",
"operands":[
{"operator":"<","operands":[
"[$assigned]",
{
"operator": "+",
"operands": [
"[$case]",
900000
]
}]},"CheckMark","ErrorBadge"
]}
}
},
{
"elmType": "span",
"txtContent": "@currentField"
}
]
}
Nov 07 2019 03:36 AM
I copied your code and pasted inte the format for the "15min" column and changed
"[$case]",
to
"[case received]",
and
"[$assigned]",
to
"[Assigned technicians]",
but it still doesnt work :( I feel like i'm misunstanding something?
{
"$schema": "<a href="https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json" target="_blank">https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json</a>",
"debugMode": true,
"elmType": "div",
"attributes": {
"class": {
"operator": "?",
"operands": [
{
"operator": "<=",
"operands": [
"[Assigned technicians]",
{
"operator": "+",
"operands": [
"[case received]",
900000
]
}
]
},
"sp-field-severity--good",
""
]
}
},
"children": [
{
"elmType": "span",
"style": {
"display": "inline-block",
"padding": "0 4px"
},
"attributes": {
"iconName": {
"operator": "?",
"operands": [
{
"operator": "<=",
"operands": [
"[Assigned technicians]",
{
"operator": "+",
"operands": [
"[case received]",
900000
]
}
]
},
"CheckMark",
""
]
}
}
},
{
"elmType": "span",
"txtContent": "@currentField"
}
]
}
Nov 07 2019 03:52 AM
You need to use field internal names and reference fields like [$fieldName].
Nov 07 2019 05:15 AM - edited Nov 07 2019 05:28 AM
So I found the fieldname for the column "15min", it is "Inom_x0020_15_x0020_minuter".
So I pasted it into the fields with "[$Inom_x0020_15_x0020_minuter]" but it still doesnt work.
What does the "900000" Stand for? It is 0:15:00 minutes?
{
"$schema": "<a href="<a href="https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json" target="_blank">https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json</a>" target="_blank"><a href="https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json</a" target="_blank">https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json</a</a>>",
"debugMode": true,
"elmType": "div",
"attributes": {
"class": {
"operator": "?",
"operands": [
{
"operator": "<",
"operands": [
"[$Inom_x0020_15_x0020_minuter]",
{
"operator": "+",
"operands": [
"[$Inom_x0020_15_x0020_minuter]",
900000
]
}
]
},
"sp-field-severity--good",
"sp-field-severity--blocked"
]
}
},
"children": [
{
"elmType": "span",
"style": {
"display": "inline-block",
"padding": "0 4px"
},
"attributes": {
"iconName": {
"operator": "?",
"operands": [
{
"operator": "<",
"operands": [
"[$Inom_x0020_15_x0020_minuter]",
{
"operator": "+",
"operands": [
"[$Inom_x0020_15_x0020_minuter]",
900000
]
}
]
},
"CheckMark",
"ErrorBadge"
]
}
}
},
{
"elmType": "span",
"txtContent": "@currentField"
}
]
}
Nov 07 2019 11:29 PM
@288088, you need to replace $case in my example with internal name of case received and $assigned with internal name of Assigned technicians. Sorry, I didn't clarify that.
Value 900000 is 15 minutes, it's calculated like 15 x 60 x 1000.
Nov 07 2019 11:50 PM
Nov 08 2019 02:07 AM
@288088 This section of your code is incorrect (the part that makes it go red):
"attributes": {
"iconName": {
"operator": "?",
"operands": [
{
"operator": "<",
"operands": [
"[$Inom_x0020_15_x0020_minuter]",
{
"operator": "+",
"operands": [
"[$Inom_x0020_15_x0020_minuter]",
900000
]
}
]
},
"CheckMark",
"ErrorBadge"
]
}
}
This: "operator": "<"
Needs to be this: "operator": ">"
Also...
The first section of code (the part that makes it go green) should be less than or equal to 15 minutes otherwise if they hit 15 minutes exactly it won't be green or red.
So the part that makes it go green should have this: "operator": "<="
T.
Nov 08 2019 02:44 AM
@288088, you can save the formatting without errors?
For me, my example works correctly:
Can you send me your version as an attachment? I can check it out.
Nov 08 2019 03:02 AM
Yes I can save the format without errors. My columns "ärende inkommet" and "Ärende tilldelat" is just text based column and i've copy pasted the date and time from another program because I need seconds in it too.
This is the code I tried from @tonyknibb
{
"$schema": "<a href="https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json" target="_blank">https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a>",
"debugMode": true,
"elmType": "div",
"attributes": {
"class": {
"operator": "?",
"operands": [
{
"operator": ">",
"operands": [
"[$_x00c4_rende_x0020_tilldelat]",
{
"operator": "+",
"operands": [
"[$_x00c4_rende_x0020_inkommet]",
900000
]
}
]
},
"sp-field-severity--good",
"sp-field-severity--blocked"
]
}
},
"children": [
{
"elmType": "span",
"style": {
"display": "inline-block",
"padding": "0 4px"
},
"attributes": {
"iconName": {
"operator": "?",
"operands": [
{
"operator": "<=",
"operands": [
"[$_x00c4_rende_x0020_tilldelat]",
{
"operator": "+",
"operands": [
"[$_x00c4_rende_x0020_inkommet]",
900000
]
}
]
},
"CheckMark",
"ErrorBadge"
]
}
}
},
{
"elmType": "span",
"txtContent": "@currentField"
}
]
}
Nov 08 2019 03:48 AM
This is what it looks like with your code:
_x00c4_rende_x0020_tilldelat = Ärende tilldelat
_x00c4_rende_x0020_inkommet = Ärende inkommet
{
"$schema": "<a href="https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json" target="_blank">https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a>",
"debugMode": true,
"elmType": "div",
"attributes": {
"class": {
"operator": "?",
"operands": [
{
"operator": "<",
"operands": [
"[$_x00c4_rende_x0020_tilldelat]",
{
"operator": "+",
"operands": [
"[$_x00c4_rende_x0020_inkommet]",
900000
]
}
]
},
"sp-field-severity--good",
"sp-field-severity--blocked"
]
}
},
"children": [
{
"elmType": "span",
"style": {
"display": "inline-block",
"padding": "0 4px"
},
"attributes": {
"iconName": {
"operator": "?",
"operands": [
{
"operator": "<",
"operands": [
"[$_x00c4_rende_x0020_tilldelat]",
{
"operator": "+",
"operands": [
"[$_x00c4_rende_x0020_inkommet]",
900000
]
}
]
},
"CheckMark",
"ErrorBadge"
]
}
}
},
{
"elmType": "span",
"txtContent": "@currentField"
}
]
}
Nov 08 2019 11:09 AM
Solution
I managed with another code to get the result that I wanted, but thanks for the effort!
{
"$schema": "<a href="https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json" target="_blank">https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a>",
"elmType": "div",
"attributes": {
"class": "=if(@currentField <= '02:00:00', 'sp-field-severity--good', if(@currentField >= '02:00:00', 'sp-field-severity--blocked', '"
},
"children": [
{
"elmType": "span",
"style": {
"display": "inline-block",
"padding": "0 4px"
},
"attributes": {
"iconName": "=if(@currentField <= '02:00:00', 'CheckMark', if(@currentField >= '02:00:00', 'Warning', '"
}
},
{
"elmType": "span",
"txtContent": "@currentField"
}
]
}
Nov 08 2019 11:09 AM
Solution
I managed with another code to get the result that I wanted, but thanks for the effort!
{
"$schema": "<a href="https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json" target="_blank">https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a>",
"elmType": "div",
"attributes": {
"class": "=if(@currentField <= '02:00:00', 'sp-field-severity--good', if(@currentField >= '02:00:00', 'sp-field-severity--blocked', '"
},
"children": [
{
"elmType": "span",
"style": {
"display": "inline-block",
"padding": "0 4px"
},
"attributes": {
"iconName": "=if(@currentField <= '02:00:00', 'CheckMark', if(@currentField >= '02:00:00', 'Warning', '"
}
},
{
"elmType": "span",
"txtContent": "@currentField"
}
]
}