SharePoint Online list row formatting with the LookUp column string start with condition using JSON

Question

I have a project list with the color status as Blue-PRJ1, Blue-PRJ2, Blue-PRJ3, Green-PRJ1, Green-PRJ2, Green-PRJ3, etc.

The color status column is the LookUp type column. When the user selects the Blue-PRJ1 or Blue-PRJ2 or Blue-PRJ3 from the lookup column, the line of the row will be changed to the respective color like Blue, Green, Red, etc.

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
    "style": {
    "font-style": "ms-fontColor-alert"
   },
  "additionalRowClass": "=if([$ProjectStatusLookupColumn.lookupValue] == 'Blue-PRJ1', 'ms-bgColor-blue',if([$ProjectStatusLookupColumn.lookupValue] == 'Blue-PRJ2', 'ms-bgColor-blue',if([$ProjectStatusLookupColumn.lookupValue] == 'Blue-PRJ3', 'ms-bgColor-blue','')))"
}

I am handling this using the multiple if condition which is working fine. Now, I am trying to reduce the multiple if condition and use something like start with text.....for example, if the lookup column value starts with the Blue, then that line be blue, if starts with Green then that line is Green, and so on.

Is anyone implemented similar requirements or how can we handle the condition like "start with" in the lookup column in JSON formatting - SharePoint Online list?

Answer 1

Use below JSON code, it should work for you:

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
    "style": {
        "font-style": "ms-fontColor-alert"
    },
    "additionalRowClass": "=if(startsWith([$ProjectStatusLookupColumn.lookupValue], 'Blue'), 'ms-bgColor-blue', if(startsWith([$ProjectStatusLookupColumn.lookupValue], 'Red'), 'ms-bgColor-red', if(startsWith([$ProjectStatusLookupColumn.lookupValue], 'Green'), 'ms-bgColor-green', '')))"
}

Documentation: JSON Formatting syntax reference

Answer 2

Column formatting does have start with method.

You can use following method in expression:

https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json

I have prepared JSON Formatting according to your requirement.

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "box-sizing": "border-box",
    "padding": "0 2px",
    "overflow": "hidden",
    "text-overflow": "ellipsis"
  },
  "attributes": {
    "class": {
      "operator": ":",
      "operands": [
        {
          "operator": "startsWith",
          "operands": [
            "[$ProjectStatusLookupColumn.lookupValue]",
            "Blue"
          ]
        },
        "sp-css-backgroundColor-BgCornflowerBlue sp-field-fontSizeSmall sp-css-color-CornflowerBlueFont",
        {
          "operator": ":",
          "operands": [
            {
              "operator": "startsWith",
              "operands": [
                "[$ProjectStatusLookupColumn.lookupValue]",
                "Green"
              ]
            },
            "sp-css-backgroundColor-BgGreen sp-css-borderColor-CyanFont sp-field-fontSizeSmall sp-css-color-WhiteFont",
            {
              "operator": ":",
              "operands": [
                {
                  "operator": "startsWith",
                  "operands": [
                    "[$ProjectStatusLookupColumn.lookupValue]",
                    "Red"
                  ]
                },
                "sp-css-backgroundColor-BgRed sp-css-borderColor-CyanFont sp-field-fontSizeSmall sp-css-color-WhiteFont",
                ""
              ]
            }
          ]
        }
      ]
    }
  },
  "txtContent": "[$ProjectStatusLookupColumn.lookupValue]"
}

It will give output as below

Please mark this as answer if it works for you.