Forum Discussion
SharePoint List calculated column formula using a Lookup column reference
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 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.
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.
