Calculate Field with a Lookup Additional Column

Question

Would like to ask some suggestion if i have 2 lists (Vendor List & Order List). Where Vendor List is contain with name & discount fields

And i create new list "Order List" with field Items, Price, Vendor (lookup from vendor list)

When i create a lookup field to the "Vendor List", i add additional column (Discount) too.

Then after i add new list, then the record will shown as below. What i would like to ask is, how to do calculation between price in the each item, with the discount additional lookup field? I look some reference about workflow in SharePoint Designer, as a newbie i still cannot have it :(.

Looking some input for all you guys. Big thanks...

Answer 1

On 2013 use CSR (Client Side Rendering) and a JSLink to override the display of the After Discount field. All the fields from the View are then available in the Ctx object

Wes Preston has written good intros into CSR: http://www.idubbs.com/blog/category/sharepoint/spc14/

Alternative is to use JavaScript client-side, once displayed the same fields are available in the global Javascript variable ctx.ListData.Row Takes some more programming.

[UPDATE]

I stand corrected.. it actually takes less programming compared to CSR/JSlink

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],"
    &"    discount=item.Vendor_x003a_Discount,"
    &"    price=item['Price.'];"
    &"console.log(discount,price,item);"
    &"this.parentNode.innerHTML=discount*price;"
&"}"">"

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 whol 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

Answer 2

I think the formula you're looking for is:

=Price-(Price*Vendor:Discount)

Where the After Discount column is a Calculated column with the formula above.

Hope this helps