Reading Time: 10 minutes

The Problem: How could I access data fields from a SharePoint Person column to display in another column?

I’ve been developing a laptop booking system in SharePoint and PowerApps.
As part of the Bookings list, I need to display data columns for the borrower’s name, user id and email address. This data is all included within SharePoint’s ‘Person or Group’ data type, however, I can’t access the Person data fields using lookup or calculated columns.

[Go straight to the answer]
 

Possible Approaches:

A lookup column seemed like the obvious approach, but the SharePoint columns containing Person data don’t appear as possible sources when you create a lookup column.

Next, I thought I might be able to access the Person data using a calculated column, but according to Microsoft, the only column data types that can be used in a calculated column formula are single line of text, number, currency, date and time, choice, yes/no, and other calculated columns.

Workflow Method:

Instead, the following method using a workflow was suggested on both StackExchange and in Microsoft Technet forum:

Ideally, I would prefer not to be counting on a workflow to run every time the data was updated as I’ve seen them not always being reliable. It also seems like a lot of extra steps that shouldn’t be necessary when the data is already there. So I carried on looking for a simpler method.

I found this question, ‘Display Template: Retrieving user properties from person field’ on StackExchange’s SharePoint forum, from someone called Tom, aka ToxicPaint:

“I’d like to be able to use the Person field to pull through a user’s email, phone number and any other properties I might want in the future and display them in the template as required.”

This is very similar to what I want to do, except that Tom wants to display the results in a web part on a SharePoint page and I want to list them in a column in the same SharePoint List. But it still gave me hope, in that getting particular fields out of the Person data was a reasonable thing to do, and the JavaScript code fragments posted by another user, Ahmed Mahmoud, included what appeared to be standard Person data field names, including Person.Title, Person.JobTitle, Person.EMail and Person.WorkPhone.

How, then, could I access these?

Displaying Data using Column Formatting

The next thing I found was a very useful thread about SharePoint from GitHub Issues:
Substring of a Text Field #4360.
LtCmdrKeene asked,

“Is there a way to get a substring when formatting a column? I want to format a column to have a hyperlink, but I need only the first 8 characters from a text field.”

This could be very useful for getting my Borrowers’ user ids out from other fields they were stored in as part of a longer string.

I hadn’t realised that column formatting could be used to control which data was displayed as well as the visual styles used to present it. But people on this thread were trying JSON formatting and asking for help because it wasn’t displaying anything, as if that was a possibility.

I discovered that code including formulae similar to those used in Excel and in SharePoint calculated columns could also be used in the Column Formatting panel’s JSON formatting box. I found a Microsoft Office support page, ‘SharePoint in Microsoft 365 Help: Column formatting’, which introduces the visual column formatting settings, and explains,

Column formatting is applied using a text format called JSON… JSON is a text based data definition language that defines JSON objects as text pairs. For example, “name”: John, or “state”: WA are objects.

Next, the Microsoft help page links to Microsoft Docs’ column formatting JSON reference guide, ‘Use column formatting to customize SharePoint’, from which it suggests copying, pasting and editing examples. This page explains,

You can use column formatting to customize how fields in SharePoint lists and libraries are displayed. To do this, you construct a JSON object that describes the elements that are displayed when a field is included in a list view, and the styles to be applied to those elements. The column formatting does not change the data in the list item or file; it only changes how it’s displayed to users who browse the list. Anyone who can create and manage views in a list can use column formatting to configure how view fields are displayed.

How to edit the JSON for column formatting

To edit the JSON, you would open the Format column panel by selecting a SharePoint column heading, selecting Column settings from the menu, and then selecting Format this column. The Format column panel appears, with a link at the bottom that says ‘Advanced Mode’:

When you click on Advanced Mode, the JSON formatting box appears halfway down in the formatting panel, like this:

The reference page explains:

A field with no formatting specified uses the default rendering. To format a column, enter the column formatting JSON in the box.
To preview the formatting, select Preview. To commit your changes, select Save. When you save, anyone who views the list will see the customization that you applied.

This is the default column formatting JSON:

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json"
}


Experimenting with copying and editing the JSON formatting led to a couple of discoveries:

First, I noticed that the way to access the value of the current data field and display it as the text content of the column is to add this block of code:
"children": [
{
"elmType": "span",
"txtContent": "@currentField"
}
]

Helpfully, there is a field called "txtContent", which can be given the value "@currentField".

So this JSON displays the currentField in a text column as if nothing had changed:

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"style": {
"display": "block"
},
"children": [
{
"elmType": "span",
"txtContent": "@currentField"
}
]
}

However, adding the same JSON to a column with the more complicated ‘Person’ data structure does not have the same effect: instead, the same JSON displays [Object Object] in the column.

I assume this is because we need to extract simpler data from the Person objects. We need to access a single field, such as the name or email address, and display it in a column that is specified to have the correct format for that field, eg ‘single line of text’.

Out of interest, I tried this in reverse, using JSON to display the contents of a text column called UUN in the person column, and this did work:

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"style": {
"display": "block"
},
"children": [
{
"elmType": "span",
"txtContent": "[$UUN]"
}
]
}

I assume this worked because the Person column usually displays the person’s name with links to other data. This was interesting, but the opposite of what I needed to do, unfortunately.

From the links to previous answers, I had discovered there should be a data field called 'email', so I tried accessing that and displaying it in the Person column and that worked. Next, I tried using the same JSON to display the email field from the Person column in a new 'single line of text' column, and that worked too:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"style": {
"display": "block"
},
"children": [
{
"elmType": "span",
"txtContent": "[$Borrower.email]"
}
]
}

This was definite progress!

However, the email address doesn’t include the text string I need (the ‘UUN’) in so I still needed to access that part of the data.

Accessing Person data using JSON formatting

In order to get the information I needed out of the Person data, I needed to know what data fields I could expect to be available, and how they would be named. So the next question was:

Where is the ‘Person or Group’ content type specified??

I looked in the SharePoint lists of site content types and list content types, but it isn’t there:

On trying another one of the standard Person fields, I discovered the UUN was being stored in the jobTitle field:

So this JSON worked… for a while…:

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"style": {
"display": "block"
},
"children": [
{
"elmType": "span",
"txtContent": "[$Borrower.jobTitle]"
}
]
}


I added another column so both name and email would be there as well and then the UUN column stopped working. Then the exact same columns and JSON worked when I reordered the display of the columns. Then it stopped working again when I showed my boss in the morning. No offence, Microsoft, but FFS. I don’t know why that ever worked at all, but jobTitle was not the right data field. Adding this post to the ‘Why, God, Why?’ category 😅

Next, I wrote a quick Power Automate Flow to get the Office 365 user data fields and write them to a post in a Teams channel so I could see what was in them:

From my Teams post, I could see that the field I needed for the UUN is called mailNickname in Office365 Users, but unfortunately that field isn’t part of the Person or Group data type. So that didn’t help me get it with JSON but at least now I know that it can be done with Flow.

Going back to online research led me to this very useful article: TutorialsInHand: Working with person field in column formatting json, in which I discovered that the field they call 'sip' is in our case the email that includes the UUN text string. This was another big step in the right direction 🙂

So the next thing I needed to do was to get the UUN substring out from the email address stored in sip..

To start with, I discovered that Excel style expressions could be used.

Using Excel formulas in JSON column formatting

The Microsoft Docs reference page, Microsoft Docs (SharePoint): Formatting syntax reference, has a good introduction to this:

Reading further in the Microsoft documentation, I found this very useful page: Microsoft Docs (SharePoint): Formatting syntax reference, which includes this handy reference list of all the operators available for use in JSON formatting for SharePoint columns: Operators.

First, I hoped to find some kind of operator for extracting a substring from a string of text, and I found exactly that:

The substring() method returns the part of the string between the start and end indexes, or to the end of the string.

So I had figured out how to get the left hand substring:
"txtContent": "=substring([$Borrower.sip], 0,9)"

However, I wouldn’t always need the same number of characters. Instead, I needed the left hand substring from the beginning until it reached the '@' character.

Reading Microsoft’s list of binary operators, I found one that looks very useful: indexOf:

So now I’ve found indexOf, I can use the '@' sign from the email address string to get the string length to extract:

"txtContent": "=substring([$Borrower.sip], 0,indexOf([$Borrower.sip], '@')"

Got it!!

The Solution:

Displaying the Email field from a Person column in a ‘Single line of text’ column:

This was the JSON formatting code to access the email field in the Person data column called ‘Borrower’, and display the email address from the borrower in a ‘Single line of text’ column:

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"style": {
"display": "block"
},
"children": [
{
"elmType": "span",
"txtContent": "[$Borrower.email]"
}
]
}

Displaying a substring from a Person data field in a ‘Single line of text’ column:

This was the JSON formatting code to access the sip field in the Person data column called ‘Borrower’, and display the left hand substring before the '@' symbol in a ‘Single line of text’ column:

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"style": {
"display": "block"
},
"children": [
{
"elmType": "span",
"txtContent": "=substring([$Borrower.sip], 0,indexOf([$Borrower.sip], '@')"
}
]
}

The Result:

These are the column data formats I now have in my Bookings List:

Column	Required?	Description
Title	No	Single line of text
Borrower	Yes	Person or group
Borrower UUN	Automatic from borrower	Single line of text Displays UUN from borrower using JSON formatting: { "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "style": { "display": "block" }, "children": [ { "elmType": "span", "txtContent": "=substring([$Borrower.sip], 0,indexOf([$Borrower.sip], '@')" } ] }
Borrower Email	Automatic from borrower	Single line of text Displays email address from borrower using JSON formatting: { "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "style": { "display": "block" }, "children": [ { "elmType": "span", "txtContent": "[$Borrower.email]" } ] }
Booking ID	Automatic	Unique sequential number, created by SP
Borrow Date	Yes	Date and Time (Date only)
Return Date	Yes	Date and Time (Date only)
Preferred Campus	No	Choice(King’s Buildings, Drummond Street, can add new options)
Laptop IDs	Yes (should be)	Will be multiple choice
Software requirements	No	Multiple lines of text
Unidesk Call Number	No	Single line of text
Notes	No	Multiple lines of text
Created	Automatic	Automatically created by SP, with whatever SP’s standard date/time format is for this column
Created by	Automatic	Automatically created by SP, person format
Modified	No but is automatic	Automatically created by SP, with whatever SP’s standard date/time format is for this column
Modified by	No but is automatic	Automatically created by SP, person format

 

If you’ve made it this far, thanks for reading, and do leave me a comment with your own SharePoint or Microsoft development blog! 🙂

See More Posts on these Topics

• More about SharePoint
• More about Why, God, Why?

Links

• Microsoft Docs: Use column formatting to customize SharePoint
• Microsoft Technet Forums: Workaround to using “Person or Group” field in a “Calculated” column formula?
• StackExchange (SharePoint): “Last Name” calculated field from person field
• StackExchange (SharePoint): Display Template: Retrieving user properties from person field
• GitHub Issues (SharePoint): Substring of a Text Field #4360
• Microsoft Docs: Column formatting – Supported column types
• Microsoft Docs: Formatting multi-value fields
• ‘SharePoint in Microsoft 365 Help: Column formatting’
• Microsoft Power Apps Community: SharePoint columns of ‘person or group’ not showing up in my PowerApp
• TutorialsInHand: Working with person field in column formatting json
• Hands On: How to split a column value using column formatting
• GitHub: SharePoint List Formatting Samples
• Microsoft Docs (SharePoint): Formatting syntax reference
• Microsoft Docs (SharePoint): Formatting syntax reference: Operators

Mar 28, 2022

(Image: geralt. Licence: Pixabay)

Share