Alternating Row Styles with List Formatting

An easy way to make your list views far more readable is to provide alternating styles between rows. This is especially helpful for wide lists with lots of columns.

Importantly, the alternating colors need to alternate regardless of the content of the list items. This means that even with changing sorts and filters applied, the alternating styles should remain consistent. Up until a few days ago, this wasn’t possible with List Formatting. However, thanks to a new operation and magic string, applying alternating styles is super easy!

Although you can use this same basic concept for advanced visualizations within column formatting or row format style view formatting, the most common usage is to apply a color across the whole row for every other row. That’s what I’ll demonstrate here using a row class style view format.

@rowIndex

A new magic string has been added that will provide you the index (relative to the view) for a given row. The index starts at 0. So, when a view is rendered, the row at the top has a @rowIndex of 0, the next is 1, the next is 2 and so on. If the sort changes, whatever row just became the new top row has a @rowIndex of 0.

We can see this in action with a simple column format:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@rowIndex"
}
The @rowIndex value remains consistent based on render position!

Awesome! But just showing the index isn’t very helpful. Fortunately, a new operation has been added that makes using this value in an expression super easy.

Modulus (Remainder)

The modulus operator is the % sign. This operator returns the remainder left over when one operand is divided by a second operand. Here are some examples:

ExpressionResult
“=13 % 5”3
“=0 % 2”0
“=1 % 2”1
“=2 % 2”0
“=3 % 2”1

It’s those last 4 examples that are particularly relevant for us. By using the remainder operator with the second operand of 2, we can consistently get results for every other value!

Alternating Row Class

View formats provide an additionalRowClass property that allows us to apply a class(es) to whole rows. We can even do this conditionally! So, using what we learned above we can use this simple format to apply a class to every other row:

alternating-rowclass:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
  "additionalRowClass": "=if(@rowIndex % 2 == 0,'ms-bgColor-themeLighter ms-bgColor-themeLight--hover','')"
}

We are checking to see if the remainder of dividing the @rowIndex by 2 is 0 (even numbers will be 0 and odd numbers will be 1). When it’s 0, we use the UI Fabric theme classes to apply a theme color across the whole row. We also add an additional class to add a hover effect as well. Here’s the result:

So beautiful!
On the fly sorts? No Problem!
On the fly filters? No Problem!

That’s all there is to it!

Thank you SPTechCon Austin!

I had the honor of attending and speaking at SPTechCon West this week. It was great! I attended several really awesome sessions, attendees were super engaged, and I even got some sweet swag. Even better? I crushed Vlad Catrinescu at Mario Kart on the same big screen we both presented on the next day.

Getting the Most out of SharePoint Patterns and Practices (PnP)

Photo via David Warner II

On Monday I presented one of my favorite sessions. This session is like a sampler platter of the awesome stuff the PnP team and community has made available for everyone to use and learn from. There were lots of people who hadn’t heard of PnP, which means their jobs are now going to be so much easier. Whoo!

There was a lot of great feedback and participation. People were very excited about Page Transformation for Modernization and, as always, PnP PowerShell. The samples and contribution opportunities were also of great interest.

Slides: Getting the Most out of SharePoint Patterns and Practices

List Formatting in O365 and 2019

Earlier today I got to present on my other favorite topic: List Formatting. Unfortunately, I got over ambitious and tried to fill my session with tons of information AND demos. I ran out of time. Sadness.

It was a big crowd and lots of people were very excited about the amazing things you can do with List Formatting. I had lots of people asking questions afterwards and even helped setup some formats for attendees right in the room. That’s the power of List Formatting, we can apply them with no installations, deployments, or admin privileges!

Slides: O365 List Formatting

Thank you so much to all the attendees, speakers, sponsors, and organizers. SPTechCon has lots of user targeted information, but so many of the sessions went into a lot of depth that I left with lots of great tips and I’m sure everyone else did too!

Formatting Multi-Select Values in List Formatting

Multi-select values (choice or person fields where you can pick more than one) have traditionally been tough to work with in List Formatting. You can use the values directly but right away you’ll see formatting differences from the standard/default formatting:

“txtContent”: “@currentField.title”
“txtContent”: “@currentField”

Person fields get separated by a semi-colon and a space, choice values are separated by a comma and lose the space. Aw, come on!

Fortunately, you can have some control over how these are separated using the join function. The join function takes in an array as the first parameter and the separator text to use between items. Here are some samples:

ExpressionField ValueResult
“=join(@currentField, ‘, ‘)”[Water,Wine,Beer]Water, Wine, Beer
“=join(@currentField, ‘|’)” [Water,Wine,Beer] Water|Wine|Beer
“=join(@currentField, ‘ & ‘)” [Water,Wine,Beer] Water & Wine & Beer

That’s pretty slick!

If you read my last post about using the indexOf function to see if your text contains a given value you might think you could use this to see if the selected values contain one of the choices/person fields. Not quite…

Selection Contains a Value

The indexOf function works on strings not arrays. Multi-select fields are arrays internally and the indexOf function will always return -1 when applied directly to the array value.

Fortunately, we can either use the join function shown above or the toString function on our array value first, then we can easily apply our contains logic using the indexOf function!

I recommend using the join function so that you know exactly what you’re going to get since the toString will vary it’s separator depending on the field type (choice or person).

Here are some examples of combining these functions to see if a choice is selected:

ExpressionField ValueResult
“=if(indexOf(join(@currentField,”),’Egg’) != -1, ‘Yes’,’No’)” [Egg,Hat] Yes
“=if(indexOf(join(@currentField,”),’Hat’) != -1, ‘Yes’,’No’)” [Egg,Hat] Yes
“=if(indexOf(join(@currentField,”),’Egg’) != -1, ‘Yes’,’No’)” [Hat,Dog] No

We can use this technique within our formats to create some very interesting visualizations:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "font-size": "16px"
  },
  "children": [
    {
      "elmType": "span",
      "attributes": {
        "title": "Water",
        "iconName": "Precipitation",
        "class": "='ms-fontColor-' + if(indexOf(join(@currentField,''),'Water') != -1, 'themeDark', 'neutralLight')"
      },
      "style": {
        "padding": "0 2px"
      }
    },
    {
      "elmType": "span",
      "attributes": {
        "title": "Coffee",
        "iconName": "CoffeeScript",
        "class": "='ms-fontColor-' + if(indexOf(join(@currentField,''),'Coffee') != -1, 'themeDark', 'neutralLight')"
      },
      "style": {
        "padding": "0 2px 0 0"
      }
    },
    {
      "elmType": "span",
      "attributes": {
        "title": "Wine",
        "iconName": "Wines",
        "class": "='ms-fontColor-' + if(indexOf(join(@currentField,''),'Wine') != -1, 'themeDark', 'neutralLight')"
      },
      "style": {
        "padding": "0 2px"
      }
    },
    {
      "elmType": "span",
      "attributes": {
        "title": "Beer",
        "iconName": "BeerMug",
        "class": "='ms-fontColor-' + if(indexOf(join(@currentField,''),'Beer') != -1, 'themeDark', 'neutralLight')"
      },
      "style": {
        "padding": "0 2px"
      }
    },
    {
      "elmType": "span",
      "attributes": {
        "title": "\"Juice\"",
        "iconName": "TestBeaker",
        "class": "='ms-fontColor-' + if(indexOf(join(@currentField,''),'\"Juice\"') != -1, 'themeDark', 'neutralLight')"
      },
      "style": {
        "padding": "0 2px"
      }
    }
  ]
}

PnP Sample: multi-choice-icons

Now you can easily work with multi-select fields and apply dynamic formats that can take these fields from strings of text to meaningful visualizations.

Love List Formatting?

Join the Bi-weekly (every other Thursday) SharePoint Patterns and Practices special interest group for general development call where I will be presenting a new List Formatting Quick Tip on each call!

Also, come get the full picture in my sessions about List Formatting at SPTechCon in Austin in February, the North American Collaboration Summit in Branson in March, the SharePoint Conference in Las Vegas in May, or the European Collaboration Summit in Germany in May:

Formatting Values Using “Contains” in List Formatting

In my last post, I demonstrated applying conditional formats when text starts with a given value. But what if you just want to know if your text contains that value (beginning, middle, end, wherever)?

We can use that same indexOf function provided by List Formatting. This function tells you the first index (starting character position) of some text within your text. The index starts at 0 and if the text isn’t found then the result is -1. Here are some sample inputs and results:

ExpressionResult
“=indexOf(‘Unreliable Peanut’, ‘U’)” 0
“=indexOf(‘Unreliable Peanut’, ‘e’)” 3
“=indexOf(‘Unreliable Peanut’, ‘reliable’)” 2
“=indexOf(‘Unreliable Peanut’, ‘p’)” -1
“=indexOf(‘Unreliable Peanut’, ‘tasty’)” -1

As you can see, you can pass in a single character or a whole word/phrase. You can also see that this function is case-sensitive.

How does knowing the index help us? The key is that the result is always -1 when the value is not contained within the text. So we can reverse that logic to know when our text contains a value.

Here’s some examples:

ExpressionField ValueResult
“=if(indexOf(@currentField, ”) != -1, ‘Yes’, ‘Nope’)” DoneYes
“=if(indexOf(@currentField, ”) != -1, ‘Yes’, ‘Nope’)” Project DoneYes
“=if(indexOf(@currentField, ”) != -1, ‘Yes’, ‘Nope’)” So Done!!Yes
“=if(indexOf(@currentField, ”) != -1, ‘Yes’, ‘Nope’)” In ProgressNope

Let’s see it in action! Here’s a simple format using the logic above to turn the text red whenever the field contains the word “dead”:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "attributes": {
    "class": "=if(indexOf(@currentField,'dead') != -1, 'ms-fontColor-redDark','')"
  }
}

But what about that last entry? Remember the indexOf function is case-sensitive which may be exactly what you want sometimes. But in this case, that capital D is really messing us up.

Case-Insensitive Contains

Fortunately, we can combine our indexOf function with another function provided by List Formatting, toLowerCase to negate the casing issue.

toLowerCase takes one text parameter and returns that value in all lowercase. So now we can wrap our field value in toLowerCase and always search using a lower case value:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "attributes": {
    "class": "=if(indexOf(toLowerCase(@currentField),'dead') != -1, 'ms-fontColor-redDark','')"
  }
}

PnP Sample: text-contains

Aw Yeah!!!

Now your formats can be even more dynamic and awesome! In my next post, we will take this idea even further! Wowee!

Love List Formatting?

Join the Bi-weekly (every other Thursday) SharePoint Patterns and Practices special interest group for general development call where I will be presenting a new List Formatting Quick Tip on each call!

Also, come get the full picture in my sessions about List Formatting at SPTechCon in Austin in February, the North American Collaboration Summit in Branson in March, the SharePoint Conference in Las Vegas in May, or the European Collaboration Summit in Germany in May:

Formatting Values Using “Starts With” in List Formatting

Applying conditional formats based on the value of your field is pretty straightforward in view and column formatting. But what if you only care if your field’s value starts with a given value?

Most formats rely on knowing all possible values and providing conditions and formats for them. This can quickly get out of hand and in many cases just isn’t feasible.

For instance, what if we wanted to show the flag of a country based on the international calling code portion of a phone number? There are millions of phone numbers in the world and trying to create conditions to match all of them is a terrible idea. But we don’t care about the whole phone number, just the calling code part at the beginning. That’s that +1 for US numbers or +39 for Italian numbers, etc.

List Formatting provides a function called indexOf. This function tells you the first index (starting character position) of some text within your text. The index starts at 0 and if the text isn’t found then the result is -1. Here are some sample inputs and results:

ExpressionResult
“=indexOf(‘Old Lady Wigs’, ‘O’)”0
“=indexOf(‘Old Lady wigs’, ‘d’)”2
“=indexOf(‘Old Lady wigs’, ‘Lady’)” 4
“=indexOf(‘Old Lady wigs’, ‘o’)” -1
“=indexOf(‘Old Lady wigs’, ‘rock’)” -1

As you can see, you can pass in a single character or a whole word/phrase. You can also see that this function is case-sensitive.

So, how do we use this to apply a format based on our text starting with a given value? The key is to look for that 0 index. This means that our value is at the very beginning of the text.

Using this same logic, we can check a given phone number for the presence of the Italian international calling code doing something like this:

ExpressionField ValueResult
“=if(indexOf(@currentField, ‘+39’) == 0, ‘Italy’, ‘?’)” +3933587254Italy
“=if(indexOf(@currentField, ‘+39’) == 0, ‘Italy’, ‘?’)” +13175558697?

Whoo!!

Now we can nest those conditions together to detect multiple calling codes:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "children": [
    {
      "elmType": "img",
      "attributes": {
        "src": "='http://flags.fmcdn.net/data/flags/h20/' + if(indexOf(@currentField,'+358')==0,'fi', if(indexOf(@currentField,'+61')==0,'au', if(indexOf(@currentField,'+46')==0,'se', if(indexOf(@currentField,'+47')==0,'no', if(indexOf(@currentField,'+7')==0,'ru', if(indexOf(@currentField,'+32')==0,'be', if(indexOf(@currentField,'+31')==0,'nl', if(indexOf(@currentField,'+43')==0,'at', if(indexOf(@currentField,'+353')==0,'ie', if(indexOf(@currentField,'+39')==0,'it', 'us')))))))))) + '.png'",
        "title": "=if(indexOf(@currentField,'+358')==0,'Finland', if(indexOf(@currentField,'+61')==0,'Australia', if(indexOf(@currentField,'+46')==0,'Sweden', if(indexOf(@currentField,'+47')==0,'Norway', if(indexOf(@currentField,'+7')==0,'Russia', if(indexOf(@currentField,'+32')==0,'Belgium', if(indexOf(@currentField,'+31')==0,'Netherlands', if(indexOf(@currentField,'+43')==0,'Austria', if(indexOf(@currentField,'+353')==0,'Ireland', if(indexOf(@currentField,'+39')==0,'Italy', 'USA'))))))))))"
      },
      "style": {
        "max-width": "23px",
        "padding": "0 6px 0 0"
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

PnP Sample: text-startswith-callingcodes

This opens up all sorts of possibilities for making some really smart formats. Stick around for my next post where we’ll take this concept even further!

Love List Formatting?

Join the Bi-weekly (every other Thursday) SharePoint Patterns and Practices special interest group for general development call where I will be presenting a new List Formatting Quick Tip on each call!

Also, come get the full picture in my sessions about List Formatting at SPTechCon in Austin in February, the North American Collaboration Summit in Branson in March, the SharePoint Conference in Las Vegas in May, or the European Collaboration Summit in Germany in May: