Forum Discussion

jasenpeters's avatar
jasenpeters
Copper Contributor
Nov 19, 2021

SharePoint List calculated column formula using a Lookup column reference

Given: A SharePoint List (SPL) called Contractors with the following columns: ContractorID // Display name of the default ID column ContractorName   Another SPL called Reports with the following...
admin
App
developer
Lists
SharePoint Online
Usage
jasenpeters's avatar
jasenpeters
Copper Contributor
Nov 20, 2021

Thank you for the Advice:

Using JSON on column formatting, I did this:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "= getYear([$ReportDate]) + '-' + [$Contractor_ID.lookupId] + '-' + [$ReportID]"
}

 

Please Note:

The equals sign =, needs to follow immediately after the first double quote " in this way (no space):

"txtContent": "= . . . "

 

Otherwise it renders gibberish when used like this (with a space):

"txtContent": " = . . . "

 

Therefore, take note of syntax, be exact, and experiment with a problematic formula, for it may be a simple oversight of a space or "hidden" syntax error.

Keep a file of examples and references to JSON column formatting formulas, it will save you a lot of time.

 

I would recommend this reference for JSON Column Formatting, once you get the feel for the syntax:

https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/formatting-syntax-reference

---

Since this method only displays the column and not store the information, I would probably

have to have a PowerApp (or Flow) to take user input and store the year and ID's in the needed format.

By the way, is: YYYY-ContractorID-ReportID, Example: 2021-101-6923

---

Thanks again for the Advice and the quick response.

OzOscroft's avatar
OzOscroft
Iron Contributor
Sep 28, 2022

Awesome work thanks jasenpeters !  This has really helped me present some information which didn't need to be stored.  Here's what I've found along the way:

 

  • If referencing lookup columns in the JSON, those other columns need to be visible in the view.
  • Getting the syntax correct for lookup column names can be tricky, especially if they include a space.  An example might be 'Company: Address Details'.  To find the reference to use:
    • Go to the List settings
    • Click on the column you wish to reference in the JSON (making sure the column is showing in the view)
    • Go to the end of the URL and copy the bit after '&Field='.
    • In the example above, this is 'Company_x003a_Address_x0020_Det'
  • You can include line breaks in the JSON code using:
    + '\n' +

 

Hope this helps others too.

Resources