Power User SharePoint Column Tips

Reading Time: 5 minutes

Are you a Power User working with SharePoint in Office 365? Do you plan on using a SharePoint list or library with any of these new tools: Microsoft Flow, Microsoft Forms, Power Apps, Power BI, Modern Team sites, etc.? If so, there are some best practices worth knowing as it relates to columns in SharePoint Information Architecture (IA). These things will be second nature to experienced SharePoint practitioners, but may not be if you’re new to this space. That’s ok – they’re easy to learn and should make your work a little bit easier once you know them.

Any Power User should understand these as part of a well-built SharePoint IA.

---

Tip 1: Create Site Columns

ALWAYS create site columns rather than list columns.

What’s the difference? Both are available from the list/library settings page and can be any kind of column type (although site columns have a few more options available). On the settings page, you will be confronted with making the decision on the kind of column you want to add:

Pick site column. Every time. A site column is so much better than a list column.

Why? It doesn’t cost anything extra to do it and in return buys you re-usability and easy searchability. A sound approach is to configure all site columns ahead of time before going to the specific list or library you want them added to.

Reusability: define once, use many times. If you define a site column, you can use it in as many lists/libraries as you want in the site collection. This makes it very scalable. This also allows you to change the column definition in 1 place and have the change propogate to all lists/libraries where the column is being used. Imagine you’re building a Microsoft Flow that is copying items from one library to another and you want to have the same column on both libraries – you will want to re-use the site column for both libraries rather than creating a new list column for each library. List columns cannot be re-used.

Searchability: if you want to leverage a column in a Search web part (E.g. Content Search Web Part on a Classic page in Classic sites and/or Highlighted Content Web Part on a Modern page in Modern sites), it’s best if you create a site column since it is automatically mapped to a managed property in the search schema. You have to manually map it with a list column. In the image below, a site column was mapped to a managed property, RefinableString02, and then used to filter the list so only items where that property was Confidential would appear in the Highlighted Content modern web part.

You will need to work with a SharePoint Search experienced resource to configure the managed property in the search schema.

---

Tip 2: Know your column types

You should be familiar with all column types to make the best decision for the column type to use for each column you want to add to your lists/libraries. The image below shows all column types available for a new site column:

Here’s two examples that demonstrate why it’s important to understand the difference between SharePoint column types:

Example 1: The choice column type will likely be your go-to selection when wanting to add a column to a list/library that has multiple values simply because it’s the quickest to setup. For a choice column, you enter the values directly in the site column definition screen. However, there are other, usually more appropriate, options available. Please refer to my blog post, Choice, Lookup or Managed Metadata? where I discuss the pros/cons of three different column types all meeting the requirement of selecting from a pre-configured list of values but each having very different capabilities.

Example 2: Even though they appear the same thru the SharePoint UI (shown below), the Yes/No column type will behave differently from the Choice column type with the choices of Yes and No when referring to it in a Microsoft Flow condition.

Testing the Yes/No column type for “Yes” in a Microsoft Flow condition:

@equals(triggerBody()?[‘YesNoColumn’], true)

Testing the Choice column type for “Yes” in a Microsoft Flow condition:

@equals(triggerBody()?[‘YesNoChoice’]?[‘Value’], ‘Yes’)

Very different indeed!

The takeaway from this tip? Know your column options and plan ahead for what type of data you are going to want to store in each one before making the decision on the column type you should use. Then, understand how these column types are referenced in the tool you are using.

Check out my other post, Microsoft Flow condition syntax for SharePoint columns, for examples of other column data types and condition checking.

---

Tip 3: Avoid spaces in column names

ALWAYS create columns without spaces in the name.

Why? Although it doesn’t matter if you are sticking to a straight list or library and NEVER going to use it with any of the new tools, it definitely WILL matter if you end up doing this, or it will certainly make things easier for you if you do.

Column names are encoded in SharePoint (applies whether you’ve created a list or site column).

What does this mean? If you created a column with a space in the name like ‘Contract Status‘, the internal name would be ‘Contract_x0020_Status‘ however if you created a column with the name ‘ContractStatus‘, the internal name would be the same, ‘ContractStatus‘.

The internal name is what is used in most applications integrating with SharePoint. For example, in Microsoft Flow if you want to filter a list/library on a column value and you need to build an expression in the query with the column name, you will need to use the internal name in the filter query.

Refer to the URL on the site column definition page and the internal name will be what’s after the field= token:

Once created, the internal name cannot be changed… period. This is why I recommend initially adding it without spaces. You can, however, change the display name (i.e. what the user sees) after-the-fact. To do this, edit the column definition and add spaces where appropriate (eg. change ‘ContractStatus’ to ‘Contract Status’). The advantage of doing it in this order is the internal name remains unchanged as ‘ContractStatus’ while the display name will show as ‘Contract Status’. The best of both worlds!

---

Parting thoughts

The new Office 365 tools are extremely powerful and can be a lot of fun to work with, however they do require some knowledge and discipline by you, the solution builder, to ensure you’re building something that both works and is maintainable in the long-run. By following the column tips in this post, it will tip the scales in that direction –  I hope you found them helpful.

As I discover new things that may help, I’ll add them to this post.

Thanks for reading.

-JCK

---

Credit: Photo by James Pond on Unsplash