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, 2022 at 23:49

2 Answers 2

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, 2022 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, 2022 at 16:35
  • 1
    Alright, I will come back to you when i figure out something it work ! Jan 7, 2022 at 12:55
  • 1
    @rilakkuma, check my updated answer Jan 7, 2022 at 14:29
  • 1
    No problem, it was quite challenge and you made me remember old math days Jan 10, 2022 at 21:10
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, 2022 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, 2022 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, 2022 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.