Combining data from multiple columns into a single visual display is a frequent requirement for SharePoint developers. Whether you are building a custom dashboard or simply trying to make a list more readable, knowing how to concatenate values effectively is a vital skill. While many developers instinctively reach for Calculated Columns, SharePoint JSON column formatting offers a more flexible, real-time alternative that doesn't add to your data storage overhead.
In this guide, we will explore the different methods for joining values from multiple columns using JSON, compare the syntax styles, and look at best practices for handling edge cases like null values.
Why Use JSON Formatting Instead of Calculated Columns?
Before diving into the code, it is important to understand why you might choose JSON formatting over a traditional calculated column.
- Real-time Updates: JSON formatting is applied on the client side. If a user updates a field, the formatted column updates instantly without a page refresh or server-side recalculation.
- No Data Storage: Calculated columns create new metadata fields in your database. JSON formatting is purely a visual layer (a "view-only" transformation), keeping your list schema clean.
- Rich UI Capabilities: Unlike calculated columns, which primarily return text, numbers, or dates, JSON formatting allows you to inject HTML elements, CSS styles, and even icons alongside your concatenated text.
Method 1: The Inline Expression (The Modern Standard)
The most common and readable way to join columns is by using an inline expression. This syntax resembles standard JavaScript or Excel logic and is generally preferred for its brevity.
Suppose you have two columns, Text1 and Text2, and you want to display them in a third column separated by the word "and."
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "div",
"txtContent": "=[$Text1] + ' and ' + [$Text2]"
}
How it Works
[$InternalName]: This is the syntax used to reference the value of another column. Always use the Internal Name of the column, not the Display Name.+Operator: When used with strings, the plus sign acts as a concatenator.- Single Quotes: Literal text strings (like ' and ') must be wrapped in single quotes within the double-quoted JSON value.
Method 2: The Abstract Tree Syntax (Operator/Operands)
Before inline expressions were fully supported in all SharePoint environments, developers used a more verbose structure known as the Abstract Tree syntax. While it is wordier, it is highly structured and can be easier for programmatic generators to create.
Here is how you would achieve the same concatenation using the operator and operands approach:
{
"elmType": "div",
"txtContent": {
"operator": "+",
"operands": [
"[$Duration]",
" ",
"[$DurationUnits]"
]
}
}
When to Use This Method
While the inline expression is easier to write, the tree syntax is sometimes preferred when dealing with extremely complex nested logic where parentheses in an inline string might become confusing. However, for 99% of use cases, the inline expression is the industry standard.
Handling Null Values and Empty Columns
A common pitfall in concatenation is how SharePoint handles empty fields. If [$Text2] is empty, your result might look like "Value1 and ". To create a more professional display, you can use a ternary operator (an if statement) to check if a value exists before adding the separator.
Example: Conditional Concatenation
{
"elmType": "div",
"txtContent": "=[$Text1] + if([$Text2], ' and ' + [$Text2], '')"
}
In this example, the code checks if [$Text2] has a value. If it does, it appends " and " followed by the value. If it is empty, it appends an empty string, preventing trailing separators.
Best Practices for SharePoint JSON Formatting
To ensure your formatting works across all views and for all users, keep these best practices in mind:
1. Always Use Internal Names
If you rename a column from "Project Name" to "Title," its internal name remains "Project_x0020_Name." If your JSON uses the display name, the formatting will break. You can find the internal name by checking the column's URL in the List Settings.
2. Check for Field Presence in the View
For JSON formatting to access a column's value (e.g., [$Text1]), that column must be present in the current view. If you remove Text1 from the view, the formatted column will show as empty or throw an error.
3. Use Excel-style Expressions for Complex Logic
Remember that SharePoint JSON expressions are a subset of Excel-like logic. You can use functions like toUpperCase(), toLowerCase(), and toString() to further manipulate your concatenated strings.
Frequently Asked Questions
Can I concatenate more than two columns?
Yes! You can chain as many as you need: "=[$Col1] + ' ' + [$Col2] + ' ' + [$Col3]". Just be mindful of the horizontal space available in your list view.
Does this work in SharePoint On-Premises?
JSON column formatting is fully supported in SharePoint Subscription Edition and SharePoint 2019. However, older versions like 2016 or 2013 do not support this feature and require CSR (Client-Side Rendering) or custom SPFx extensions.
Why is my JSON showing as plain text instead of formatting the column?
This usually happens if there is a syntax error. Ensure your JSON is valid by using a tool like VS Code. Also, ensure you have pasted the code into the "Format column" section under Column Settings, not the "Format view" section.
Wrapping Up
Concatenating values using JSON formatting is a powerful way to enhance the user experience in SharePoint Online. By moving away from calculated columns and utilizing inline expressions, you create a faster, more dynamic interface for your users. Whether you choose the simple inline syntax or the structured tree syntax, mastering these techniques allows you to transform raw data into meaningful information.
Next time you need to join a 'First Name' and 'Last Name' or combine a 'Status' with a 'Date,' try using the JSON methods outlined above to keep your SharePoint site modern and efficient.
