Forum Discussion

Chris_Boylan's avatar
Chris_Boylan
Copper Contributor
Dec 30, 2021
Solved

Sharepoint list format whole row using indexOf

Hoping this will be a simple question for someone who knows JSON. I'm new to JSON and have found two examples of conditional formatting I'm trying unpick to colour a whole row on a sharepoint list using indexOf to search for the string "Holiday". Our training calendar (sharepoint list) can include "School holidays", "Bank holidays", "Half term holidays" etc in the field 'Title'.

 

Changing the column colour works for the title field alone with the following indexOf example (using if)

 

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "attributes": {
    "class": "=if(indexOf(@currentField,'oliday') != -1, 'sp-css-backgroundColor-BgCornflowerBlue sp-field-fontSizeSmall sp-css-color-CornflowerBlueFont','')"
  }
}

 

 

And, changing the whole row background colour works with the following which does not use indexOf (using operators?)

 

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
  "additionalRowClass": {
    "operator": ":",
    "operands": [
      {
        "operator": "==",
        "operands": [
          "[$Training_x0020_Crs_x0020_Ttl]",
          "Holiday"
        ]
      },
      "sp-css-backgroundColor-BgCornflowerBlue sp-field-fontSizeSmall sp-css-color-CornflowerBlueFont",
      ""
    ]
  }
}

 

 

To me the logic in both examples looks to be achieved in slightly different ways and I've hit a wall trying to combine the code, does anyone know how to use indexOf condition to detect 'holiday' to conditionally format the whole row?

 

(In the code above it looks like sharepoint has kept 'Training Crs Ttl' as a reference to the field now renamed to Title but I don't think this has a bearing on the code)

 

Many thanks

 

Chris

 

  • Chris_Boylan 

     

    I haven't tested, but you should be able to replace the entire operator object that starts with opening brace on line 3 and ends on line 16 with the if statement on line 6 in first example.  Then replace @currentfield with [$Training_x0020_Crs_x0020_Ttl]

     

    The result will be the following:

    {
      "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
      "additionalRowClass": "=if(indexOf([$Training_x0020_Crs_x0020_Ttl],'oliday') != -1, 'sp-css-backgroundColor-BgCornflowerBlue sp-field-fontSizeSmall sp-css-color-CornflowerBlueFont','')"
    }

    I hope this helps.

    Don

    Please click Mark as Best Response & Like if my post helped you to answer or resolve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

     

3 Replies

  • Chris_Boylan 

     

    I haven't tested, but you should be able to replace the entire operator object that starts with opening brace on line 3 and ends on line 16 with the if statement on line 6 in first example.  Then replace @currentfield with [$Training_x0020_Crs_x0020_Ttl]

     

    The result will be the following:

    {
      "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
      "additionalRowClass": "=if(indexOf([$Training_x0020_Crs_x0020_Ttl],'oliday') != -1, 'sp-css-backgroundColor-BgCornflowerBlue sp-field-fontSizeSmall sp-css-color-CornflowerBlueFont','')"
    }

    I hope this helps.

    Don

    Please click Mark as Best Response & Like if my post helped you to answer or resolve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

     

    • Chris_Boylan's avatar
      Chris_Boylan
      Copper Contributor

      @Don Kirkham, thank you! it worked. To help me understand, do you know the reason there are two possible approaches to the logic? (the 'if' and the 'operator' examples in my opening post). Is it just the flexibility of the code and then the skill of the coder to get a more succinct expression?

      • Don Kirkham's avatar
        Don Kirkham
        MVP
        I don't know the reason behind the two options. While Microsoft uses the operator syntax when it takes the list options in the UI and converts them into the Advanced view, having the "if" option is great for people that have been working in Excel for years as the formulas are very similar to what is offered in Excel.

        Glad I could help!

Resources