3

I'm having trouble formatting the JSON for a column called "SLA Day Counter". This column just counts the days between when the request was received and when it was actually handled. The request should be handled within 3 days of receipt unless it's a website review request which should be handled within 10 days. There is another column called "Initiative" that identifies what type of request it is. The problem is there are 4 website initiatives (Website-Full/New Review, Website-Other, Website-Business, Website-Update). I tried to convince the team to only use one but they are adamant on keeping those classifications.

Anyways, I created the following formatting rules using the simple formatting tool on SharePoint:

  1. IF SLA day counter is greater than 3 AND IF Initiative does not equal to Website-Full/New Review, cell will show as red
  2. IF SLA day counter is greater than 3 AND IF Initiative does not equal to Website-Other, cell will show as red
  3. IF SLA day counter is greater than 3 AND IF Initiative does not equal to Website-Business, cell will show as red
  4. IF SLA day counter is greater than 3 AND IF Initiative does not equal to Website-Update, cell will show as red
  5. IF SLA day counter is greater than 10 AND IF Initiative is equal to Website-Full/New Review, cell will show as red
  6. IF SLA day counter is greater than 10 AND IF Initiative is equal to Website-Other, cell will show as red
  7. IF SLA day counter is greater than 10 AND IF Initiative is equal to Website-Business, cell will show as red
  8. IF SLA day counter is greater than 10 AND IF Initiative is equal to Website-Update, cell will show as red

However, even if the Initiative is Website-Update and the SLA day counter is only at 7 days, it will still show as red. Please help! :'O

1
  • Check my answer, if SLA = 7 days and Initiative = Website-Update it shouldn't be red ♥ Jan 5 at 23:49
2

1.1 Trying to understand


First of all we need to simplfy your business requirement into a simple set of a Logical condition to make it for the Computer to understand it :

Here, from all the condition you have mentioned, the column become RED only if :

[The SLA Day Counter > 3] AND Initiative != ["Website-Full","New Review", "Website-Business","Website-Other","Website-Update"]

OR

The SLA Day Counter > 10 AND Initiative = ["Website-Full","New Review", "Website-Business","Website-Other","Website-Update"]


1.2 Solution


1-Create a new column "Calculated Column" and name it "Color" for example, the type make it "Single line of text"

enter image description here

2- Copy and paste the following formula :

=IF(OR(AND([SLA Day Counter]>3,AND(Initiative<>"Website-Full",Initiative<>"New Review",Initiative<>"Website-Other",Initiative<>"Website-Business",Initiative<>"Website-Update")),AND([SLA Day Counter]>10,OR(Initiative="Website-Full",Initiative="New Review",Initiative="Website-Other",Initiative="Website-Business",Initiative="Website-Update"))),"Red","Green")

3- Now check that column, it will write "Red" if the condition is true, else it will write "Green

4- Next step, format the "SLA Counter Day" Just like that :

enter image description here


1.3 Results


Here is the results I got :

enter image description here


2- Second Solution


Here, I am going to explain my second solution for your case following the Algebra logical expressions,

Let's name your following condition as follow :

  • A = SLA Day Counter > 3
  • B = Initiative != "Website-Full"
  • C = Initiative != "New Review"
  • D = Initiative != "Website-Other"
  • E = Initiative != "Website-Business"
  • F = Initiative != "Website-Update"
  • X = SLA Day Counter > 10

So if we express your business need in a Mathematical format it will look like this :

(A && B && C && D && E && F) || (X && (B || C || D || E || F))

This is a little bit complicated and difficult to implement it directly to computer, so first we need to SIMPLIFY it using "Boolean expression simplification" Methods which called Disjunctive normal form (DNF)

Results : A && B && C && D && E && F || X

Interpretation :

[The SLA Day Counter > 3] AND Initiative != ["Website-Full","New Review", "Website-Business","Website-Other","Website-Update"]

OR

[The SLA Day Counter > 10]

So going to SharePoint, your solution will look like this :

enter image description here

And here is it, it gave me the same results :

enter image description here

Please accept the answer if it works.

6
  • OMGGGG IT WORKED. Thank you. You're amazing!!!
    – rilakkuma
    Jan 6 at 16:31
  • Wait!! This only works if I don't hide the colour column. Is there any way I can hide colour and still have the formatting?
    – rilakkuma
    Jan 6 at 16:35
  • 1
    Alright, I will come back to you when i figure out something it work ! Jan 7 at 12:55
  • 1
    @rilakkuma, check my updated answer Jan 7 at 14:29
  • Thank you so much. It works perfectly. Thank you for also taking the time to explain it :')
    – rilakkuma
    Jan 10 at 16:56
2

Give this format a try:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if((startsWith([$Initiative],'Website') && @currentField > 10) || (startsWith([$Initiative],'Website') == false && @currentField > 3),'red','')"
  }
}

Not sure what other format options you were going for, so this is pretty basic in that it just sets the background-color to red or nothing. I've simplified the logic a bit by using startsWith to make it easier to find those that begin with Website vs those that don't. I've also assumed you are applying this directly to the SLA Day Counter column so I've just used @currentField but if you are applying this elsewhere just swap those out with the [$SLADayCounter] (or whatever the internal name of the field is).

3
  • Hi there, I tried this but the SLA day counter goes red on website even if it's within the 10-day SLA. screenshot: i.stack.imgur.com/QPT7H.jpg
    – rilakkuma
    Jan 5 at 18:41
  • Did you paste the exact format I posted above? In your screenshot it looks like you are applying background classes to get that pink shade rather than just red. Here's the format above: i.stack.imgur.com/SbtKN.png Jan 5 at 20:33
  • I just changed 'red' to the hex code #FABBC3 but otherwise, I used the exact format you suggested. I tried it just now with 'red' and I get the same result.
    – rilakkuma
    Jan 6 at 16:37

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.