SharePoint Online List Calculated Field HTML Tags is not working

Question

in SharePoint migration process we found a list having two calculcated fields. 1. Color: using if condition based on field values, field will get/Set the color. 2. Display: will display the Title value with some color(getting from color field)

Color Field formula, and its working fine.

=IF([Status]="DEL","Mag",IF([Status]="PEN","Mag",IF([Status]="SENT","Mag",IF([Status]="WITHD","Gray",IF([Category]="","Blue",IF([Category]="Maint","Blue",IF([Category]="Plan","Blue",IF([Category]="Forced","Darkorange",""))))))))

Actual issue is here. Display Field: formula

="<span style='width:100%;display:inline-block;text-align:center;border:1px solid "&Colour&";color:"&Colour&";cursor:pointer;text-decoration:none;'>"&Title&"</span>"

formula its just getting color field value and style for Title field value. if i use above formula Title value is displaying but HTML tags also displaying before and after. just added span tag with title then i understand its issue with HTML or SPO not supporting

ok, MS has stopped this feature here. But how to solve this issue using column formatting in the column settings page.

kindly help me with complete steps, with code also.

Appreciate your help

First of all i need to get Title field value, using below code i can get.

{ "$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json", "elmType": "div", "txtContent": "[$Title]" }

i need to apply if condition to set color Color formula

=IF([Status]="DECLINED","Magenta",IF([Status]="PENDING","Magenta",IF([Status]="SENT","Magenta",IF([Status]="WITHDRAWN","Gray",IF([Category]="","Blue",IF([Category]="Maintenance","Blue",IF([Category]="Planned","Blue",IF([Category]="Forced","Darkorange",""))))))))

can you help me using above html code how to convert in to JSON, then we need to set style?

Answer 1

Save your calculated column type as "Number" column. It will get rendered if the formula is correct.

Note: This will only work in CLASSIC MODE as mentioned here.

If you are using your site in Modern SharePoint mode, then HTML formatting in SharePoint column is not available in SharePoint Online. Alternatively, you should use JSON Formatting on the column which you want to modify the view for.

Try this formula on the Title field.

{
    "$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json",    
    "elmType": "div",
    "txtContent": "@currentField",
    "style": {
            "color": "[$color]",
            "text-align": "center",
            "border": "1px solid [$color]",
            "cursor": "pointer",
            "text-decoration": "none" 
    }
}

In [$color] - "color" is the internal name of your color column as you have used in your formula.