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

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

2 Answers 2

Reset to default
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.

Improve this answer
6
  • OMGGGG IT WORKED. Thank you. You're amazing!!! Commented 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? Commented Jan 6, 2022 at 16:35
  • 1
    Alright, I will come back to you when i figure out something it work ! Commented Jan 7, 2022 at 12:55
  • 1
    @rilakkuma, check my updated answer Commented Jan 7, 2022 at 14:29
  • 1
    No problem, it was quite challenge and you made me remember old math days Commented 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).

Improve this answer
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.sstatic.net/QPT7H.jpg Commented 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.sstatic.net/SbtKN.png Commented 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. Commented Jan 6, 2022 at 16:37

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.