Generate List Formatting Elements in a Loop Using forEach

A few weeks back I demonstrated how to work with multi-select person or choice fields using indexOf to perform startsWith or contains checks to make some pretty cool formats.

While these are still valid techniques, they have some limitations that the new forEach property and the related operator, loopIndex, can solve. Specifically, applying formatted elements for each value of a multi-select field!

The Problem

I previously created a sample, multi-person-currentuser, that allows you to highlight a multi-person field when one of the users is the current user. The results end up looking something like this:

This sample takes advantage of the contains logic previously discussed by looking for the indexOf the @me (current user’s email address) within a flat string generated using the join operator:

multi-person-currentuser

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=join(@currentField.title, ', ')",
  "attributes": {
    "class": "=if(indexOf(join(@currentField.email,';'), @me) != -1, 'ms-fontColor-themePrimary ms-fontWeight-semibold', '')"
  }
}

Works pretty well and if that’s all you need, go grab that sample!

But what if we could take it further than just displaying the fields as a string? What if we could apply elements for each item? Well… good news, that’s exactly what the new forEach property allows us to do!

The forEach Property

The forEach property is not yet part of the schema (so don’t be surprised if it gets highlighted as invalid in something like VS Code). You can use it within column formatting or inside of your rowFormatter for view formatting.

The forEach property allows you to create virtual fields that you can access within an element. The element where you add the property (along with all of it children) are rendered once for each item within the array (array refers to the collection of selected people or choices).

Because the element is rendered multiple times, you must have a containing element. This is why if you attempt to use the forEach property in the root element, you’ll get an error.

The forEach property’s value is a simple sentence in the form virtualFieldName in ArrayField. Let’s look at an example.

For this first example we’ll use a simple multi-select choice field. In this case we’ve just made the choices some letters. Here it is with no formatting applied:

Now let’s apply a format using the forEach property:

multi-choice-foreach:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "children": [
    {
      "forEach": "choiceIterator in @currentField",
      "elmType": "div",
      "txtContent": "[$choiceIterator]",
      "attributes": {
        "class": "ms-bgColor-themePrimary ms-fontColor-white",
        "title": "='I am the letter ' + [$choiceIterator]"
      },
      "style": {
        "width": "16px",
        "height": "16px",
        "text-align": "center",
        "margin": "1px"
      }
    }
  ]
}

Here’s what that looks like:

So what did we do? As mentioned above, you can’t apply forEach to the root element. So we created a div and then gave it a single child. However, by using forEach within the child we’re using this element as a template that will be repeated within the root container once per selected choice.

The forEach value requires you to provide the virtual field name followed by the word in and concluding with the name of the array to loop over. In this case, as seen on line 6, we are using choiceIterator as the virtual field name and our array is the @currentField (this could have just as easily been another array field in your view using the [$FieldName] syntax).

Note that the virtual field name should be unique. It is possible to clobber your other fields if you use the same name as one of the internal names of your fields! This means that if you use Title then you’ll no longer have access to the actual Title field’s value! This will be true even after the loop completes. So choose carefully. I find it best practice to use either the field name or type followed by the word Iterator. This has the added benefit of making it obvious that you are retrieving a loop value within your element – but that’s up to you.

Now that we’ve added the forEach property, we can access the virtual field anywhere within our template object (and its children) just like it was any other field! You can see this in the txtContent property on line 8 and we even use it in an expression within the title property to create a nice tooltip on line 11.

Taking it further with loopIndex

Back to our person example from above, wouldn’t it be great to do more than simply show their names? There’s another sample called person-roundimage-format that applies the standard circle image for users. Using the techniques above we can quickly convert it to support multi-select person fields (add a forEach and change our field accessors):

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "children": [
    {
      "forEach": "personIterator in @currentField",
      "elmType": "div",
      "style": {
        "width": "32px",
        "height": "32px",
        "overflow": "hidden",
        "border-radius": "50%",
        "margin":"2px"
      },
      "children": [
        {
          "elmType": "img",
          "attributes": {
            "src": "='/_layouts/15/userphoto.aspx?size=S&accountname=' + [$personIterator.email]",
            "title": "[$personIterator.title]"
          },
          "style": {
            "position": "relative",
            "top": "50%",
            "left": "50%",
            "width": "100%",
            "height": "auto",
            "margin-left": "-50%",
            "margin-top": "-50%"
          }
        }
      ]
    }
  ]
}

Here’s what that looks like:

Now the images are showing up and we even get multiple when more than one person is selected! But what happens when we have lots of selections? The results aren’t great and give us all a sad:

You can see that up to 3 people looks just fine, but 4+ starts to have some weird squishing (and nobody likes weird squishing). So we need some way of knowing how many people we have for an item and which one we are on in the template.

Fortunately, you can use the length and loopIndex operators to accomplish this!

The length operator will provide the total number of items in an array (it does NOT provide string length). We can use this value to determine when we shouldn’t show an element (to remove face 4, 5, 6, etc.).

The loopIndex operator provides us with the zero-based index of where we are in the forEach loop. To use it, simply provide it the virtual property name you want to get the index of (since you can nest multiple forEach loops) as a string. So, in our case we can use "=loopIndex('choiceIterator')".

We’re going to base our solution on the UI Fabric Facepile with descriptive overflow. In order to do that, we want to accomplish the following:

  • Show 1 to 3 faces without change (that seems to work great)
  • Never show more than 3 circles
  • Replace the third circle with a descriptive overflow circle when there are 4 or more people selected

The first case we’ve got handled. The second can be done by using "display":"none" as mentioned in my last post. The third requires an alternate element that only shows when there are 4 or more people and we are on the 3rd person.

multi-person-facepile:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "children": [
    {
      "forEach": "personIterator in @currentField",
      "elmType": "div",
      "style": {
        "width": "32px",
        "height": "32px",
        "overflow": "hidden",
        "border-radius": "50%",
        "margin": "2px",
        "display": "=if(loopIndex('personIterator') >= 3, 'none', '')"
      },
      "children": [
        {
          "elmType": "img",
          "attributes": {
            "src": "='/_layouts/15/userphoto.aspx?size=S&accountname=' + [$personIterator.email]",
            "title": "[$personIterator.title]"
          },
          "style": {
            "position": "relative",
            "top": "50%",
            "left": "50%",
            "width": "100%",
            "height": "auto",
            "margin-left": "-50%",
            "margin-top": "-50%",
            "display": "=if(length(@currentField) > 3 && loopIndex('personIterator') >= 2, 'none', '')"
          }
        },
        {
          "elmType": "div",
          "attributes": {
            "title": "=join(@currentField.title, ', ')",
            "class": "ms-bgColor-neutralLight ms-fontColor-neutralSecondary"
          },
          "style": {
            "width": "100%",
            "height": "100%",
            "text-align": "center",
            "line-height": "30px",
            "font-size": "14px",
            "display": "=if(length(@currentField) > 3 && loopIndex('personIterator') == 2, '', 'none')"
          },
          "children": [
            {
              "elmType": "span",
              "txtContent": "='+' + toString(length(@currentField) - (2))"
            }
          ]
        }
      ]
    }
  ]
}

Here’s what that looks like:

Here’s what we did:

  • On line 14, we added a display property to our template element to set the value to none if the loopIndex is greater than or equal to 3 (keep in mind that the index starts at zero so we’re basically saying never show items 4 and up)
  • On line 31, we added a similar display property to our img element to set the value to none if the number of items is greater than 3 and the loopIndex >= 2. This allows us to show it as normal if there are 3 or less people selected but when there are 4 or more, we don’t want to show that 3rd person.
  • On lines 34-54 we add our descriptive overflow element. This is a gray circle that says how many more people were selected than are shown.
  • On line 46, we once again take advantage of the display property to ensure that the overflow element is only shown when there are more than 3 people selected and we are on the 3rd element (loopIndex = 2).
  • We use the join operator to create a tooltip with everybody’s name in it on line 37.
  • We determine the number of additional people by simply subtracting 2 (since we know how many we are showing) from the length of the array. Notice that the 2 is wrapped in parenthesis. This is to combat an issue with the subtraction operator.

While that certainly isn’t the simplest sample in the world, it demonstrates common list formatting patterns such as conditional display, element loops, and customization based on loop position.

This opens up even more possibilities in the already awesome List Formatting world! Whoo!!

Conditionally Launch Flows using List Formatting

Creating a button with Column Formatting to launch a flow is a fantastic way to make actions obvious (not hidden in the Flow menu). If you have a list item flow, I strongly recommend doing this (just copy, paste, and tweak the sample). But what if you want to go further? What if you have multiple flows for list items and you want to help guide your users through this more complex (but relatively common) scenario?

Good news! List formatting expressions make it possible to only show the flow launch button(s) that make sense based on the values of the item! Here’s what we’re building:

In the list above, we have 3 separate flows (Develop, Deploy, and Destroy). We want to only prompt the user to launch one of these based on the value of the Status column in the list item. You could argue that it might make more sense to launch a single flow that handles the conditional logic directly. Sure, but customizing the text, icon, and color to make it obvious to the user what action they are taking is still an awesome thing to do.

Conditional Logic Across Properties

In our sample, we want to conditionally change the color, icon, text, visibility, as well as the flow launched. This brings us to a very common scenario in List Formatting: applying the same logic to multiple properties.

It would be awesome to apply your logic to entire elements, but in List Formatting it is only possible to conditionally apply the value of a property. This means you can’t conditionally include/exclude a property or element. These properties/elements have to be included with their individual values conditionally set.

So, if you want to set a style property based on a condition, like the text color, you have to include the property and set its value regardless. Generally, you handle this by setting up an expression like this: “color”:”=if(@currentField>2,’red’,”)”

You cannot, however, apply that same logic to the inclusion of the color property itself.

Conditional Logic for Elements

Although you must include all the elements and properties whose values you want to set (even if only in some conditions), you can use the style display attribute to remove entire elements by simply setting the value to none. This is how we remove the entire button when the status is ‘Destroyed’ in our sample (line 15 below). We don’t want to prompt the user to launch any flow, so we simply remove the button altogether.

You can extend this pattern further by creating a placeholder top element of a div with children. The individual child elements can be turned on or off by conditionally setting the display property. That’s not what we’re doing here, but it’s something to keep in mind.

Conditional Flows

generic-start-flow-conditionally:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "button",
  "customRowAction": {
    "action": "executeFlow",
    "actionParams": "='{\"id\": \"' + if([$Stage]=='','b60a26d3-fd87-4947-9d1d-344cb31d953a',if([$Stage]=='Development','3a27a39c-0ec9-4342-8fe3-bfb37fefc3da','3091d383-f8ed-48da-9962-bd7c24e70688')) + '\"}'"
  },
  "attributes": {
    "class": "='ms-fontColor-' +if([$Stage]=='','orangeLight',if([$Stage]=='Development','teal','redDark'))"
  },
  "style": {
    "border": "none",
    "background-color": "transparent",
    "cursor": "pointer",
    "display": "=if([$Stage]=='Destroyed','none','inherit')"
  },
  "children": [
    {
      "elmType": "span",
      "attributes": {
        "iconName": "=if([$Stage]=='','Lightbulb',if([$Stage]=='Development','Deploy','HeartBroken'))"
      },
      "style": {
        "padding-right": "6px"
      }
    },
    {
      "elmType": "span",
      "txtContent": "=if([$Stage]=='','Develop!',if([$Stage]=='Development','Deploy!','Destroy!'))"
    }
  ]
}

You can see that we are applying each of our elements conditionally by comparing the value of the Stage column (also present in the view). For the flow, we build the actionParams conditionally by building the escaped JSON value and swapping the ID value in and out based on the stage (line 6).

You can customize this sample by adding additional conditions, changing the comparison column (use the internal name), and the ID(s) of the flows themselves.

Getting a Flow’s ID

To use the code, you must substitute the ID of the Flow(s) you want to run. The IDs are contained within the expression inside the customRowAction attribute inside the button element.

To obtain a Flow’s ID:

  1. Click Flow > See your flows in the SharePoint list where the Flow is configured
  2. Click on the Flow you want to run
  3. Copy the ID from the end of the URL (between flows/ and /details)

Update

See this demoed on the PnP Call (Live from MVP Summit):

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 the SharePoint Conference in Las Vegas in May, or the European Collaboration Summit in Germany in May:

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 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 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 the SharePoint Conference in Las Vegas in May, or the European Collaboration Summit in Germany in May: