Column formatting using JSON

Contributor

Hi

 

I'm trying to format a column based on a column containing certain text. This column can have multiple values starting with: "On Hold". Example: "On Hold - Awaiting customer" or "On Hold - Awaiting Vendor". We may add more in the future, and I'm sure I would forget to update the column format.

Is there a like operator, so that I can do:

 

{
	"operator": "<like operator>",
	"operands": [
		"[$Status]",
		"On Hold*"
	]
}

Thanks.

10 Replies

Hi,

 

you can create a new calculated column [ColumnStatus7Characters] using a formula like =LEFT([ColumnStatus], 7) and than you can use the = operator in the JSON for column [Status], but checking column [ColumnStatus7Characters] for "On Hold". Not nice but a Workaround.

 

Regards

@Tomislav Karafilov Thanks for the workaround.

 

I'm still wondering if it is possible just in JSON.

Hi Alan,

I haven't played that much with column formatting so not sure, but give this solution a try as it will massively help you
https://github.com/SharePoint/sp-dev-solutions/tree/master/solutions/ColumnFormatter

Joel

Hi @Joel Rodrigues

 

I was trying for a quick solution using the new Column Formatting feature. Not really wanting to write code unless I have to.

 

Alan

Hi @Alan Trafford,

 

That project is a web part that gives you a friendly interface to build the JSON for column formatting. It also contains a wizard :)

Give it a try :)

 

Joel

Hi @Joel Rodrigues

 

I saw a demo of it on PnP Monthly Community Call - January 2018. It looks really good. That said, I'm still trying to avoid coding unless I have to. I think I'll be using @Tomislav Karafilov workaround, as it's easy to apply.

 

Alan

Hey,

Really good write up of what you can achieve with Column Formatting here: https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting

 

Doesn't look like there is a "like" type operator to do partial string matching / substring matching, so I am guessing you would have to either:

- specify each of the possible "On Hold - <hold reason>" values in the JSON

- in your list have a field for the value "On Hold" and a separate field for the reason

 

Good luck!

Hi @Nigel Witherdin

 

That's what I did in the end. The owner of the list did not want a new columns added, so I could not use @Tomislav Karafilov solution. In case it will help others, here's my final code:

 

{
    "$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json",
    "debugMode": true,
    "elmType": "div",
    "attributes": {
        "class": {
            "operator": "?",
            "operands": [
                {
                    "operator": "&&",
                    "operands": [
                        {
                            "operator": "<=",
                            "operands": [
                                "[$Finish]",
                                "@now"
                            ]
                        },
                        {
                            "operator": "||",
                            "operands": [
                                {
                                    "operator": "==",
                                    "operands": [
                                        "[$Status]",
                                        "Status1"
                                    ]
                                },
                                {
                                    "operator": "==",
                                    "operands": [
                                        "[$Status]",
                                        "Status2"
                                    ]
                                },
                                {
                                    "operator": "==",
                                    "operands": [
                                        "[$Status]",
                                        "On Hold - 1"
                                    ]
                                },
                                {
                                    "operator": "==",
                                    "operands": [
                                        "[$Status]",
                                        "On Hold - 2"
                                    ]
                                },
                                {
                                    "operator": "==",
                                    "operands": [
                                        "[$Status]",
                                        "On Hold - 3"
                                    ]
                                },
                                {
                                    "operator": "==",
                                    "operands": [
                                        "[$Status]",
                                        "On Hold"
                                    ]
                                }
                            ]
                        }
                    ]
                },
                "sp-field-severity--blocked",
                ""
            ]
        }
    },
    "children": [
        {
            "elmType": "span",
            "style": {
                "display": "inline-block",
                "padding": "0 4px"
            },
            "attributes": {
                "iconName": {
                    "operator": "?",
                    "operands": [
                        {
                            "operator": "&&",
                            "operands": [
                                {
                                    "operator": "<=",
                                    "operands": [
                                        "[$Finish]",
                                        "@now"
                                    ]
                                },
                                {
                                    "operator": "||",
                                    "operands": [
                                        {
                                            "operator": "==",
                                            "operands": [
                                                "[$Status]",
                                                "Status1"
                                            ]
                                        },
                                        {
                                            "operator": "==",
                                            "operands": [
                                                "[$Status]",
                                                "Status2"
                                            ]
                                        },
                                        {
                                            "operator": "==",
                                            "operands": [
                                                "[$Status]",
                                                "On Hold - 1"
                                            ]
                                        },
                                        {
                                            "operator": "==",
                                            "operands": [
                                                "[$Status]",
                                                "On Hold - 2"
                                            ]
                                        },
                                        {
                                            "operator": "==",
                                            "operands": [
                                                "[$Status]",
                                                "On Hold - 3"
                                            ]
                                        },
                                        {
                                            "operator": "==",
                                            "operands": [
                                                "[$Status]",
                                                "On Hold"
                                            ]
                                        }
                                    ]
                                }
                            ]
                        },
                        "Error",
                        ""
                    ]
                }
            }
        },
        {
            "elmType": "span",
            "txtContent": "@currentField"
        }
    ]
}

Depending on how your data is structured, you may want to restructure your data so that you can use the "Choice" column type to assign metadata to each record. This will simplify your overall implementation. It will also prevent user error and variations within "Single Line of Text" data entries. i.e. "On Hold" vs "OnHold" vs "On-Hold".

 

More recently, there is also a no-code solution for Choice, Date/Time, and Boolean column types that does not require knowledge of complex JSON to assign background colors.

No Code Column Formatting – SharePoint Online

 

This approach assumes 1 choice option for each variation between "On Hold - Awaiting customer" or "On Hold - Awaiting Vendor" respectively. You can define up to 24 color options at the moment.

My answer is late, but may be it could help others looking for similar solution.

 

We can use indexOf operator to check if string contains a particular substring. 

Eg. 

"txtContent": "=if(indexOf([$Status], 'On Hold') >= 0, 'On Hold', 'Released')"

Or something like this:

"background-color": "=if(indexOf([$Status], 'On Hold') >= 0, 'Yellow', 'Green')"

Check the syntax once again if it is not working.

 

For more details:

https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting