Any views expressed within media held on this service are those of the contributors, should not be taken as approved or endorsed by the University, and do not necessarily reflect the views of the University in respect of any particular issue.

...in which I don't go up mountains

Face with binary code

Using JSON formatting in SharePoint columns to display fields from a Person data column

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:

Have you tried to use a workflow?

1 Create a new text column in the list.

2 Create a workflow and use Update item in this list,
Action > Add the new text column to the value in people and group column.

Then you can add the text column in calculate formula.

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’:
Opening column formatting in SharePoint
When you click on Advanced Mode, the JSON formatting box appears halfway down in the formatting panel, like this:

Opening JSON formatting for the SharePoint column

JSON formatting: It’s good in Advanced Mode isn’t it

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:

SharePoint's list of content types

SharePoint, bleurgh

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:

Power Automate Flow to list my data fields as an Office 365 User

Flow to see what creepy data fields Office 365 Users has for me

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:

Excel-style expressions

All Excel-style expressions begin with an equal (=) sign. This style of expression is only available in SharePoint Online (not SharePoint 2019).

Simple conditional expressions:

This simple conditional expression evaluates to none if @me is not equal to [$Author.email], and evaluates to ” otherwise:

=if(@me != [$Author.email], 'none', '')

More complex if/else statements can be written like this:

=if([$Sentiment] <= 0.3, 'sp-field-severity--blocked', if([$Sentiment] < 0.9,'sp-field-severity--warning','sp-field-severity--good'))

Non-conditional operators that take one or two operands can be written like these examples:

=[$foo] * -7

=sin(@currentField)

=toString(60 + (sin(6.2831853 * @currentField) * 60))

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:

Ternary operators

Ternary operators expect three operands:

substring: returns the part of the string between the start and end indices. – Only available in SharePoint Online

  • “txtContent”:”=substring(‘DogFood’, 3, 4)” results in F
  • “txtContent”:”=substring(‘DogFood’, 4, 3)” results in F
  • “txtContent”:”=substring(‘DogFood’, 3, 6)” results in Foo
  • “txtContent”:”=substring(‘DogFood’, 6, 3)” results in Foo

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:

Binary operators

Binary operators expect two operands.

indexOf: takes 2 operands. The first is the text you would like to search within, the second is the text you would like to search for.

It returns the index value of the first occurrence of the search term within the string.
Indexes start at 0. If the search term is not found within the text, -1 is returned.

This operator is case-sensitive. – Only available in SharePoint Online

  • “txtContent”: “=indexOf(‘DogFood’, ‘Dog’)” results in 0
  • “txtContent”: “=indexOf(‘DogFood’, ‘F’)” results in 3
  • “txtContent”: “=indexOf(‘DogFood’, ‘Cat’)” results in -1
  • “txtContent”: “=indexOf(‘DogFood’, ‘f’)” results in -1

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

Links

(Image: geralt. Licence: Pixabay)

Share

Previous

Making a Dad Jokes App using REST APIs and Power Apps Custom Connectors

Next

Making an OAuth2 Custom Connector to fetch data from the Stack Exchange REST API in Power Apps

4 Comments

  1. Kai Richardson

    Excellent post. This was very helpful to me, and resulted in a very strange outcome that you may be interested to hear about. I have a person field where the “Display Name” is shown. I used the “Export to CSV” function and the resulting file shows the Display Name. Then I was asked to send a file with an email column as well. So I followed your instructions and added the JSON above on a single text line column to show the email address. I then did the export to CSV again…And now ALL the fields, including the original Person field, export the email address. The “Display Name” is nowhere to be found. Intrigued, I wondered what would happen if I deleted the new JSON column. The result: the Export still only exports the email address for the original column.

  2. Great Share!!

  3. Kimberly

    You are a life saver! Thank you!

  4. Tom

    Thanks for putting this together. I knew about indexOf and substring, etc., but I frequently forget the syntax and so this will serve as a good, handy reference for me to refer back to. Appreciate your diligence and hard work!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress & Theme by Anders Norén

css.php

Report this page

To report inappropriate content on this page, please use the form below. Upon receiving your report, we will be in touch as per the Take Down Policy of the service.

Please note that personal data collected through this form is used and stored for the purposes of processing this report and communication with you.

If you are unable to report a concern about content via this form please contact the Service Owner.

Please enter an email address you wish to be contacted on. Please describe the unacceptable content in sufficient detail to allow us to locate it, and why you consider it to be unacceptable.
By submitting this report, you accept that it is accurate and that fraudulent or nuisance complaints may result in action by the University.

  Cancel