SharePoint List calculated column formula using a Lookup column reference

[jasenpeters]

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

[#2]

@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

[#3]

@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.

[#4]

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:

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.

[#5]

@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.

[#6]

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.

[#7]

@ganeshsanap 

List1 have columns like User ID,UserName, Task,E-Mail, Zone

List2 have columns EMail, Zone

Zone for list1 i am getting from List2 setting it as lookup column and Mapping it with E-Mail column.

Now in List1 both E-Mail and Zone showing as lookup. i want to created another column say it UniqueID as calculated column Zone_User ID. Can you please help me here doing this by JSON.

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent":"="[$Zone.lookupvalue]+"-"+[$User ID]"
}

I tried it but its not working.

[#8]

@ramyasahu Try JSON like below in column formatting option for your Zone_UserID column:

{
   "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
   "elmType": "div",
   "txtContent":"=[$Zone.lookupValue]+'-'+[$UserID]"
}

Make sure you are using correct internal name of your zone and user id columns. Follow this article to find the internal name of your SharePoint column: How to find the Internal name of columns in SharePoint Online? 

---

Please consider giving a Like if my post helped you in any way.