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:

Applying Column Formats to Multi-line Text Fields

Multi-line text columns don’t provide the standard “Format this column” option under Column settings in the modern list view column menu. They used to, but now they don’t. Fortunately, there is still a way to apply column formatting to these fields!

There are 2 ways in within the interface to apply column formatting for a column (you can also do it programatically). The easiest and most common way is to use the “Format this column” option mentioned above, but it’s not the only way! The advanced settings for a column provide an additional spot where you can paste your formats. Aw yeah!

The Format

I’m using the text-wrap-format sample from PnP created by Aaron Miao. This is a great format for when you really want to see your full text (instead of the cut-off fade provided by default). I’ve modified the sample slightly to apply the primary theme color for the text to make it even more obvious. Here’s the full format:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "white-space": "normal",
    "padding": "11px 0"
  },
  "attributes": {
    "class": "ms-fontColor-themePrimary"
  }
}

Applying the Format

Here’s what our list view looks like before applying column formatting to the multi-line text field, “Synergy”:

To apply column formatting to a multi-line text column:

  • Navigate to the List Settings (Site Actions > List Settings):
  • Choose the multi-line column from the column settings
  • Scroll to the bottom of the multi-line column settings and paste your format in the Column Formatting section:
This same option is also available for site columns!
  • Click OK, then return to your list view and refresh to see the format applied:
The blue is the theme color and was added to the sample just to make it more obvious a format was applied. The key thing to notice is that the full text is now shown.
  • Weep at the beauty of thy column!

NOTE – List Formatting encodes values prior to rendering which makes the use of enhanced (rich text) multi-line fields basically unusable in your formats. These values come back as HTML and that HTML will be encoded and then displayed inline with your values. It is NOT recommended to use Rich text fields in your formats.

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:

New List Formatting Magic String @currentWeb!

There are several special string values that can be used within both view and column formatting. The most common is, of course, @currentField which will return the value of the field you are formatting in column formatting or the title field in view formatting.

These “magic strings” are placeholders for contextual information that are replaced when the format is applied. For instance, @now will be replaced with the exact date/time the format is rendered. This is really helpful to provide dynamic formats.

While poking around today, I found a new one! @currentWeb can now be used to return the absolute url for the site! This is the equivalent of the page context’s webAbsoluteUrl.

Why is this exciting? Previously, you had 2 options when trying to link to something on your site or pulling in an image and they each had major drawbacks:

  • Hardcode the Base URL (https://tenant.sharepoint.com/yourresource)
    • Pro: You’ll always get the image/link you wanted
    • Con: Your format can’t be reused on other sites without manually fixing these links
  • Use a Relative URL (../../yourresource)
    • Pro: Your format is reusable across sites
    • Con: Your URLs are dependent on your relative location. So if someone uses your format within a web part on a different level of your site (folder), your URLs could break

Now, by using @currentWeb, you can have all the good with none of the bad!

For instance, just recently I demoed a quick tip on the PnP call that showed you how to use a relative URL to reference a local image in order to keep the format generic enough to be used with PnP Remote Provisioning. Now my dots can just be replaced with @currentWeb!

Here’s the original relative URL in the contenttype-format view formatting sample:

  "elmType": "img",
  "attributes": {
    "src": "='../../Shared Documents/Fruit/' + if([$ContentType]=='Apple',[$AppleType],[$OrangeType]) + '.png'"
  },

Again, that works fine as long as the format is being applied at the same relative distance from the image files. But now, we can just write:

  "elmType": "img",
  "attributes": {
    "src": "=@currentWeb + '/Shared Documents/Fruit/' + if([$ContentType]=='Apple',[$AppleType],[$OrangeType]) + '.png'"
  },

Now, the resulting URL will always resolve to my images regardless of where in the site my format is being rendered!

Here’s an even simpler example for when I want to link to a document in my documents library:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "a",
  "attributes": {
    "href": "=@currentWeb + '/Shared Documents/MyDoc.pdf"
  }
}

What a great addition! Now List Formatting is even more powerful!

Note: @currentWeb is only supported in SharePoint Online and is not available in SharePoint 2019

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:

Ramp up on SharePoint Framework Extensions at SPC 2019

I will be speaking at the SharePoint Conference 2019 in Las Vegas, May 21st-23rd! I’m honored to have the opportunity to talk about two of my favorite SharePoint subjects: List Formatting AND SharePoint Framework (SPFx) Extensions!

I’m, of course, super excited to be able to talk about List Formatting (see my previous post for details about that session). I am a huge proponent of using view and column formatting to customize your modern list views. There’s so much you can accomplish using simple JSON. But, I’m also fully aware of the limitations. When you hit those limitations, that’s the perfect time to use the SharePoint Framework.

SharePoint Framework Extensions allow you to not only customize your fields (Field Customizers) to take them far beyond what Column Formatting can do, they provide extensibility and customization to modern pages using the same awesome tooling and APIs available for client-side web parts.

Need a custom footer or header? SPFx Extension. Need to provide toast notifications? SPFx Extension. Need to add a custom menu option to the action bar or the context menu? SPFx Extension. Need to add your own analytics to every page? SPFx Extension. Need to create custom field renderings that provide interactivity? SPFx Extension.

I love the SharePoint Framework. Client-side web parts are awesome and they get most of the attention (they also came out first) and I’ve created several. But, my favorite part of SPFx are Extensions. They are often easier to create than web parts (depending on how much interface you actually need), and they provide some really awesome deployment models that can really simplify global customizations across your tenant.

Understanding SharePoint Framework (SPFx) Extensions

SharePoint Framework Extensions are the replacement for Custom Actions, JS Link, and more for Modern Pages in Office 365 and SharePoint 2019. Whether you’ve started experimenting with the SharePoint Framework or not, come find out exactly what the Extensions are, when to use them, limitations, and advantages. SharePoint Framework Extensions are not only powerful and flexible tools to customize SharePoint, when it comes to modern pages, they’re the only way.

That’s the overview of my session on SPFx Extensions, but an even simpler description is just: SPFx Extensions 101.

At first glance it might seem strange that I would be presenting a session in the Business Apps track (List Formatting) and then this one within the SharePoint Development track. However, both sessions are about enabling you to customize modern pages.

Modern sites and pages are awesome and if you’re still using on-premises SharePoint 2016 or earlier or if your organization is still using a large number of classic sites, you may not even know just how awesome they are yet (another reason to attend SPC). While so many features that used to require customizations to either provide (or provide a more usable/functional implementation) are available and no longer require custom implementations, there are still cases where some minor tweaks are required. These can be anything from dynamic visualizations, to corporate logos and universal links on every page, to 3rd party integrations, to disclaimers for specific site classifications, and more.

Knowing when to use List Formatting vs SPFx Extensions and understanding what both are intended for is a key skill for not just developers, but power users, managers, architects, admins, and more. Both sessions can be attended independently, but I highly encourage you to attend both as they will work together to create a much fuller picture of modern site customization options and techniques.

SharePoint Framework extensions are available in both Office 365 and SharePoint 2019. If you are on either environment or suspect that you will be eventually, this will be an essential session to understand what can be accomplished and the tools needed to do so. This session will serve as both a comprehensive overview for admins, architects, and admins as well as provide implementation details (code/tooling/samples) for developers. I’m really looking forward to this session and hope you’ll join me!

If you still have questions or ideas, I’ll also be helping with the Patterns and Practices (PnP) booth and will be happy to talk with you further! Just come find me. We can just talk, or you can bring your laptop and we’ll take a look at your code together!

Register!

The SharePoint Conference 2018 was fantastic. It’s amazing to be in the presence of so many SharePoint (and related tech like PowerApps, Flow, OneDrive, Yammer, Teams, and more) experts and have them be so approachable. If you are a SharePoint professional (user, admin, manager, developer, etc.) then you should definitely attend SharePoint Conference 2019. Go ahead and register now. You can even save $50 by using the discount code KENT!

See and hear from the experts directly!

Whether you attend my sessions or not, I hope you’ll come and say hello. In the meantime, feel free to reach out on this blog, twitter, or attend one of the PnP Calls!

Join me at SPC to learn all about List Formatting in O365 and SP2019!

I love List Formatting (column formatting and view formatting). I’ve contributed many samples, created an editor, and I present List Formatting Quick Tips during the Patterns and Practices (PnP) Special Interest Group call for general SharePoint Development. Now, I get to speak about it at the SharePoint Conference in Las Vegas this May!

List Formatting in O365 and SharePoint 2019

Did you know you can quickly and easily provide dynamic visualizations directly in List views? Both non developers and developers can change how fields and rows look in modern list views by creating simple JSON objects. Learn how to conditionally format fields and rows to take your lists from simple tables to meaningful views. We’ll cover the basics of list formatting, tips and tricks, and the tools and resources available to enable you to get started immediately.

That’s the quick blurb about the session, but what kinds of things will we actually cover?

We’ll start with the basics and show you the many options for applying formats. I’ll cover both the out of the box options (JSON and preformatted options) as well as show you the PnP List Formatter tool I wrote.

We’ll talk about syntax and possibilities primarily by walking through many of the amazing community samples. You’ll be inspired by what you can do and how easily you can do it. They’ll be some code, of course, and we’ll talk about how it works, but if that’s not your thing, I’ll also be showing you lots of ways you can skip the code altogether.

We’ll also talk about some of the limitations and the alternatives you might consider when your needs go beyond what List Formatting can do (hint, attend my other session on SharePoint Framework Extensions). I’ll share several clever tricks for working around these limitations.

My goal is for you to leave the session excited about what you can do without having to be a developer or deploying code (You only need Designer level permissions on your site). I’ll assume nothing about your experience level and they’ll be plenty of material for those of you just getting started. But, I’ll also be providing lots of advice and advanced scenarios so that even those that have been using List Formatting for a while are sure to get a lot as well.

If you still have questions or ideas, I’ll also be helping with the Patterns and Practices booth and will be happy to talk with you further! I seriously love this stuff and would love to talk with you.

Register!

It’s almost January, and if your company is just about to get new budgets for the new year. Make sure you’re on the list! The sooner you register, the cheaper it is. You can even use KENT to save an additional $50! WOWEE!

Employees that attend conferences are generally some of the best. Take advantage of access to so much knowledge. Don’t just attend your co-worker’s talk about what they learned – be the one giving the talk! Show off your own expertise and use this conference to grow your career.

My day job is consulting. My company would be happy to take your money, but it’s even cheaper to ensure your team is up to date on what’s available and how to do it. The more of your team you send the better.

Attend!

I attended the conference last year and it was awesome! If you are a SharePoint professional (user, admin, manager, developer, etc.) then you should attend! There are tons of sessions from both experts in the community and Microsoft themselves.

Many of the biggest announcements are made at the conference and Microsoft engineering is out in full force. It’s the perfect time to find out what’s happening in SharePoint (and related technologies like PowerApps, Flow, OneDrive, Yammer, Teams, and more) and to get answers to your questions. The speakers, Microsoft engineers, and other members of the community are highly accessible. Find them! Ask them things and suggest ideas directly. Seriously, it’s one of the most valuable parts of the conference and you’re missing out if you aren’t doing this!

Even better? Find one of the experts and buy them a drink (or hand them one of the free ones). There are lots of networking opportunities and people are often amazed at how approachable so many members of the community are!

Have Fun!

Attend as many sessions as you can and participate in the after hours events if you’re interested. But you’ll also be in Las Vegas. So go catch a show! The MGM Grand itself is host to David Copperfield. Paolo Pialorsi and I attended it last year and it was awesome!

This is us in the FRONT ROW taking a picture in front of the no pictures sign

Whether you attend my sessions or not, I hope you’ll come and say hello. In the meantime, feel free to reach out on this blog, twitter, or attend one of the PnP Calls!

List Formatting Quick Tip: Format Only Columns

Sometimes you want to create a column but you don’t care about it’s contents. I do this all the time when creating Flow buttons:

LaunchFlowFormat

By providing a button directly in the list view that launches a flow for the given item you make it far easier for users to know about the availability of the flow, make it easy to quickly get their job done, and you can even provide additional context such as a tooltip or specific icon. You can see how to do this (and then just cut/paste/modify the format) using the Launch Flow for the Selected Item column formatting sample.

It’s a great sample and a great use case for column formatting. However, the quick tip I want to share with you is how to easily make a format only column. A format only column is part of your view but doesn’t get in the way while editing or creating items.

The values of Calculated columns can’t be retrieved in list formatting, but these columns can still have column formats associated with them. So the trick is to use an empty calculated column!

  1. In your list view choose Add column then in the list of types choose More…
    AddAColumnMore
  2. Give the column a name and choose Calculated for the type. In the formula box enter =”” then click OK:
    EmptyCalculatedColumn
  3. Now you can apply whatever format you want by using the column menu and choosing Column Settings > Format this column and pasting it in.

Now you have a column that can have a format applied to make your views look awesome, but won’t show up in the information panel!

FormatOnlyInformationPanel

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: