Currency Script Calculation Woes

[cornflakes_01]

I'm currently doing a customization on the request items table that is making me pull out my hair. What I need to do is create a "Total Cost" field on the requested items table such that the field takes the price of the item, and multiplies that to give the total cost for that request item. The reason I need to do this is because we do our reporting at the request item level rather than at the request level. We've got some custom categories defined for request items such that we can report on items such as "We spent X amount of dollars on networking equipment, and X amount of dollars on server equipment".

The problem is, we're using a multi currency model, and all calculations are being done in the native instance currency. I've read over the wiki about doing scripting on price fields, but what i've got so far will not work for items that are in a currency other than canadian. I tried creating a calculated field, but that returns the wrong amount if the person viewing the record is in a different currency than that of the item. Our instance is defaulted to the Canadian Currency, and as such when i run the script below it converts the price into canadian, then takes that price, multiplies it by the quantity, and updates the u_total_price field with the new value. The problem is, i'm not sure how to convert that canadian result back into the currency that the item is defined in.

My test case is, if I have an item worth $1000USD, with 2 quantity, the script runs, and sets the u_total_price field to $2,054.84USD. The real result should be $2000USD.

My current business rule:
Type: onBefore
Conditions: current.quantity.changes() || current.price.changes()

Script:
updatePrice();

function updatePrice(){

var currency = current.price.getCurrencyString();

var abbCurrency = currency.slice(0,3);
var cost = parseFloat(current.price.getReferenceValue()) * current.quantity;
current.u_total_price = abbCurrency + ";" + cost;

}

I can understand why it is putting in the wrong amount, but how do I go about converting it back into the proper currency on the result's way back into the currency field?

Also, if i'm overcomplicating the problem, simpler ideas are definately welcome!

[Arlen]

Here's the wiki article that describes the javascript methods pertaining to pricing:
http://wiki.service-now.com/index.php?title=Scripting_Currency_and_Price_Fields

Pay particular attention to the 'set' method:
http://wiki.service-now.com/index.php?title=Scripting_Currency_and_Price_Fields#Setting_Values

Also, you want to check the price type field:
http://wiki.service-now.com/index.php?title=Calculated_Currency
http://wiki.service-now.com/index.php?title=Multiple_Currency_Model

[cornflakes_01]

Hi Arlen,
Thanks for your reply, I've read the wiki article for scripting pricing and currency fields. The problem is with the calculation, not with how i'm setting the price field. The problem occurs for cases where I am doing calculations on currencies that are other than the base currency on my instance. Because no matter what, i'll be pulling the wrong price (unless the user's session is in the same currency as is set on that item). If I pull the price value using the getReferenceValue method, the price will get converted into the instance's base currency (which will be wrong), OR if I just pull the value as a value, the price will come in with the session currency for whoever the rule is being run by (inconsistent and likely wrong).

What I really need is one of two things to be possible:
1) A method by which I can pull the exact value in the currency that the item is specified in. OR
2) A method by which I can re-convert the price back to the currency specified on the item before I set the value of the item.

My initial GUT feeling is that I need to find out the name of the table responsible for exchange rates, and figure out how that data can be used to re-convert the value back into the item's currency before setting the field value.

[cornflakes_01]

I just figured it out. I used the getCurrencyString to pull the value in the currency desired, then used the javascript slice method to remove that price from the string and calculate the cost based on that value. Revised code is below.

Business Rule Type: Before (insert and update)
Table: sc_req_item
Conditions: current.quantity.changes() || current.price.changes()
Script:
updatePrice();

function updatePrice(){
var currency = current.price.getCurrencyString();
var abbCurrency = currency.slice(0,3);
var price = currency.slice(4);
var cost = parseFloat(price) * current.quantity;
current.u_total_price = abbCurrency + ";" + cost;
}

[Sven Bauer]

I prefer to split the Currency-String rather then using slice, if for some odd reason the currency code hasn't exactly 3 characters.

And at one of my customers we had many issues with wrong javascript calculations (guess what you get if you calculate "79.99 - 39.99"), therefor I round calculation on Currencies/Prices at 2 digits:

function updatePrice(){

      var currency = current.price.getCurrencyString();

      var abbCurrency = currency.split(';')[0]; // I prefer split

      var price = currency.split(';')[1];

      var cost = Math.round(100 * parseFloat(price) * current.quantity)/100;   //Round to 2 digits

      current.u_total_price = abbCurrency + ";" + cost;

}