Currency conversion to USD

[Sandy32]

I had requirement on currency conversion, we will enter amount in variable(single line text) and will select currency(reference--fx_currency) so, user will add amount like 1000,200.50 etc and will select currency then amount will convert with USD and populate in new variable.. Below is the screenshot for ref. Please help me on this requirement. 

[Ankur Bawiskar]

Hi,

you need to write onchange client script on the Currency field and then using GlideAjax+ Script Include to query "fx_rate" table for the currency conversion

• Conversion rates are stored in the fx_rate table.
• Each record contains the conversion rate from a given currency to the Euro. The rates are updated daily from the ECB website by a scheduled job called ECB Exchange Rate Load.
• A currency conversion from one currency to another involves two rates.
• Actual: In the fx_rate table, each record contains the conversion rate from a given currency to the Euro.
• Expected: In the fx_rate table, each record contains the conversion rate from the Euro to the given currency.

So you would require to convert user selected to Euro and again Euro to USD

During you pick the record you need to pick the latest record as the table is updated with daily rates.

https://docs.servicenow.com/bundle/orlando-platform-administration/page/administer/currency/concept/...

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[Sandy32]

Hi Ankur,

Can you please provide script for reference, if possible.

[Ankur Bawiskar · Accepted solution]

Sample script below

You can then use GlideAjax and onChange Client Script

Input: currency sys_id, amount

Output: amount in USD

Script:

getConversionValue: function(){

		var currencySysId = this.getParameter('sysparm_currencySysId');
		var amount = this.getParameter('sysparm_amount');

		var selectedToEuroGr = new GlideRecord("fx_rate");
		selectedToEuroGr.orderByDesc('sys_created_on');
		selectedToEuroGr.addQuery("currency", currencySysId);
		selectedToEuroGr.query();
		if (selectedToEuroGr.next()) {
			var selectedCurrencyToEuro =  parseFloat(amount)/parseFloat(selectedToEuroGr.rate);

			selectedCurrencyToEuro = selectedCurrencyToEuro.toFixed(3);

			var euroToUsdGr= new GlideRecord("fx_rate");
			euroToUsdGr.orderByDesc('sys_created_on');
			euroToUsdGr.addQuery("currency.code", "USD");
			euroToUsdGr.query();
			if(euroToUsdGr.next()){
				return (parseFloat(euroToUsdGr.rate)*selectedCurrencyToEuro).toFixed(3);
			}
			return ''; // no conversion found

		}
		return ''; // no conversion found

	},

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[MrMuhammad]

Hi,

fx_rate table contains all the conversion rates but OOB it does have static data and its not updating. Your steps should be as below

• First you need to find out the API that provides you the updated conversion rates.
• Setup Scheduled job that will get updated rates from API and import in fx_rate table.
• Write a client script and make GlideAjax call to query fx_rate table and you need to pass Amount entered by user plus currency and return multiplicative value of amount and rate to the client script.

Note: Currency conversion API is very important to get the accurate amount conversion as currencies rate fluctuate very often. Please let me know if you face any issues or need further elaboration. 

Please mark this ACCEPTED & HELPFUL if it answered your question.

Thanks & Regards,
Sharjeel