2

I'm trying to work out the code required for multiple-condition formatting of a column based on two others.

I can write multiple 'if' conditions in Excel style, but cannot get && statements to work no matter what I try, so help would be appreciated.

Below I have the code that works to format to two conditions, but I also need to add a second condition that basically ignores the ContentType of 'Folder'. So if 'date is x and ContentType is not Folder', then colour red and if 'date is y and ContentType is not Folder', then colour yellow.

{
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(@currentField <= @now - 15552000000,'#F99385', if(@currentField <= @now - 7776000000,'#FFB56B' '')",
    "padding": "0 4px"
  }
}
4
  • Instead of using &&, can you try using the AND? ie (@currentField <= @now - 15552000000) AND @ContentType != 'Folder' rather than (@currentField <= @now - 15552000000) && @ContentType != 'Folder'
    – KGlasier
    Apr 15, 2019 at 17:47
  • 1
    Thanks for the reply. Tried that but just blanks the column and doesn't show any data until I remove the 'AND contentType' bits. But I've just managed to figure out how to do it in longhand JSON now and it seems to be working. Not as easy to read or write as the Excel style, but at least it works.
    – JamesSnaps
    Apr 18, 2019 at 16:31
  • Sorry I'm late getting here, but I think I have your answer. Let me know if it works for you
    – Stevish
    Apr 30, 2019 at 20:02
  • Did Microsoft "forget" to include these important operators? And why are there "normal" calculated fields in Sharepoint, "backdoor" calculated fields to overcome the "forgetting" to make TODAY() and NOW() functions work, Excel formulas, DAX, etc. etc. Some mental abilities in Redmond have been through the liquidiser! Feb 14, 2022 at 7:52

2 Answers 2

3

You've basically got the "Or" idea down. You only need to switch it to "And," except without using &&'s or ||'s. Here's what I mean:

I'm going to split this out onto multiple lines so it's easier to see. Obviously, you will have to put it back onto one line to use it:

//Basic if syntax:
if(
    condition,
    resultIfTrue,
    resultIfFalse
)

//OR (like you have it)
if(
    condition1,
    resultIfTrue,
    if(
        condition2,
        result-If-c1-False-And-C2-True
        result-If-c1-False-And-C2-false
    )
)

//So, here's the AND:
if(
    condition1,
    if(
        condition2,
        result-If-c1-true-And-C2-True
        result-If-c1-true-And-C2-false
    ),
    result-if-c1-false
)

//So I think yours would look like this:
=if(
    [Content type != Folder], //not sure the code for this without looking it up
    if (
        @currentField <= @now - 15552000000,
        '#F99385', 
        if(
            @currentField <= @now - 7776000000,
            '#FFB56B',
            ''
        )
    ),
    ''
)

//Or, to stick with only the "Or" mentality, just switch to using == instead of !=
=if(
    [Content type == Folder],
    '',
    if (
        @currentField <= @now - 15552000000,
        '#F99385', 
        if(
            @currentField <= @now - 7776000000,
            '#FFB56B',
            ''
        )
    )
)
0

As a workaround for the lack of boolean operators such as AND, OR etc. in the JSON language used by SharePoint list formatting, * and + respectively, can be used instead. Since the IF function views 0 as false, and 1 or anything greater as true, + can be used instead of OR, and * instead of AND. The comparisons must be in their own brackets though, e.g:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if((([$Progress] != 'Not started') * ([$Progress] != '')) + ([$CurrentActivity] != ''), 'YES', 'NO')"
}

This can read as IF ([$Progress] != 'Not started' AND [$Progress] != '') OR [$CurrentActivity] != '', then ...

Use of + for OR, and * for AND can easily be verified as follows:

+-------+-------+-------+---+
|   A   |   B   |  OR   | + |
+-------+-------+-------+---+
| false | false | false | 0 |
| false | true  | true  | 1 |
| true  | false | true  | 1 |
| true  | true  | true  | 2 |
+-------+-------+-------+---+
+-------+-------+-------+---+
|   A   |   B   |  AND  | * |
+-------+-------+-------+---+
| false | false | false | 0 |
| false | true  | false | 0 |
| true  | false | false | 0 |
| true  | true  | true  | 1 |
+-------+-------+-------+---+

It's probably possible to use 1 - (condition) as a replacement for the NOT operator, although I haven't tested that.

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.