i had requirement in fm_expense_line table we have amount filed

[sakila]

Requirement Statement:

In the fm_expense_line table, we have an amount field (type: currency) where the user enters a value and selects a currency. There is also a custom field u_amount_usd (type: currency) that should store the equivalent amount in USD.

The table also contains a date field provided by the user. Using this date and the selected currency, we need to look up the itfm_fx_rate_list table to find the applicable exchange rate based on the currency and the date range (start date and end date).

Once the correct exchange rate is identified, calculate:

u_amount_usd = amount * rate

Finally, auto-populate the u_amount_usd field with this calculated value.

[debendudas]

Hi @sakila  ,

You can use a Before Insert/Update Business Rule on fm_expense_line table.

Logic:

Condition -> Amount, its currency, or the date changes.

Query itfm_fx_rate with selected currency and expense date

If record found in itfm_fx_rate table:

set u_amount_usd = amount * rate

Script:

(function executeRule(current, previous) {
    var dateVal = current.getValue('u_date'); // Replace with your date field name
    var amount = current.getValue('amount');
    var currencyCode = current.amount.getCurrency();

    if (!amount || !currencyCode || !dateVal) {
        current.u_amount_usd = '';
        return;
    }

    var rateGr = new GlideRecord('itfm_fx_rate');
    rateGr .addQuery('u_currency_code', currencyCode); // Replace with actual field name
    rateGr .addQuery('u_start_date', '<=', dateVal); // Replace with actual field name
    rateGr .addQuery('u_end_date', '>=', dateVal); // Replace with actual field name
    rateGr .orderByDesc('u_start_date'); // Replace with actual field name
    rateGr .setLimit(1);
    rateGr .query();
    if (rateGr .next()) {
        var rate = parseFloat(rateGr .getValue('u_rate')); // Replace with actual field name
        var usdAmount = parseFloat(amount) * rate;
        current.u_amount_usd = usdAmount.toFixed(2);
        current.u_amount_usd.setCurrency('USD');
    } else {
        current.u_amount_usd = '';
        gs.addInfoMessage('No FX rate found for ' + currencyCode + ' on ' + dateVal);
    }
})(current, previous);

Note: The table itfm_fx_rate was not available in my PDI. Please validate the field names used in the code and replace them as needed. Also, the code has not been tested, so review and validate it before using.

If you want this functionality at the form level—on change of the amount or date field—use the same logic and implement it with GlideAjax to retrieve data from the server.

 If my response helped you, please consider marking it as "Accept as Solution"‌‌ ✔️ and "Helpful" ‌‌👍.
Doing so makes it easier for other community members to find the right answers and helps strengthen our community.

[Ankur Bawiskar]

@sakila  

you can use Before Insert & Update BR on your table

Condition: currency [CHANGES] AND Date [IS NOT EMPTY]

Script:

(function executeRule(current, previous) {

    var srcCurrency = current.currency.getCurrencyCode();
    if (srcCurrency === 'USD') {
        current.u_amount_usd = current.amount;
        return;
    }

    var rateGr = new GlideRecord('itfm_fx_rate_list');
    rateGr.addQuery('from_currency.code', srcCurrency);
    rateGr.addQuery('to_currency.code', 'USD');
    rateGr.addQuery('start_date', '<=', current.u_txn_date);
    rateGr.addQuery('end_date', '>=', current.u_txn_date);
    rateGr.setLimit(1);
    rateGr.query();

    if (rateGr.next()) {
        var rate = parseFloat(rateGr.rate);
        var amt = parseFloat(current.amount);
        if (!isNaN(rate) && !isNaN(amt)) {
            current.u_amount_usd = amt * rate;
        }
    }
})(current, previous);

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[Ankur Bawiskar]

@sakila  

Thank you for marking my response as helpful.

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏