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: