Allow lookup columns in calculated columns
I need to use a lookup column in a formula of a calculated field.
Stephane Bouillon
shared this idea
4 comments
-
YatinP
commented
Please please please add this feature. It will stop requiring a Designer Workflow for such small functionality.
-
Stephen C Thompson
commented
The same idea has been presented in this suggestion, "Allow lookup columns brought up from different list into calculated columns", https://sharepoint.uservoice.com/forums/329214-sites-and-collaboration/suggestions/13981188-allow-lookup-columns-brought-up-from-different-lis
Since there is no way for us to merge duplicate suggestions I recommend voting for the instance that currently has more votes.
-
Ryan Caldwell
commented
Very Disapointed - I found a work around for a problem I discovered where a choice column is not able to be used by lookup column selection - this was by creating a formula column to put text at the front of the choice data in the main list - this let the lookup column be able to select it & I could bring the data from this list into child list. I then wanted to use RIGHT(LEN-MIN(FIND({},&""))-)) formula to display the numbers only - like you can in excel. But Sharepoint can't use a lookup column in calculated columns - so it can't be automated...
2 very silly restrictions - 1)can't use lookup to display data in another list from a choice column. 2)a work around with formula is possible - but - you can't use a lookup column in calculated columns so as to be able to extract the needed data out of the lookup column..........Really really disappointing.
-
M.Qassas
commented
Unfortunately, The lookup column is not supported in
- SharePoint Calculated Column Formula.
- SharePoint Column Validation.For more details, Please, check
- The supported and unsupported columns in SharePoint calculated column formula at https://blog.devoworx.net/2017/09/11/supported-fields-in-calculated-column-sharepoint/
-------------------------------------------------
The available workarounds are
(1) USING CHOICE FIELD INSTEAD OF LOOKUP FIELD
- If you can change the lookup field to choice field with static options, so it’s recommended to use the choice field instead of lookup field, the choice field is supported in the Calculated Column. Check The Supported and Unsupported Columns in SharePoint Calculated Column Formula.
(2) USING SHAREPOINT DESIGNER WORKFLOW
- Create a new lookup field with a supported data type like a Single line of text.
In the workflow, Set the new lookup field value with the original Lookup column value on item created or changed.
- Now you can use the new lookup field in the SharePoint calculated column Formula.For the details steps, please check "Using lookup field in SharePoint calculated column formula" at https://blog.devoworx.net/2017/09/11/lookup-field-in-calculated-column-sharepoint/
(3) USING CODE.
- In case you have a development background, you can also use the Event Receiver to build your formula via code.
