Looking for a way to access the Currency Field Reference Amount

[Scot_Soares]

What I am trying to do:

Leverage the existing Currency field to display an invoice amount in the native currency and have another field that will show the invoice amount in US dollars. For example, an invoice comes in for 100 Euros. This value is entered in a Currency field on a ticket form. Once this value is 'saved', a filed called 'USD Amount' automatically populates with the amount in US dollars.

The existing Currency field appears to already have some of this functionality. If I enter a numeric value in the field, then hover my mouse over it, pop-up text appears that shows the amount in US dollars. I'm looking to have this amount show up in my 'USD Amount' field instead. This pop-up value appears to be stored in this element (fx_currency_instance.reference_amount).

My challenge appears to be getting the value from this amount into a custom ticket form I created (with it's own table).

I presume this could be handled via script in a business rule, but I'm struggling to sort out the code to pull in the value. The SN Wiki doesn't have much detail on the inner workings of the currency tables, so hopefully someone this form has some insights.

Thanks for any suggestions!

Scot

[tony_fugere]

Hope all is well your way!

I'm thinking the .getReferenceValue() method is what you need here, but maybe not. See https://wiki.servicenow.com/index.php?title=Scripting_Currency_and_Price_Fields

I say maybe not, because we did something similar using a GlideRecord lookup to the fx_rate table to calculate the USD value of a foreign currency amount to put in a separate USD field. See if the fx_rate table can help you out and if not, reply back to this thread and we'll continue to peel this onion!

[Scot_Soares]

Hey Tony,

Thanks for the reply, great to hear from you!

I had looked at that wiki page previously but I'm still not sure how best to get the values I need. From what I can see, Service Now creates 'instance' entries each time a currency field is populated with a value. These instance values are stored in the fx_currency_instance table and they hold the USD amount. Looks like the Script Includes AJAXCurrencyValues and CurrencyConverter do the conversions based on the logged in user's locale.

Perhaps we can leverage some of the code in the Script Includes within a client script?

Scot

[tony_fugere]

I think a business rule might be best here that will do the lookup to the fx_rate table to get the USD value based on the price given to us. You can borrow some good ideas from AJAXCurrencyValues such as .getReferenceCurrencyCode() which isn't documented on the Wiki. Here's some code I think might help. I haven't had a chance to test it, but it is adapted from code we use internally at Navigis today.

Condition:

current.u_my_usd_price.nil() || current.price.changes()

// Assumes u_my_usd_price is a currency field
current.u_my_usd_price = getUSDPrice();

function getUSDPrice(){
   var currency = current.price.getReferenceCurrencyCode();
   var answer = current.price;
   if (currency != 'USD'){
     var ce = new GlideRecord('fx_rate');
     ce.addQuery('currency.code', currency);
     ce.query();
     if (ce.next()){
        answer = "USD;" + (current.price.getReferenceValue() / ce.rate).toFixed(2);
     }
   }
   return answer;
}