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

Hi Peter,

If it is defaulting to "Today's" rate, it usually means one of two things:

Date Format Issue: The server script (GlideDateTime) isn't recognizing the format of the date string sent from the client, so it defaults to "Now".
Missing Rate: There is no entry in the fx_rate table for that specific date, so it falls back to the latest one.

Let's add some Logging to your Script Include to see exactly what is happening.

1. Update Script Include (with Debugging) Replace your function with this version. It will write to the System Logs (syslog).

JavaScript

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

        // DEBUG LOGS (Check System Logs > All to see these)
        gs.info('CURRENCY DEBUG: Amount=' + amount + ', Currency=' + fromCurr + ', DateString=' + dateStr);

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

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

        if (dateStr) {
            // Attempt to parse the user's date format
            var gdt = new GlideDateTime();
            gdt.setDisplayValue(dateStr); // Try setDisplayValue first (handles user format)
            
            // If the date looks invalid (today), try the standard constructor
            if (!gdt.isValid()) {
                 gdt = new GlideDateTime(dateStr);
            }
            
            gs.info('CURRENCY DEBUG: Interpreted Date Object=' + gdt.getValue());
            converter.setRateTableDate(gdt);
        }

        var result = converter.convert();
        gs.info('CURRENCY DEBUG: Result=' + result.getAmount());
        
        return result.getAmount();
    },

2. Troubleshooting Steps

Run the test again on the form.
Go to System Logs > System Log > All.
Search for "CURRENCY DEBUG".

What to look for:

If DateString is empty: The Client Script isn't grabbing the field value correctly. (Are you using an MRV? If so, ensure the Client Script is defined inside the MRV, not on the Catalog Item).
If Interpreted Date Object is Today: setDisplayValue failed to parse the format.
If the Date is correct but the Rate is wrong: Check your fx_rate table. Filter for Currency = USD and verify if there is a record for Jan 02.

Let me know what the logs say!

Best regards, Brandão.

Peter Williams · 3 weeks ago

it seem that the date are coming through

Peter Williams · 3 weeks ago

i also added in the logs to see what rate i was using and its shows as this

2026-01-20 01:27:29 PM
Information CURRENCY DEBUG: Amount=100, From=USD, Date=2026-01-01 *** Script

2026-01-20 01:27:29 PM
Warning Invalid query detected, please check logs for details [Unknown field from_currency in table fx_rate] QueryEventLogger

2026-01-20 01:27:29 PM
Information CURRENCY DEBUG: Converted Amount=138.2589 *** Script

2026-01-20 01:27:29 PM
Information CURRENCY DEBUG: FX Lookup Date=2026-01-01 12:00:00 *** Script

2026-01-20 01:27:29 PM
Information FX RATE USED: Rate=132.3358, Valid From=undefined, FX Sys ID=a1768bc30a0a0b540095275597632cfa

when i look up the sys_id it shows this

Itallo Brandão · 3 weeks ago

Hi Peter,

That screenshot is the "Smoking Gun"! The logs confirm that the Script is working perfectly, but your Data is missing.

The Analysis:

Code is Good: Look at the line Interpreted Date Object=2025-10-28. The system is successfully receiving and understanding the date you selected.
The Issue: Look at the Result for the different dates.
- Date 2025-10-28 -> Result: 138.2589
- Date 2026-01-20 -> Result: 138.2589
- Date 2026-01-02 -> Result: 138.2588... (Effectively the same).

Conclusion: The conversion result isn't changing because your instance does not have historical Exchange Rates loaded for those specific dates in the past. When ServiceNow cannot find a rate for "Jan 2nd, 2026" in the fx_rate table, it falls back to the most recent available rate (likely today's rate).

Next Step: You can mark the script as "Done". To fix the calculation values, you need to check your data:

Navigate to the fx_rate table.
Filter for Currency = USD.
You will likely see only one or two recent records.
For this to work historically, you would need to import historical rate data (or just wait, as the daily Scheduled Job builds up the history from now on).

Best regards,
Brandão.

Peter Williams · 3 weeks ago

i made a modification to the script include to this

var CatalogCurrencyHelper = Class.create();

CatalogCurrencyHelper.prototype = Object.extendsObject(AbstractAjaxProcessor, {

convertCurrency: function () {

var amount = parseFloat(this.getParameter('sysparm_amount'));

var fromCurr = this.getParameter('sysparm_currency');

var dateStr = this.getParameter('sysparm_date');

gs.info('CURRENCY DEBUG: Amount=' + amount +

', From=' + fromCurr +

', MRV Date=' + dateStr);

if (!amount || !fromCurr || !dateStr) {

return JSON.stringify({ amount: '', rate: '' });

}

// Build lookup datetime using MRV date (end of day)

var lookupDT = new GlideDateTime(dateStr + ' 23:59:59');

gs.info('CURRENCY DEBUG: FX lookup datetime=' + lookupDT.getValue());

// Query FX rate using only the currency field

var fxGR = new GlideRecord('fx_rate');

fxGR.addQuery('currency', fromCurr); // filter by source currency

fxGR.addQuery('sys_created_on', '<=', lookupDT); // use MRV date as max

fxGR.orderByDesc('sys_created_on'); // get the latest before date

fxGR.setLimit(1);

fxGR.query();

if (!fxGR.next()) {

gs.warn('CURRENCY DEBUG: No FX rate found for ' + fromCurr + ' on or before ' + lookupDT.getValue());

return JSON.stringify({ amount: '', rate: '' });

}

var rate = parseFloat(fxGR.rate);

gs.info('CURRENCY DEBUG: FX rate used=' + rate +

', Created=' + fxGR.sys_created_on +

', FX sys_id=' + fxGR.sys_id);

// Manual conversion to CAD

var convertedAmount = amount * rate;

var rounded = parseFloat(convertedAmount.toFixed(2));

gs.info('CURRENCY DEBUG: Converted=' + rounded);

// Return both converted amount and rate

return JSON.stringify({ amount: rounded, rate: rate });

},

type: 'CatalogCurrencyHelper'

});

What is your thoughts on this