4

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 Dec 17 '16 at 12:53
4

Note:

in June 2017, Microsoft disabled the use of JavaScript in a Calculated Column

That means given answers may not apply for newer SharePoint versions

For long explanation and work arounds see:
June 13th 2017 Microsoft blocked handling HTML markup in SharePoint calculated fields - how to get the same functionality back


Original answer:

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

=" <img src=""/_layouts/images/blank.gif"" onload=""{"
    &"var TR=this.parentNode.parentNode.parentNode,"
    &"    item=ctx.ListData.Row[TR.rowIndex-1];"
    &"console.log(item);"
&"}"">"

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

Then change the Formula to something like:

=" <img src=""/_layouts/images/blank.gif"" onload=""{"
    &"var TR=this.parentNode.parentNode.parentNode,"
    &"    item=ctx.ListData.Row[TR.rowIndex-1],"
    &"    discount=item.Vendor_x003a_Discount,"
    &"    price=item['Price.'];"
    &"console.log(discount,price,item);"
    &"this.parentNode.innerHTML=discount*price;"
&"}"">"

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 be hidden) and on Email Alerts

Details at www.ViewMaster365.com/#How

iCC

3

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

Run the workflow on item create and item update.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.