Forum Discussion
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 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
9 Replies
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.
Would you be able to give additional detail (step by step) of creating a power automate flow in order to repeat (concatenate) the text? I have not used power automate before and am getting confused with the process- especially the title/id parts. Thank you!
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.
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.
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 UserCan you give more detail on creating a flow in Power Automate? I want to automatically copy text into a new column in order to then perform a calculated function- I can't do this originally because of the lookup function.
