Finance request forms dealing with other currencys

Peter Williams · 3 weeks ago

Good morning everyone,

i've been creating finance request forms for my firm's finance department and have a request that i am not sure how to proceed in developing.

Currently, when someone purchases an item in another currency we want to see the currency they used. the price in that currency and then the amount converted to CAD.

Which they will be paid out in CAD

The user would then first put in the amount converted which in this case is 125.25
By default the currnecy used is CAD but if they purchase in another they will choose and then the thrid field will show the USD> 100.01 which they put in themselves

My question, is there a simpler way to do this?

Itallo Brandão · 3 weeks ago

Hi Peter,

The current process feels "backwards" for the end-user because it asks for the result (CAD) before the input (Original Amount).

The "Simpler Way" (Better UX): Flip the logic. Ask the user for the Original Amount and Currency first, then let ServiceNow automatically calculate the estimated CAD amount for them.

Note: Since credit card exchange rates differ from system rates (due to bank fees), you should always leave the final "CAD Amount" field editable so the user can match it exactly to their bank statement.

Here is the setup to automate this:

1. Create/Arrange your Variables

original_currency (Select Box/Lookup Select Box pointing to fx_currency or just static choices like USD, EUR).
original_amount (Decimal or Single Line Text).
reimbursement_amount_cad (Decimal) -> This will be auto-populated.

2. The Backend Logic (Script Include) Create a Client Callable Script Include to fetch the system exchange rate.

Name: CatalogCurrencyHelper
Client Callable: Checked

JavaScript

var CatalogCurrencyHelper = Class.create();
CatalogCurrencyHelper.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    convertCurrency: function() {
        var amount = this.getParameter('sysparm_amount');
        var fromCurr = this.getParameter('sysparm_currency');
        var toCurr = 'CAD'; // Target currency

        if (!amount || !fromCurr) return '';

        // Use the native Currency Converter API
        var converter = new sn_currency.GlideCurrencyConverter(fromCurr, toCurr);
        converter.setAmount(amount);
        var result = converter.convert();
        
        return result.getAmount(); // Returns the converted value
    },

    type: 'CatalogCurrencyHelper'
});

3. The Frontend Logic (Catalog Client Script) Create an onChange script that triggers when the Currency or Original Amount changes.

Type: onChange
Variable: original_amount (Create a similar one for original_currency)

JavaScript

function onChange(control, oldValue, newValue, isLoading) {
    if (isLoading || newValue == '') {
        return;
    }

    var currency = g_form.getValue('original_currency'); // e.g., 'USD'
    if (!currency || currency == 'CAD') return;

    var ga = new GlideAjax('CatalogCurrencyHelper');
    ga.addParam('sysparm_name', 'convertCurrency');
    ga.addParam('sysparm_amount', newValue);
    ga.addParam('sysparm_currency', currency);
    
    ga.getXMLAnswer(function(response) {
        if (response) {
            // Set the estimated CAD amount
            // You might want to round it: parseFloat(response).toFixed(2)
            g_form.setValue('reimbursement_amount_cad', response);
            
            g_form.showFieldMsg('reimbursement_amount_cad', 'Estimated based on today\'s rate. Please adjust to match your bank statement if needed.', 'info');
        }
    });
}

Result: The user types 100 and selects USD. The system instantly populates 135.50 in the CAD box. The user smiles because they didn't have to do the math.

If this response helps you, please mark it as Accepted Solution.
This helps the community grow and assists others in finding valid answers faster.

Best regards,
Brandão.

View solution in original post

Itallo Brandão · 3 weeks ago

@Peter Williams

I'm glad I could help!

Feel free to connect with me on LinkedIn: https://www.linkedin.com/in/itallobrandao/

Best regards!

Peter Williams · 3 weeks ago

Sounds good man.
one question is there a way to use the Reciept date to calculate the exchange rate details.
example.
User purchases a item on Jan. 2 but submitting the purchase on Jan.10th
the rate is different from the 2nd to the 10th

Could i use the date field of my MRV and look up the exchange rate of the 2nd to calculate it

Further enhancing the UX

Peter Williams · 3 weeks ago

Itallo Brandão · 3 weeks ago

Hi Peter,

Yes, absolutely! That is a great enhancement for accuracy.

The sn_currency API supports historical lookups using the method setRateTableDate().

Here is how to update the previous code to include the Receipt Date in the calculation:

1. Update the Script Include We need to capture the date parameter and pass it to the converter.

JavaScript

var CatalogCurrencyHelper = Class.create();
CatalogCurrencyHelper.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    convertCurrency: function() {
        var amount = this.getParameter('sysparm_amount');
        var fromCurr = this.getParameter('sysparm_currency');
        var dateStr = this.getParameter('sysparm_date'); // Capture the date
        var toCurr = 'CAD'; 

        if (!amount || !fromCurr) return '';

        var converter = new sn_currency.GlideCurrencyConverter(fromCurr, toCurr);
        converter.setAmount(amount);

        // LOGIC FOR DATE
        if (dateStr) {
            var gdt = new GlideDateTime(dateStr);
            converter.setRateTableDate(gdt); // Forces the rate from that specific day
        }

        var result = converter.convert();
        return result.getAmount(); 
    },

    type: 'CatalogCurrencyHelper'
});

2. Update the Catalog Client Script Pass the receipt_date field value to the GlideAjax.

JavaScript

function onChange(control, oldValue, newValue, isLoading) {
    if (isLoading || newValue == '') {
        return;
    }

    // Adjust field names to match your MRV variables
    var currency = g_form.getValue('original_currency'); 
    var rDate = g_form.getValue('receipt_date'); // Get the date

    if (!currency || currency == 'CAD') return;

    var ga = new GlideAjax('CatalogCurrencyHelper');
    ga.addParam('sysparm_name', 'convertCurrency');
    ga.addParam('sysparm_amount', newValue);
    ga.addParam('sysparm_currency', currency);
    ga.addParam('sysparm_date', rDate); // Send the date
    
    ga.getXMLAnswer(function(response) {
        if (response) {
            g_form.setValue('reimbursement_amount_cad', response);
            // Updated message
            g_form.showFieldMsg('reimbursement_amount_cad', 'Estimated based on the exchange rate from ' + rDate, 'info');
        }
    });
}

Important Requirement: This logic relies on your instance having the Historical Exchange Rates stored in the fx_rate table for the dates selected.

If your scheduled job only keeps the current daily rate and doesn't store history, the system might default to the latest available rate.

Best regards,
Brandão.

Peter Williams · 3 weeks ago

THank you but it seem that the calculations are off or the date are not getting inputted into the form

and its converting from USD to CAD which is getting from todays date not previous dates