SharePoint List calculated column formula using a Lookup column reference

New Contributor

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 columns:
ReportID // Display name of the default ID column
ReportNumber // Calculated column
Contractor_ID // Lookup column referring to the ContractorID column of the Contractors SPL

 

The calculated column, ReportNumber, would have a formula similar to:
= [ReportID] & "-" & Contractor_ID

 

Question:
Does SharePoint Lists support the use of a Lookup column in the formula of a calculated column?
If so, I need help on the proper syntax for the formula. (The formula above is not working)

 

Thanks in Advance

4 Replies

@jasenpeters you can't reference a lookup column in a calculated column formula. You could use a flow in Power Automate to create the ReportNumber and display that in a single line of text column.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

 

 

@jasenpeters calculated column formula does not support lookup (and multiple lines of text, person or group, multiple selection choice fields, etc.) columns.

 

Workarounds:

1. If you want to store the actual concatenated value in "ReportNumber" column (& use it somewhere else): Create a small Power automate flow on "Reports" list which will run on item creation/modification & updated the "ReportNumber" column with concatenated value using update item action & expressions.

 

2. If you just want to show the concatenated value in list view & do not want to use it anywhere (no need to store the value): You can use JSON column formatting to show the concatenated value in list view using simple JSON.


Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

Thank you for the Advice:

Using JSON on column formatting, I did this:
{
"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:

Formatting syntax reference | Microsoft Docs

---

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.

@jasenpeters Great work, I am glad that you figured it out using JSON formatting.

Is it working as expected for you or do you need any further help with the JSON formatting?


Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.