Nov 19 2021 12:40 AM
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
Nov 19 2021 04:12 AM
@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
Nov 19 2021 06:31 AM
@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.
Nov 20 2021 12:41 AM
Thank you for the Advice:
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.
Nov 20 2021 01:00 AM
@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.
Sep 28 2022 07:05 AM - edited Sep 28 2022 07:06 AM
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:
Hope this helps others too.
Dec 10 2022 07:18 AM
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.
Dec 11 2022 11:15 PM - edited Apr 17 2023 04:11 AM
@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.