lookup column in calculated column formula

I have a Lookup column(Discountfirst) which has Number. I also have another Calculated column(Finaldate) with Resulttype DateTime. One more column(DateofReceipt) in Date.

My Calculated column formula looks like this

=(DateofReceipt)+(Discountfirst).

I get an error

“One or more column references are not allowed, because the columns are defined as a data type that is not supported in formulas”.

I googled and came to know I can’t use Lookup Column in Calculated field.

Any suggestion how can do this ?

=================

  

 

Is there any way you can convert your lookup to a Choice field instead?
– MattCoats
Jan 14 ’15 at 15:38

  

 

No not possible.
– user1514428
Jan 14 ’15 at 15:41

  

 

May I ask why it needed to be a lookup in the first place? Is it a site collection lookup field or is it pulling data from another list?
– MattCoats
Jan 14 ’15 at 21:50

  

 

Lookup field is pulling data from another list.
– user1514428
Jan 15 ’15 at 7:46

  

 

When creating the calculated formula, you may notice that lookup fields do not show under the Insert Column heading. Lookup columns cannot be referenced in a calculated column. The suggested workaround here is to use a workflow that copies the lookup value into a text field and to use that copied field in the formula.
– Nikhil Bhavani
5 hours ago

=================

2 Answers
2

=================

The next option with you is to create a Workflow using SharePoint Designer.

Run the workflow on item create and item update.

Change the field FinalDate to a DateTime
Create a new workflow and attach it to the List in question
In the workflow using DateFunction you can add the value into a variable
Use Update Current Item activity and update field FinalDate

The Lookup info you are after is actually available in the Context object once the View has loaded, so you can access it with eithe CSR or….

Stuff some Javascript in a Calculated Column… less lines compared to CSR.. but difficult to debug if you don’t get the syntax right.

You will have to use your own internal fieldname DiscountFirst

Create a calculated column and paste the Formula, then set the datatype to Number so it executes the HTML

=”

Look at the F12 console and check the exact spelling for your required fields

Then change the Formula to something like:

=”

explanation

The onload function is triggered once the blank image is loaded.
The Table Row has the rowIndex.. but the first table row in the View Table is the Header row
The colon in Vendor:Discount is escaped when the Lookup was created
Price is a currency and a string notation with your locale, but Microsoft provides it as value is well.. only with a weird notation with a dot at the end (which can not be used as valid Javascript, so we access it by the string value

at the end the whole IMG tag is overwritten with the calculation

Only drawback of stuffing HTML/Javascript in a Calculated Column is it will show up as text in Forms (can we hidden) and on Email Alerts

Details at www.ViewMaster365.com/#How

iCC